NielsLiisberg’s gists

NielsLiisberg / counter.sql

Last active October 5, 2022 14:14

SQL: counter

	-- Returns a list numbers:
	-- from and including FROM value
	-- until and including TO value
	-- and optional STEP can be either positive or negative. Default to +1 for step up and -1 for step down
	--
	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	-- It is a cool example how far you can go with SQL: Have fun 😀

NielsLiisberg / days_in_year.sql

Created October 5, 2022 12:16

SQL: Returns a list of all dates in a given year

	-- Returns a list of all dates in a given year
	--
	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	-- It is a cool example how far you can go with SQL: Have fun 😀
	-- (C) Niels Liisberg 2022
	--
	-- This gist is distributed on an "as is" basis, without warranties

NielsLiisberg / service_program_procedure_exports.sql

Created September 29, 2022 16:08

service program procedure exports

	-- List service program procedure exports
	--
	-- Simply paste this gist into ACS SQL and step through the example.
	--
	-- It is a cool example how far you can go with SQL: Have fun 😀
	-- (C) Niels Liisberg 2022
	--
	-- This gist is distributed on an "as is" basis, without warranties
	-- or conditions of any kind, either expressed or implied.
	----------------------------------------------------------------------------------------------

NielsLiisberg / base64_encode_blob.sql

Last active August 8, 2022 09:28

	-- convert BLOB to a bsae64 CLOB in UTF-8
	-- 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.
	--
	-- It is a cool example how far you can go with SQL: Have fun 😀
	-- (C) Niels Liisberg 2022
	--
	-- This gist is distributed on an "as is" basis, without warranties
	-- or conditions of any kind, either express or implied.

NielsLiisberg / enable_netserver_user.sql

Last active June 29, 2022 08:17

SQL re-enable net user

	-- Enable netserver user
	-- 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.
	--
	-- It is a cool example how far you can go with SQL: Have fun 😀
	-- (C) Niels Liisberg 2022
	--
	-- This gist is distributed on an "as is" basis, without warranties
	-- or conditions of any kind, either express or implied.

NielsLiisberg / system_name.sql

Created June 22, 2022 15:55

SQL Retrieve system name by MI call

	-- Retrieve the system name by call to MI
	-- 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.
	--
	-- It is a cool example how far you can go with SQL: Have fun 😀
	-- (C) Niels Liisberg 2022
	--
	-- This gist is distributed on an "as is" basis, without warranties
	-- or conditions of any kind, either express or implied.

NielsLiisberg / rename_local_database.sql

Created June 21, 2022 14:15

SQL rename local default database to system name

	-- When indexes are created in one languate - they will not be usable in other languages.
	--
	-- This procedure simply replaces the current *LOCAL
	-- database name with the name of the system name
	--
	-- Simply paste this gist into ACS SQL and step through the code.
	--
	-- 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 😀

NielsLiisberg / table_access_sequence.sql

Created June 21, 2022 12:44

SQL table access sequence

	-- When indexes are created in one languate - they will not be usable in other languages.
	--
	-- This table function returns a list of access to tables by
	-- national char features. By analysing the SQSSEQ and language
	-- combined with the job CCSID and language, is can be determind
	-- if an inex will be used on not.
	--
	-- Simply paste this gist into ACS SQL and step through the code.
	--
	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

NielsLiisberg / drop_old_files.sql

Last active August 2, 2022 13:57

SQL Drop old backup files made by RSTOBJ commd

	-- This procedure deletes old backup files produced
	-- when the RSTOBJ command restores file objects.
	--
	-- It uses the regex to filter the names of the files
	-- the RSTOBJ command gives these file objets in the rename process.
	--
	-- Simply paste this gist into ACS SQL and step through the code.
	--
	-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
	--

NielsLiisberg / udtf-intro.sql

Last active August 19, 2024 14:59

SQL Db2 for IBM i - introduction to UDTF

	-- SQL Db2 for IBM i - introduction to UDTF
	--
	-- This is a tutorial starting with SQL functions, over User defined Table Function (UDTF)
	-- and procedures. It covert the basic features and some common pitfalls.

	-- By using IBM i SQL service as the vehicle for making service calls over http and integrate data
	-- from Db2 on the IBM i makes this tutorial super relevant.
	--
	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2022-2024

Niels Liisberg NielsLiisberg