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

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

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

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