Retrieve Files Used in Queries of an IBM i Library
A colleague of mine left me a real treasure trove of queries, accumulated over many years of work. It’s an endless list: over 1000 queries, all well cataloged and described.
Recently, I needed to find a query related to customer orders. Knowing that the order file is called OTORD, the customer file CLCLI, and the sample file CMCMP, I started opening the most likely queries. The process was long and frustrating: Open Query > Specify file selections > View the files used > Close Query. Stressful, right?
So, I thought I’d let our trusty IBM i do some work for me.
I created a procedure to retrieve the list of files used by all the queries in a specific library, with the ability to add some filters to answer more specific questions. For example:
Which queries use the files OTORD00F, CLCLI00F, and CMCMP00F?
The result? A clean and well-organized subfile:
The source code follows:
/******************************************************/ /* Retrieve Files Used in Queries of an IBM i Library
*/ /******************************************************/
PGM
PARM(&LIBRARY)
DCL VAR(&LIBRARY)
TYPE(*CHAR) LEN(10)
DCL VAR(&CPF ) TYPE(*CHAR) LEN(07)
DLTF
FILE(QTEMP/FQRFILE)
MONMSG MSGID(CPF0000)
DSPPGMREF
PGM(&LIBRARY/*ALL) OUTPUT(*OUTFILE) +
OBJTYPE(*QRYDFN) OUTFILE(QTEMP/FQRFILE)
MONMSG MSGID(CPF3033)
EXEC(CHGVAR VAR(&CPF) +
VALUE(CPF3033))
MONMSG MSGID(CPF3064)
EXEC(CHGVAR VAR(&CPF) +
VALUE(CPF3064))
CALL PGM(FQRRPG)
PARM((&CPF))
ENDPGM
|
CHGINPDFT(CS UL
HI) INDARA CA03(03
'F3=EXIT') REF(FQRFILE) *-------------------------------------------------------------- R SFL1 SFL *-------------------------------------------------------------- S1OPT
1A
B 8 2 S1LIB
R +3REFFLD(WHLIB
) S1PNAM
R + 1REFFLD(WHPNAM) S1FNAM
R + 1REFFLD(WHFNAM) S1RFNM
R + 1REFFLD(WHRFNM) *-------------------------------------------------------------- R FMT01 SFLCTL(SFL1) *-------------------------------------------------------------- SFLPAG(0010) SFLSIZ(&NBRRECS) OVERLAY N50 SFLDSP SFLDSPCTL 50 SFLCLR 91 SFLEND(*MORE) RTNCSRLOC(&PM_RCD
&PM_FLD) PM_RCD
10A
H PM_FLD
10A
H SF1NUM
4S 0H NBRRECS
5S 0P 1 26'Files
used by Query' F1LIB
R B 3 6REFFLD(WHLIB
) F1PNAM
R B + 1REFFLD(WHPNAM) F1FNAM
R B + 1REFFLD(WHFNAM) F1RFNM
R B + 1REFFLD(WHRFNM) + 1'<--FILTERS' 4 25'OR' F1FNA2
R B 4 28REFFLD(WHFNAM) 5 25'OR' F1FNA3
R B 5 28REFFLD(WHFNAM) 6 2'OPT' +1'Library ' +1'Query ' +1'File ' +1'Format ' *-------------------------------------------------------------- R S1CMD *-------------------------------------------------------------- S1MSG
78A
23 2 24 2'F3=EXIT' |
**FREE //
**************************************************** // Retrieve
Files Used in Queries of an IBM i Library * //
**************************************************** ctl-opt
option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no); dcl-f FQRRPGV
workstn indds(Dspf) sfile(SFL1 : SF1NUM); // constant dcl-c MaxSFLrec
9999 ; dcl-s ReLoadSFL
ind inz(*on) ; // alfanumeric
filter fields dcl-s F1LIB0lke
varchar(35) inz('%') ; dcl-s F1LIB0prv
varchar(35) inz(*blanks); dcl-s F1PNAMlke
varchar(35) inz('%') ; dcl-s F1PNAMprv
varchar(35) inz(*blanks); dcl-s F1FNAMlke
varchar(35) inz('%') ; dcl-s F1FNAMprv
varchar(35) inz(*blanks); dcl-s F1RFNMlke
varchar(35) inz('%') ; dcl-s F1RFNMprv
varchar(35) inz(*blanks); dcl-s F1TEXTlke
varchar(55) inz('%') ; dcl-s F1TEXTprv
varchar(55) inz(*blanks); dcl-s F1FNA2lke
varchar(35) inz('%') ; dcl-s F1FNA2prv
varchar(35) inz(*blanks); dcl-s F1FNA3lke
varchar(35) inz('%') ; dcl-s F1FNA3prv
varchar(35) inz(*blanks); dcl-ds Dspf
qualified ; Exit ind
pos(03) inz(*off); DoForF6 ind
pos(06) inz(*off); SflClr ind
pos(50) inz(*off); SflEnd ind
pos(91) inz(*off); end-ds ; dcl-ds
RecordDs; WHLIB char(10); WHPNAM
char(10); WHFNAM
char(11); WHRFNM
char(10); end-ds; Dcl-pi FQRRPG; pCPF char(7); End-pi; // Both
"exec sql" is not executed but needed at compile time. // It must be
placed just after the last "dcl" declaration. // LANGIDSHR
defines the sort sequence as a "shared-weight sort // upper/lower
insensitive, special characters are treated //
"ä" as "A" //
"Ü" as "E" and so on. exec sql set
option Commit = *None; exec sql set
option SRTSEQ = *LANGIDSHR; // preapre the
data recordset exec sql
declare C1 cursor for select WHLIB , WHPNAM, WHFNAM, WHRFNM from FQRFILE where
WHLIB like :F1LIB0lke and WHPNAM like :F1PNAMlke and WHFNAM like :F1FNAMlke and WHRFNM like :F1RFNMlke or WHLIB
like :F1LIB0lke and WHPNAM like :F1PNAMlke and WHFNAM like :F1FNA2lke and WHRFNM like :F1RFNMlke or WHLIB
like :F1LIB0lke and WHPNAM like :F1PNAMlke and WHFNAM like :F1FNA3lke and WHRFNM like :F1RFNMlke for read only;
//************************************************************* // Start
working...
//************************************************************* ReLoadSFL =
*on; dow Dspf.Exit =
*off; if ReLoadSFL; LoadSFL(); endif; write S1CMD; exfmt FMT01; if F1FNAM =
*blanks; F1FNAM =
'xxxxxxxxxxx'; endif; if F1FNA2 =
*blanks; F1FNA2 =
'xxxxxxxxxxx'; endif; if F1FNA3 =
*blanks; F1FNA3 =
'xxxxxxxxxxx'; endif; select; when
Dspf.Exit; // F3=Exit leave; when
Dspf.DoForF6; // F6=do something // call a
program or a subroutine other; //
Read user options dow 1 = 1; readc
SFL1; if Not
%eof; if
S1OPT = '1'; // call
a program or a subroutine endif; if
S1OPT = '2'; // call
a program or a subroutine endif; S1OPT =
'>'; update
SFL1; S1OPT =
' '; else; leave; endif; enddo; endsl; SetFilters(); enddo; exec sql close
C1; *inlr = *on;
//************************************************************* // Load subfile
//************************************************************* dcl-proc
LoadSFL; ReLoadSFL =
*off; // Clear
subfile Dspf.SflClr =
*on; write FMT01; Dspf.SflClr =
*off; // Load SFL SF1NUM =
*zero; exec sql
close C1; exec sql open
C1; // Read
recordset dow Sqlcode =
0 and SF1NUM < MaxSFLrec; exec sql
fetch C1 into :RecordDS; if sqlcode
= 0;
S1LIB = WHLIB ; S1PNAM =
WHPNAM; S1FNAM =
WHFNAM; S1RFNM =
WHRFNM; SF1NUM +=
1; write
SFL1; else;
Dspf.SflEnd = *on; // SFLEND *END endif; enddo; NBRRECS =
SF1NUM; S1MSG =
%Editc(NBRRECS:'X') + ' record loaded. No more record to load'; if sqlcode =
0 and NBRRECS >= MaxSFLrec; // MaxSFLrec reached S1MSG =
%Editc(NBRRECS:'X') + ' records loaded. Use filters to limit
the records to be loaded.'; endif; if NBRRECS =
0; clear SFL1; SF1NUM +=
1; S1LIB = '*'; S1MSG = 'No
records loaded'; if pCPF =
'CPF3033'; S1MSG =
'Object *ALL of type QRYDFN not found'; endif; if pCPF =
'CPF3064'; S1MSG =
'Library not found'; endif; write SFL1; endif; end-proc;
//************************************************************* // Set filters
filed
//************************************************************* dcl-proc
SetFilters; if (F1LIB
<> F1LIB0prv); //
char filter F1LIB0prv
= F1LIB ; F1LIB0lke
= '%' + %trim(F1LIB ) + '%'; ReLoadSFL =
*on; endif; if (F1PNAM
<> F1PNAMprv); //
Name F1PNAMprv
= F1PNAM; F1PNAMlke
= '%' + %trim(F1PNAM) + '%'; ReLoadSFL =
*on; endif; if (F1FNAM
<> F1FNAMprv); //
Name F1FNAMprv
= F1FNAM; F1FNAMlke
= '%' + %trim(F1FNAM) + '%'; ReLoadSFL =
*on; endif; if (F1RFNM
<> F1RFNMprv); //
Name F1RFNMprv
= F1RFNM; F1RFNMlke
= '%' + %trim(F1RFNM) + '%'; ReLoadSFL =
*on; endif; if (F1FNA2
<> F1FNA2prv); //
Name F1FNA2prv
= F1FNA2; F1FNA2lke
= '%' + %trim(F1FNA2) + '%'; ReLoadSFL =
*on; endif; if (F1FNA3
<> F1FNA3prv); //
Name F1FNA3prv
= F1FNA3; F1FNA3lke
= '%' + %trim(F1FNA3) + '%'; ReLoadSFL =
*on; endif; end-proc; |
DSPPGMREF PGM(yourLibrary/*ALL) OUTPUT(*OUTFILE) +
Now, from the command line, type: CALL PGM(FQRCL) PARM((yourLibrary))
Remember: You can filter all columns in the SubFile using all the yellow fields, like this:
Note that it also works with the *ALL parameter to scan your entire IBM i
Try with: CALL PGM(FQRCL) PARM((*ALL))
There you go!
Comments
Post a Comment