Skip to content

Instantly share code, notes, and snippets.

Niels Liisberg NielsLiisberg

Block or report user

Report or block NielsLiisberg

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@NielsLiisberg
NielsLiisberg / sav_rst.sql
Created May 27, 2020
SQL save and restore from/to IFS or restore directly from the web by HTTP or HTTPS
View sav_rst.sql
-- 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
NielsLiisberg / ftp_ifs.sql
Created May 15, 2020
SQL doing FTP simple PUT and GET of streamfiles
View ftp_ifs.sql
-- 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;
View ifs_write_blob.sql
-- 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
NielsLiisberg / webget.sql
Last active May 13, 2020
Get a stream file via the HTTP/HTTPS protocol and store it on the IFS
View webget.sql
-- 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)');
View migrate_database.sql
-- 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
------------------------------------------------------------------------------
View reset_identity_columns.sql
-- 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
NielsLiisberg / word.sql
Last active Feb 14, 2020
SQL get the n'th word from a string
View word.sql
-- 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
NielsLiisberg / joblog.sql
Created Feb 7, 2020
SQL send messages to joblog
View joblog.sql
-- Log a message to the joblog
-- 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' , '
{
/* declare prototype for Qp0zLprintf */
@NielsLiisberg
NielsLiisberg / Longest_common_subsequence.sql
Created Feb 5, 2020
SQL implementation of the ”Longest common subsequence” algorithm
View Longest_common_subsequence.sql
-- 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
View envvar.sql
-- 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>
You can’t perform that action at this time.