Niels Liisberg NielsLiisberg

NielsLiisberg / sql_to_csv.sql

Last active May 10, 2022 14:47

SQL Produce CSV file

	-- This will produce a CSV file on the IFS using ifs_append and ifs_write
	-- also found here on my "gist"
	-- Simply give it a select statement or a procedure call
	-- and it will produce a CSV file in the IFS path of your choice
	-- Note: 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.sql_to_csv
	(

NielsLiisberg / set_bash_as_defaul_shell.sql

Last active June 5, 2020 14:12

SQL Set bash as your default shell

	-- install bash
	cl: qsh cmd('yum install bash');

	-- If you don't have a profil yet, then add the open source path to your default path
	cl: qsh cmd('touch $HOME/.profile');
	cl: qsh cmd('setccsid 1252 $HOME/.profile');
	cl: qsh cmd('echo ''PATH=/QOpenSys/pkgs/bin:$PATH'' >> $HOME/.profile');
	cl: qsh cmd('echo ''PS1="\\h-\\$PWD:\\n"'' >> $HOME/.profile');

	-- Finally set bash as the default shell

NielsLiisberg / modulus10.sql

Created November 5, 2019 23:25

SQL modulus 10 LUHN check and calculation

	-------------------------------------------------------
	-- 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 / ifs_write_json.sql

Last active November 5, 2019 17:17

SQL compund statement using FOR loop and writing JSON to the IFS

	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 / ifs_append.sql

Last active July 15, 2021 12:54

SQL append to IFS file

	----------------------------------------------------------------------------------------------
	-- Easy way to append text to a stream file in the IFS, by using C runtime as inline code
	--
	-- Simply paste the raw version of this gist into ACS SQL and select "run all"
	--
	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
	--
	-- You need library QSYSINC installed:
	-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
	--

NielsLiisberg / ifs_write.sql

Last active July 24, 2020 08:55

SQL write IFS file

	-- Simple way to write a stream file to the IFS, by using C runtime as inline code
	-- This will produce stream files UTF-8 encoded
	-- 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:
	----------------------------------------------------------------------------------------------
	call qcmdexc ('addlible qsysinc');
	call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C)');
	delete from qtemp.c;
	insert into qtemp.c (srcdta) values
	('{'),