|
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
;
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.