BirgittaHauser

-- Read *.csv file located within the IFS directly with SQL 
----------------------------------------------------------------------------------
-- Birgitta Hauser - 2019-10-25

-- 1. *.csv file located in the IFS: /home/Hauser/Employee1.csv
--    with the following content (including the column description)

-- "EMPLOYEENO","NAME","FIRSTNAME","ADDRESS","ZIPCODE","CITY","COUNTRY"
-- 10,"Meier und Sohn","","Industriestr. 3-13","80333","Muenchen","DE"
-- 20,"Bauer","Herrmann","Wald-und-Wiesen-Weg. 4","63128","Dietzenbach","DE"

BirgittaHauser

-- 1. CvtNumYYYYMMDD2Date User Defined Function
--    Convert a numeric date in the format YYYYMMDD into a real date
Create or Replace Function YourSchema.CvtNumYYYYMMDD2Date (                        
       ParDateNum Dec(8, 0) )                     
       Returns DATE                              
       Language SQL                              
       Specific YourSchema.CvtNumYYYYMMDD2Date               
       Not Deterministic                         
       Reads SQL Data                            
       Called on NULL Input                      

BirgittaHauser

-- Determine DDS described physical files with unique Keys
Select a.* 
From syspartitionIndexstat a
Where     Table_Schema = 'YOURSCHEMA' 
      and Index_Type   = 'PHYSICAL' 
      and UNIQUE       = '0';

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'),

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

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

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

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

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

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