NielsLiisberg’s gists

NielsLiisberg / candidate_key.sql

Last active August 11, 2021 11:29

	-- Returns the best fit for a candidate key for a library/file
	-- for SQL tables, please convert the name to the physical filename first.

	-- Simply paste this gist into ACS SQL and run it to create the UDTF.

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

NielsLiisberg / jpa.sql

Last active November 16, 2021 11:55

	-- This ia a code-generator for JPA classes in Java.
	-- Give this table function the Shema and table name and
	-- it will produce a JPA class you can paste into your project.
	-- Take a look at the use cases below.

	-- This table function requires "candidate_key" and "snake_case"
	-- you will find elsewhere on my gist - build them first.

	-- Simply paste this gist into ACS SQL and run it to create the UDTF.

NielsLiisberg / camel_case.sql

Last active August 24, 2021 08:33

	-- UDTF to return a camelcase of a string for column naming purposes

	-- Simply paste this gist into ACS SQL and run it to create the UDTF.

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

	-- This gist is distributed on an "as is" basis, without warranties

NielsLiisberg / Logon.sql

Created October 28, 2021 12:56

SQL logon - check user profile and password


	-- Check the user profile and password
	-- This also showcase how to integrate the C code directly into your UDTF
	-- You need my IFS_WRITE UDTF found elsewhere at my gist
	--
	-- 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 2021
	--

NielsLiisberg / format_message.sql

Created October 28, 2021 16:01

Format messages ID and messages data into text

	-- Format a message from messag ID and messages data
	-- 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 2021
	--
	-- This gist is distributed on an "as is" basis, without warranties
	-- or conditions of any kind, either express or implied.

NielsLiisberg / job_is_waiting_for.sql

Last active December 13, 2021 15:17

Who is locking my database resources

	-- Returns the job name that is locking resources for another job
	--
	-- 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
	--
	-- It is a cool example how far you can go with SQL: Have fun -
	-- (C) Niels Liisberg 2021
	--
	-- This gist is distributed on an "as is" basis, without warranties

NielsLiisberg / udtf-intro.sql

Last active June 3, 2025 23:38

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

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

Niels Liisberg NielsLiisberg