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>

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

-- Log a message to the joblog
--
-- This also showcase how to integrate the C code directly into your UDTF/Procedure
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library.
--
-- This is a cool example how far you can go with SQL: Have fun 😀  
-- (C) Niels Liisberg 2022

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

-- 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

-- 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

-- Get a stream file via the HTTP/HTTPS protocol and store it on the IFS.
-- This was inspired by the "wget" AIX command, that is not always available
-- Note1: This also works for save files
-- Note2: This also compiles on vanilla systems without QSYSINCL installed
-- Note3: I am using library QUSRSYS. I suggest you put it into your tool library
-- I doubt it is a good idea to build huge applications this way, however it 
-- is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
----------------------------------------------------------------------------------------------
call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C)');

NielsLiisberg

-- Write a BLOB to IFS 
-- Note1: This also works for save files
-- Note2: This also compiles on vanilla systems without QSYSINCL installed
-- Note3: I am using library QUSRSYS. I suggest you put it into your tool library
-- I doubt it is a good idea to build huge applications this way, however it 
-- is a cool example how far you can go with SQL: Have fun :) 
-- (C) Niels Liisberg 2020
----------------------------------------------------------------------------------------------
call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C)');
delete from qtemp.c;

NielsLiisberg

-- FTP PUT and GET stream files from and to the IFS 
-- This is a wrapper arround the IBM i FTP command, making it easy to simply
-- call this stored procedure to put and get to and from a FTP server
-- I doubt it is a good idea to build huge applications this way, however it 
-- is a cool example how far you can go with SQL: Have fun :) 
-- (C) Niels Liisberg 2020
----------------------------------------------------------------------------------------------

-- Need this template file for compile
create or replace table qtemp.ftplog(line char(240)) on replace delete rows;

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)
)