Skip to content

Instantly share code, notes, and snippets.

View BirgittaHauser's full-sized avatar

Birgitta Hauser BirgittaHauser

View GitHub Profile
@BirgittaHauser
BirgittaHauser / ReadCsv.SQL
Created October 24, 2019 10:14
Read *.Csv file and split the content into rows and columns directly with SQL (on Db2 for i)
-- 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
BirgittaHauser / CvtNum2Date.SQL
Created October 24, 2019 10:43
SQL Function to convert a numeric date into a real date
-- 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
BirgittaHauser / RetrievePFwithUniqueKey
Created October 31, 2019 09:23
Determine DDS described physical files containing Unique Keys
-- 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
BirgittaHauser / gist:86e18528b01c9452392e4d10043fe9d9
Created November 3, 2019 10:25
Locate_In_String - Determine the last occurence of a character or a set of characters
-- 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
BirgittaHauser / Timestamp ISO8601
Created January 3, 2020 09:57
Convert a (Db2 for i) Timestamp into a character representation of a timestamp in the ISO8601 Format: YYYY-MM-DDTHH:MM:SS+/HH:MM
-- 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
BirgittaHauser / ReadCsv.SQL
Created January 30, 2020 05:18
Read *.csv File directly with SQL
-- 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
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
BirgittaHauser / SearchSrcMbr
Created November 5, 2020 15:44
How to search source physical file member for a specific String
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
BirgittaHauser / BHA_Retrieve_DDS_Source_Members
Created November 20, 2020 09:40
Determine the Source Member for DDS describe physical and logical files
-- 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
BirgittaHauser / Get_Qual_ObjName
Created June 22, 2021 06:47
Get the qualified object name for a unqualified specified object in the library list
-- 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