NielsLiisberg

begin
    declare cmd varchar(4096);
    declare comma varchar(1) default ''; 
    call qusrsys.ifs_write('/tmp/test.json'  , '[');  
    for vl as c1 cursor for select cusnum , lstnam from qiws.qcustcdt a do
        call qusrsys.ifs_append('/tmp/test.json'  , 
            comma concat  
            json_object (
                'customerNumber' : CUSNUM,
                'name' : LSTNAM

NielsLiisberg

------------------------------------------------------- 
-- Implements LUHN modulus 10 for credit card checksum
-- Niels Liisberg 
------------------------------------------------------- 
create or replace function qusrsys.modulus10 (inString varchar(32))                                       
    returns int   
    language sql                                                                           
    reads sql data                                                                          
    returns null on null input                                                               
    no external action                                                                      

NielsLiisberg

-- This is a tool to export all source physical filemembers on IBM i
-- to a git repo to keep track of changes over time
-- This requires the BASH stored procedure found on my gist
-------------------------------------------------------------------

-- Change the names to your names/repos/mail addr and Run this once:

call qusrsys.bash ('
    mkdir /passivegit;
    cd /passivegit;

NielsLiisberg

-- This example are using the IFS_WRITE and IFS_APPEND 
-- to produce a IFS files based on CLOB data in UTF-8
-----------------------------------------------------
create or replace procedure qusrsys.ifs_write_clob(name varchar(256), buf clob  ccsid 1208 ) 
external action 
modifies sql data

specific IFS_WRTCLB
set option output=*print, commit=*none, dbgview = *source
begin

NielsLiisberg

-- This implements the ”Longest common subsequence” algorithm by 
-- loading two source physical file members into arrays. This 
-- showcases the use of arrays in SQL but also the performance. 
-- I.e. 1000 lines of code will compare up to 1.000.000 times, 
-- hoewever,  this runs quite fast because of the feature
-- to use memory in memory to do the magic. The LCS algorithm is i.e. used
-- in GIT for tracking changes.
--
-- Read more here:
-- https://en.wikipedia.org/wiki/Longest_common_subsequence_problem

NielsLiisberg

-- Word: 
-- returns the nth delimited word in string or returns null 
-- if fewer than n words are in string. The n must be a positive whole number
-- (C) Niels Liisberg 2020
------------------------------------------------------------------------------  
create or replace function qusrsys.word (
    sourceString clob, 
    wordNumber int,
    delimiter char(1) default ' '
) 

NielsLiisberg

-- Saves and restores Library into streamfile. local on the IFS or direct with HTTP from an URL 
-- Note1: This uses my ftp_put_ifs and webget, also found on my Gist
-- Note2: I am using library QUSRSYS. I suggest you put it into your own tool library
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
----------------------------------------------------------------------------------------------
create or replace procedure qusrsys.savlib_to_ifs (
    library char(10),
    stmf  varchar(256)
) 

NielsLiisberg

-- migrate_database: 
-- This copies and replaces all tables and files in the target schema ( library ) 
-- with data from  tables and files in the source schema ( library) 
-- Also it wil cater for identity columns in the target to be set to the next available value.
-- Be carefull to use this since it will not keep a bacup of your target data
-- So be sure that it works for you. Take a backup of you traget before you begin.
-- Both clasic PF and SQL tables are supported. 
-- Note: I am using QUSRSYS here , but i suggest that you place it in your own system schema 
-- (C) Niels Liisberg 2020
------------------------------------------------------------------------------

NielsLiisberg

-- reset_identity_columns: 
-- Resets all identity columns is a given table and schema to next values after last used values.
-- This comes in handy when you copy data into a table and overwrites all current rows 
-- since this operation will not reset the id counter which can create severe errors
-- Note: I am using QUSRSYS here , but i suggest that you place it in your own system schema 
-- (C) Niels Liisberg 2020
------------------------------------------------------------------------------
 create or replace procedure qusrsys.reset_identity_columns ( 
    in table_schema char(10),
    in table_name   varchar(256)

NielsLiisberg

-- Get environment variable as a string - set by ADDENVVAR  command
-- You need to have the "ifs_write" procedure found on my gist
-- you need library QSYSINC installed:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
-- I doubt this method is a good idea to build huge applications, 
-- however it is a cool example how far you can go with SQL:
----------------------------------------------------------------------------------------------
call qusrsys.ifs_write('/tmp/include.c' , '
{
    #include <stdlib.h>