Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dancarlosgabriel/571db94c1b8efe467483b991a7758aad to your computer and use it in GitHub Desktop.
Save dancarlosgabriel/571db94c1b8efe467483b991a7758aad to your computer and use it in GitHub Desktop.
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))
Returns Table (SrcSeq Dec(6, 2) ,
SrcDat Dec(6, 0) ,
SrcDta Char(132))
Language SQL
Specific PH_SrcMbr
Not Deterministic
Modifies SQL Data
Called On NULL Input
Disallow Parallel
Not Fenced
Set Option DBGVIEW = *SOURCE ,
DATFMT = *ISO ,
TIMFMT = *ISO
Begin
Declare LocSQLCmd VarChar(1024) Default '';
Declare Continue Handler for SQLState '42704' Begin End;
Declare Continue Handler for SQLException
Begin
Declare ErrText VarChar(128) Not Null Default '';
Get Diagnostics Condition 1 ErrText = MESSAGE_TEXT;
Signal SQLState 'PH001' Set MESSAGE_TEXT = ErrText;
End;
Set (ParSrcMbr,
ParSrcFile,
ParSrcLib) = (Upper(Trim(ParSrcMbr)),
Upper(Trim(ParSrcFile)),
Upper(Trim(ParSrcLib)));
Drop Alias QTEMP.TMPSRCMBR;
Set LocSQLCmd = 'CREATE OR REPLACE ALIAS QTEMP.TMPSRCMBR FOR ' concat
Trim(ParSrcLib) concat '.' concat
Trim(ParSrcFile) concat '(' concat
Trim(ParSrcMbr) concat ')';
Execute Immediate LocSQLCmd;
Return Select SrcSeq, SrcDat, SrcDta
From QTEMP.TMPSRCMBR;
End ;
Label on Specific Function YourSchema.PH_SrcMbr
is 'Read Source Physical File Member';
Comment on Specific Function YourSchema.PH_SrcMbr
is 'Read Source Physical File Member';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment