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

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

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

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

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

-- Run a bash command or script and returns the stdout as a table.
-- It assumes bash is installed by YUM so 
-- it will be in the default location /QOpenSys/pkgs/bin/bash
-- You can use this aproach to other shells like sh, qsh setc.
-- 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:
----------------------------------------------------------------------------------------------

NielsLiisberg

-- Run a bash command or script.
-- It assumes bash is installed by YUM so 
-- it will be in the default location /QOpenSys/pkgs/bin/bash
-- You can use this aproach to other shells like sh, qsh setc.
-- I use qusrsys here however I suggest that you use your own toolibrary
-- 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:
----------------------------------------------------------------------------------------------

NielsLiisberg

--
-- Writes UTF-8 CLOB to IFS 
-- Simple way to write a stream file to the IFS, by using C runtime as inline code
--
-- This also showcase how to integrate the C code directly into your UDTF
--
-- 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