Skip to content

Instantly share code, notes, and snippets.

Avatar

Niels Liisberg NielsLiisberg

View GitHub Profile
@NielsLiisberg
NielsLiisberg / format_message.sql
Created Oct 28, 2021
Format messages ID and messages data into text
View format_message.sql
-- 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
NielsLiisberg / Logon.sql
Created Oct 28, 2021
SQL logon - check user profile and password
View Logon.sql
-- 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
--
View camel_case.sql
-- 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
View jpa.sql
-- 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.
View candidate_key.sql
-- 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
View snake_case.sql
-- UDTF to return a snake-case 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
View run ACS SQL script
-- This stored procedure is used to
-- Run a SQL script compatible with ACS 'Run SQL script' feature
-- The purpose is to create schemas, tables procedures, UDTF,
-- views etc., Alter tables, run update and delete statements.
-- This procedure is perfect for a DevOps loops. However statements
-- that produces result set are not supported.
-- The special case of running CL is also supported.
@NielsLiisberg
NielsLiisberg / bash_table_bin.sql
Created Feb 12, 2021
SQL Run bash scripts and return the data to SQL in ASCII
View bash_table_bin.sql
-- Run a bash command or script and returns the stdout as a table.
-- Each text line will be a row in the result set - the magic is done by the cool "pipe" statement.
-- Data is returned in binay (ASCII) so you need the ASC_TOO_EBCDIC to look at at the rows from with in ACS
-- So if you just heed to run bash text then use my gist bash_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 also need library QSYSINC installed:
@NielsLiisberg
NielsLiisberg / ascii_to_ebcdic.sql
Created Feb 12, 2021
SQL ASCII to EBCDIC conversion
View ascii_to_ebcdic.sql
-- Convert Q&D ascii to EBCDIC
-- 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
--
-- This gist is distributed on an "as is" basis, without warranties
@NielsLiisberg
NielsLiisberg / sql_update_read_only_views.sql
Last active Dec 29, 2020
SQL update a read-only view
View sql_update_read_only_views.sql
-- Normally you can not update/insert or delete on read only view.
-- And view becomes "read only" if you have any calculation,
-- uses any scalar function or joins
--
-- The trick here is to use a "instead-of trigger" where you control
-- the update, insert and delete process
--
-- 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 -