Skip to content

Instantly share code, notes, and snippets.

@BirgittaHauser

BirgittaHauser/How to read source files with SQL

Last active October 2, 2023 07:31
Show Gist options
  • Save BirgittaHauser/8a95ec08f00f99f708ac42de0e5d9332 to your computer and use it in GitHub Desktop.
Save BirgittaHauser/8a95ec08f00f99f708ac42de0e5d9332 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
How to read source files with SQL
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';
@josephmarreiros
Copy link

Hello Birgitta,

Would you mind providing an example on how to use the function?

I have replaced "YourSchema" with "Joseph"

The creation of the function was successful.

Object Type Library Attribute Text
PH_SRCMBR *SRVPGM JOSEPH CLE SQL FUNCTION PH_SRCMBR

I have tried both:
values Joseph.PH_SrcMbr('CPYSFCONST', 'QRPGLESRC', 'JOSEPH');

and
select Joseph.PH_SrcMbr('CPYSFCONST', 'QRPGLESRC', 'JOSEPH') FROM SYSIBM.SYSDUMMY1;

I get this error:
SQL State: 42887 Vendor Code: -390 Message: [SQL0390] Use of function PH_SRCMBR in JOSEPH not valid. Cause . . . . . :   Use of function PH_SRCMBR in schema JOSEPH is not valid.  The specific name is PH_SRCMBR. One of the following has occurred: -- A table function was specified in a clause other than the FROM clause. -- A function was specified in the FROM clause but the function is not a table function. -- A table function was specified as a source function in a CREATE FUNCTION statement. -- UNNEST was specified but is only allowed in SQL procedures and functions. -- WITH RETURN was specified for a cursor that has a function with an array result in the select-list. -- ARRAY_AGG, LISTAGG, XMLAGG, or GROUPING aggregate function or a sourced function was used in an OLAP specification. Recovery  . . . :   Remove the function from the clause or change the function name, arguments, or path so that a different function is found. Try the request again.

Thank you in advance,
Joseph

@BirgittaHauser
Copy link
Author

It is a User Defined Table Function which must be called in the FROM CLAUSE of a SELECT Statement.

Select *
  from Table(YOURSCHEMA.PH_SrcMbr(Parsrcmbr => 'YOURMBR', 
                               Parsrcfile   => 'YOURSRCF', 
                               Parsrclib    => 'YOURSRCL'));

@josephmarreiros
Copy link

josephmarreiros commented Aug 19, 2023 via email

@mk1tools
Copy link

mk1tools commented Oct 2, 2023

Hi Birgitta, your function is very useful.
I suggest a few improvements:

  1. return SrcDta Char(240) instead of 132 so you can read large source physical file

  2. enclose the member name in double quotes so you can read member with name as "MYMBR.BAK" or similar.

set LocSQLCmd = 'CREATE OR REPLACE ALIAS QTEMP.TMPSRCMBR FOR ' concat
trim(SOURCE_LIBRARY) concat '.' concat
trim(SOURCE_FILE) concat '("' concat
trim(SOURCE_MEMBER) concat '")';
  1. filter for empty lines of code
return select SRCSEQ, SRCDAT, rtrim(SRCDTA)
from QTEMP.TMPSRCMBR
where SRCDTA <> ' ';

The reason is a trick. Sometimes I found display file member with the SRCDTA field filled by invalid blanks and the SRCDTA empty.

  1. Only for documentation
comment on parameter function YourSchema.PH_SrcMbr
(SOURCE_MEMBER is 'Member name',
SOURCE_FILE is 'File name',
SOURCE_LIBRARY is 'Library name');

Thanks a lot. Good job.
Marco

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment