NielsLiisberg

-- Set environment variable from a string value 
-- you need library QSYSINC installed:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
--
-- 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 2023
--
-- This gist is distributed on an "as is" basis, without warranties 

NielsLiisberg

-- Convert text to decimal number for europeans where decimal point is a comma 
--
-- 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.
--
-- Note: I am using the 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 2022

NielsLiisberg

-- Copy members from a source physical file to IFS directory and preserving the member text as part of the filename.
--
-- Notes: 
-- 1) I am using library QUSRSYS. I suggest you put it into your own tool library
-- 2) The output direcory has to exists. 
-- 3) Files contents are being replaces if they already exists. 
-- 4) IFS files CCSID is set to 1208 that is UTF-8
--
-- Simply paste this gist into ACS SQL and step through the example.
--

NielsLiisberg

-- Use MI to generarte a RFC 4122 compiant UUID / GUID 
--
-- This also showcase how to integrate the MI api using 
-- C code directly into your UDF
--
-- 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

-- SQL Scalar function to check access to an IFS file or folder

-- It returns:
--    0=If the file or folder Exists and you have access
--   -1=No access or file does not exists 
--
-- This is a wrapper for the access() unix API:
-- https://pubs.opengroup.org/onlinepubs/009695299/functions/access.html

-- Note second parameter is the acces mode, that defaults to F_OK

NielsLiisberg

-- SQL Scalar function to delete ( unlink) a IFS files 
-- this is a wrapper for unlink (delete IFS files)  system / unix API 

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

NielsLiisberg

-- UDTF to return a capitalize first letter in each word of a string

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

NielsLiisberg

-- Returns database relations with keys 
-- for SQL tables, the name is convert 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 2023

NielsLiisberg

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

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: