NielsLiisberg

-- Let SQL create a command and CLLE program to run PASE shell
-- scripts or commands - much like QSH but for PASE. Ex. like this:
--
--    SH SCRIPT('ls')
--
-- Simply paste 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
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020

NielsLiisberg

-- SQL procedure to get a quick system catalog of files, tables and views.
-- I use this from ACS all the time to get a catalog overview.
--
-- Simply paste 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
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
-------------------------------------------------------------------------------------
create or replace procedure  qusrsys.syscat  (

NielsLiisberg

-- Any_sort solves the problem where you have an alphanumeric column 
-- that contains mixed numeric and alphanumeric data and you need to 
-- order both alphanumeric AND numeric at the same time 
-- 
-- Any sort returns a varchar that left adjust alpha and right adjust numeric values
--
-- Simply copy this gist and paste it into ACS SQL prompt. Select "run all" to build this feature.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--

NielsLiisberg

-- Export_schema_script and import_schema_script uses IBM generate_sql_objects  
-- for building a export and import script in "perfect order" with IBM's SQL statements
-- in "perfect order"
--
-- After exporting a schema definition you can then use the output script 
-- to build an empty new version of the schema OR you can migrate 
-- and upgrade an existing older version of the same schema with 
-- a new layout. Perfect for CI/CD and development/production environment
-- 
-- The clone_empty_schema is a combination of first running 

NielsLiisberg

-- Packing a numeric into a zoned buffer by using C runtime function.
-- The magic is done by the QXXDTOZ api and not external dependencies
-- 
-- Simply paste 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
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
--
-- This gist is distributed on an "as is" basis, without warranties 

NielsLiisberg

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

NielsLiisberg

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

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

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

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