SQL Search Source by Line Changed Date

Someone recently wanted to do this and I wondered if it could be
done with SQL. I also wanted to experiment with SQL PL. So I
tried it as a learning project.

Here's the code and I'm very open to suggestions on improvement
in technique, style, whatever.

First, a UDF which take library, file, member, and date range.
It creates an ALIAS for the member and then search if by for
lines in the date range.
It populates a SRCFND file in my library with qualifying lines.

CREATE OR REPLACE FUNCTION lennons1.src_line_date(
plib varchar(10),
pfile varchar(10),
pmember varchar(10),
pymdfrom numeric(6),
pymdto numeric(6)
)
RETURNS INTEGER
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
declare SQLCODE Integer Default 0;
declare insert_count integer default 0;
declare vsrcseq numeric(6,0);
declare vsrcdat numeric(6,0);
declare Vsrcdta varchar(256);
declare alias_name varchar(40);
declare skip_recd integer;
declare bad_data condition for SQLSTATE '22023'; --Invalid data
declare src_mbr CURSOR FOR
select * from qtemp.zz_src where srcdat between pymdfrom and pymdto;
declare continue handler for bad_data
set skip_recd = 1;
set alias_name = plib concat '."' concat pfile
concAT '"("' concat pmember concat '")';
execute immediate 'create alias qtemp.zz_src FOR ' concat alias_name;
OPEN src_mbr;
LblLoop:
loop
set skip_recd = 0;
fetch next from src_mbr into vsrcseq, vsrcdat, vsrcdta;
if SQLCODE=100 then leave LblLoop;
end if;
if skip_recd = 1 then goto LblLoop;
end if;
insert into lennons1.srcfnd
values(plib,pfile,pmember,vsrcseq,vsrcdat, vsrcdta);
set insert_count = insert_count + 1;
end Loop;
close src_mbr;
execute immediate 'drop alias qtemp.zz_src';
return insert_count;
end;

Then SQL to call it. The MBRS CTE gives, I believe, all source files
(in my library) and the call to the function has a line changed date
range. You can adjust the select as you see fit.


WITH MBRS AS
(SELECT SYSTEM_TABLE_SCHEMA AS LIB,
SYSTEM_TABLE_NAME AS FILE,
SYSTEM_TABLE_MEMBER AS MEMBER
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_SCHEMA = 'LENNONS1' AND SOURCE_TYPE IS NOT NULL
AND SOURCE_TYPE NOT IN ('PF', 'LF')
--EVFTEMPF is IBM file and contains bad SRCDAT data
AND SYSTEM_TABLE_NAME not like('EVFTEMPF%')
-- fetch first 5 rows only
)
SELECT lib, file, member,
lennons1.src_line_date(
lib, file, member, 200723, 200724) Found_Lines
FROM MBRS
ORDER BY Found_Lines desc, lib, file, member;

src_line_date gives results like this:
LIB FILE MEMBER FOUND_LINES
LENNONS1 DEMO PMTCUSTD 63
LENNONS1 DEMO PMTCUSTR 59
LENNONS1 DEMO PMTSTATED 54
LENNONS1 DEMO CRTMSGF 4
LENNONS1 A.SRCFILE X.MBR 0

And you can see the actual lines in SRCFND:
select * from lennons1.srcfnd;

srcfnd is created thus:
create table lennons1.srcfnd (lib char(10), file char(10), member char(10),
srcseq numeric(6,0), srcdat numeric(6,0), srcdta varchar(256));

Notes:
1) By using varchar I think I've accounted for differing srcdta lengths.
2) I discovered you could have blanks in srcdat. Turned up first in
EVFTEMP files, but I also found one in my own code. I ignore these
lines.
3) I discovered you could have a . in a member name, e.g. M2.TEST1.
Also in file names, like A.SRCFILE.
Thus the double quotes in the alias_name.
4) You can also have strange member names like #@$ but I can't
figure out how to handle this in SQL.
5) I could not get the System Debugger to run (on PUB400.COM). This made
debugging difficult.
6) There is a dearth of useful documentation on SQL PL. There is info in
the SQL Reference, in the Db2 11.1 for Linux, UNIX, and Windows
SQL PL manual, and I just found some articles by Ted Holt
on ITJungle which I will have to study.