Re: SQL Search Source by Line Changed Date -- MIDRANGE-L

Sam, that was me asking about this.
I have tested it and it looks good.
I created the table and UDF.
Then created in ACS Run SQL Scripts Insert example. That way I can call it
back up when I want.
I did take it a bit further and added variables.
This is what my Insert example looks like.

-- Change these three temp variables to be what you want.
CREATE OR REPLACE VARIABLE kcrawford1.TmpSQLV01 CHAR(10) DEFAULT
'KCRAWFORD1' -- Name of library you want to search
;
CREATE OR REPLACE VARIABLE kcrawford1.TmpSQLV02 numeric(6) DEFAULT '200101'
-- From Date to Search YYMMDD
;
CREATE OR REPLACE VARIABLE kcrawford1.TmpSQLV03 numeric(6) DEFAULT '200104'
-- To Date to Search YYMMDD
;
-- Clear the work file from the last time.
cl:CLRPFM FILE(KCRAWFORD1/SRCFND)
;
-- The MBRS CTE gives, all source files (in library) and
-- the call to the function has a line changed date range.
-- it will use the variables from above.
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 = kcrawford1.TmpSQLV01 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,
kcrawford1.Srch_Src_line_date(
lib, file, member, kcrawford1.TmpSQLV02, kcrawford1.TmpSQLV03)
Found_Lines
FROM MBRS
ORDER BY Found_Lines desc, lib, file, member
;
-- =========+=========+=========+=========+=========+
--You can see the actual lines in SRCFND:
select *
from kcrawford1.srcfnd
;

On Wed, Aug 12, 2020 at 4:48 PM Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com