BirgittaHauser

CREATE OR REPLACE FUNCTION YourSchema.NUM72DATE ( 
	DATENUM DECIMAL(7, 0) ) 
	RETURNS DATE   
	LANGUAGE SQL 
	DETERMINISTIC 
	CALLED ON NULL INPUT 
BEGIN 
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
           RETURN DATE ( '8888-12-31' ) ; 
   RETURN CAST ( DIGITS ( DEC ( CHAR ( DATENUM + 19000000 ) , 8 ) ) CONCAT '000000' AS DATE ) ; 

BirgittaHauser

Create Function YourSchema/Checknum (Parstring Varchar(32))
       Returns Integer
   Language Sql
   Modifies Sql Data
   Deterministic
   Called On Null Input
   Secured
   Begin
     Declare LocNum  Decimal(31, 9) Not NUll Default 0;
     Declare Continue Handler For SQLException Return -1;

BirgittaHauser

-- Convert a numeric date consisting of 3 Columns (4-digit year, 2-digit month, 2-digit day) into a real date
-- If Year, Month, Day are all *Zeros - 0001-01-01 is returned
-- If Year, Month, Day are all 9      - 9999-12-31 is returned
-- If Year, Month, Day deliver an invalid date - 8888-12-31 is returned
-- Otherwise the converted date is returned

Create Or Replace Function YourSchema/CvtYYYY_MM_DD2Date (
          Paryear Decimal(4, 0)  Default 0, 
          Parmonth Decimal(2, 0) Default 0,
          Parday Decimal(2, 0)   Default 0)

BirgittaHauser

-- I was recently asked how to get the qualified SQL/System Name for an unqualified specified object from the library list.
-- To make it easy I created a global variable to hold the Object Name 
-- (a global variable can be populated with the SQL SET statement)
Create Or Replace Variable YourSchema.GblObjName VarChar(128) Default '';

-- In the following SELECT statement all Product and User Libraries in the library list are searched
-- for Programs, Service Programs and Files
-- If the content of the global variable is up to 10 characters the system name and SQL names are searched 
-- otherwise only the SQL Names
-- The sequence of the libraries is considered

BirgittaHauser

-- Parameters for Object_Statistics:
-- 1. OBJECT_SCHEMA: Library where the DDS described database object is located
--                   Special Values: *ALL, *ALLAVL, *ALLSIMPLE, *ALLUSR, *ALLUSRAVL, *CURLIB, *LIBL, *USRLIBL
-- 2. OBJECT_TYPE_LIST: List Object Types 
--                   Special Values: *ALL
-- 3. OBJECT_NAME: Name of the DDS described physical or logical file
--                   Special Values: *ALL

Select  X.ObjName, X.ObjLib, 
        x.Source_File, x.Source_Library, x.Source_Member

BirgittaHauser

It was just a question in a Forum: How to search (all) source physical file members for a specific string and list all those members
In this examples all source files beginning with "QSRC" in the "YOURSCHEMA" library are searched whether they include "String". 
All Source Members that include "String" are returned

With a as (Select a.System_Table_Schema OrigSchema, 
                  a.System_Table_Name   OrigTable, 
                  a.System_Table_Member OrigMember,
                  Trim(System_Table_Schema) concat '/' concat 
                  Trim(System_Table_Name)   concat '(' concat 
                  Trim(System_Table_Member) concat ')'         as OrigCLOBMbr

BirgittaHauser

Here is a function whith wich you can read your source files directly with SQL:
-- 1.1. Parameters:                                                                
--      ParSrcMbr        Source Member Name                                        
--      ParSrcFile       Source File                                               
--      ParSrcLib        Source Library                                            
-- ---------------------------------------------------------------*                
Create Or Replace Function YourSchema.PH_SrcMbr                                               
       (ParSrcMbr     VarChar(10) ,                                                
        ParSrcFile    VarChar(10) ,                                                
        ParSrcLib     VarChar(10))                                                 

BirgittaHauser

-- Read *csv File from IFS
With x as (-- Split IFS File into Rows (at CRLF)
           Select Ordinal_Position as RowKey, Element as RowInfo
             from Table(SysTools.Split(Get_Clob_From_File('/home/Hauser/Employee.csv'), x'0D25')) a
             Where Trim(Element) > ''),
     y as (-- Split IFS File Rows into Columns (and remove leading/trailing double quotes ")
           Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
             from x cross join Table(SysTools.Split(RowInfo, ',')) a)
-- Return the Result as Table

BirgittaHauser

-- Convert a (Db2 for i) Timestamp into a character representation of a timestamp in the ISO8601 Format:
-- YYYY-MM-DDTHH:MM:SS+/HH:MM

Create Or Replace Function YOURSCHEMA.TIMESTAMP_ISO8601
          (ParTimestamp  Timestamp(6)    Default Current_Timestamp,
           ParTimeZone   Decimal(6, 0)   Default Current_Timezone)
       Returns Varchar(26)
       Language SQL
       Modifies Sql Data
       Specific TS_ISO8601

BirgittaHauser

-- With the scalar function LOCATE_IN_STRING it is possible to search a string backwards
-- IBM Documentation: If the value of the 3rd parameter (START) is less than zero, the search begins at
--                    CHARACTER_LENGTH(source-string) + start + 1 and continues for each position to the 
--                    beginning of the string

-- Example, determine the last '/' in an IFS file name, so the file name can be determined
With x (IFSFile) as (Values('/home/Hauser/JSON/Examples/MyFile.json'),
                           ('/MyCompany/MyFirstDirectory/My2ndDir/LastDir/Sales20191103.txt'),
                           ('Dir01/NextDir/MySQLScript.sql'),
                           ('MyFile.txt'),