Problem solve
Get help with specific problems with your technologies, process and projects.
Retrieve query source
A utility that can easily be tweaked to provide visibility of files and fields used within iSeries 400 queries.
A utility that can easily be tweaked to provide visibility of files and fields used within iSeries 400 queries....
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
This is extremely useful when system documentation has become out of date and system changes are due.
I wrote the program to gather information on all queries. But it wouldn't be difficult to adjust the code for particular libraries only.
The clean up at the end has been commented out to allow later analysis over the files.
The program has little error trapping and assumes you have source file QTXTSRC in QGPL. You will need AS/400 Query and SQL on your machine.
RTVQRYC (CLP)
/*********************************************/
/* */
/* RETRIEVE QUERY SOURCE FOR SEVERAL QUERIES */
/* MARTIN CUBITT KEYMED LIMITED */
/* 3 OCTOBER 2001 */
/* */
/* USEFUL FOR CHECKING FILE/FIELDS */
/* WHERE-USED WHEN DOCUMENTATION IS */
/* NOT COMPLETE. */
/* */
/* REQUIRES AS/400 QUERY AND SQL */
/* */
/*********************************************/
DCL VAR(&CMD) TYPE(*CHAR) LEN(768)
DCLF FILE(RTVQRYW1)
/* DELETE ANY EXISTING WORK FILES/MEMBER */
DLTF FILE(QTEMP/RTVQRYW1)
MONMSG MSGID(CPF0000)
DLTF FILE(QTEMP/RTVQRYW2)
MONMSG MSGID(CPF0000)
RMVM FILE(QGPL/QTXTSRC) MBR(RTVQRYTMP)
MONMSG MSGID(CPF0000)
/* RETRIEVE KNOWN QUERY AND PROCESS THAT TO GET SOURCE TO */
/* OVERRIDDE AND FILE TO SET AS NOMAX */
/* RETRIEVE SOURCE FOR QUERY TO QGPL/QTXTSRC(RTVQRYTMP) */
RTVQMQRY QMQRY(QGPL/RTVQRYQ) SRCFILE(QGPL/QTXTSRC) +
SRCMBR(RTVQRYTMP) ALWQRYDFN(*YES)
/* NAME THIS SOURCE 'SOURCE' FOR FUTURE REFERENCE */
OVRDBF FILE(SOURCE) TOFILE(QGPL/QTXTSRC) +
MBR(RTVQRYTMP)
/* SET UP SQL COMMAND (IGNORE H AND V LINES FROM SOURCE) */
CHGVAR VAR(&CMD) VALUE('SELECT ''' || &ODLBNM || +
''', ''' || &ODOBNM || ''', SRCSEQ, +
SRCDTA FROM SOURCE WHERE +
SUBSTR(SRCDTA,1,1) NOT IN(''H'',''V'')')
/* PROCESS SQL STATEMENT TO CREATE OUTPUT TO RTVQRYW2 */
RUNSQL REQUEST(&CMD) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/RTVQRYW2) MEMBER(*ADD/*FIRST)
/* CHANGE FILE TO NOMAX */
CHGPF FILE(QTEMP/RTVQRYW2) SIZE(*NOMAX)
CLRPFM FILE(QTEMP/RTVQRYW2)
DSPOBJD OBJ(*ALLUSR/*ALL) OBJTYPE(*QRYDFN) +
OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)
CALL PGM(RTVQRYC1)
/* REPORT FINDINGS */
RUNQRY QRY(QGPL/RTVQRYQ)
/* TIDY UP */
/* DLTF FILE(QTEMP/RTVQRYW1) +
MONMSG MSGID(CPF0000) +
DLTF FILE(QTEMP/RTVQRYW2) +
MONMSG MSGID(CPF0000) +
RMVM FILE(QGPL/QTXTSRC) MBR(RTVQRYTMP) +
MONMSG MSGID(CPF0000) +
DLTOVR FILE(SOURCE) +
MONMSG MSGID(CPF0000) */
The section:
DSPOBJD OBJ(*ALLUSR/*ALL) OBJTYPE(*QRYDFN) +
OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)
CALL PGM(RTVQRYC1)
can be repeated for example:
DSPOBJD OBJ(LIB1/*ALL) OBJTYPE(*QRYDFN) +
OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)
CALL PGM(RTVQRYC1)
DSPOBJD OBJ(LIB2/*ALL) OBJTYPE(*QRYDFN) +
OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)
CALL PGM(RTVQRYC1)
DSPOBJD OBJ(LIB3/*ALL) OBJTYPE(*QRYDFN) +
OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)
CALL PGM(RTVQRYC1)
and the data will accumulate.
RTVQRYC1 (CLP)
/*********************************************/
/* */
/* RETRIEVE QUERY SOURCE FOR SEVERAL QUERIES */
/* MARTIN CUBITT KEYMED LIMITED */
/* 3 OCTOBER 2001 */
/* */
/* USEFUL FOR CHECKING FILE/FIELDS */
/* WHERE-USED WHEN DOCUMENTATION IS */
/* NOT COMPLETE. */
/* */
/* REQUIRES AS/400 QUERY AND SQL */
/* */
/*********************************************/
DCL VAR(&CMD) TYPE(*CHAR) LEN(768)
DCLF FILE(RTVQRYW1)
LOOP:
RCVF
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(END))
RTVQMQRY QMQRY(&ODLBNM/&ODOBNM) SRCFILE(QGPL/QTXTSRC) +
SRCMBR(RTVQRYTMP) ALWQRYDFN(*YES)
CHGVAR VAR(&CMD) VALUE('SELECT ''' || &ODLBNM || +
''', ''' || &ODOBNM || ''', SRCSEQ, SRCDTA FROM +
SOURCE WHERE SUBSTR(SRCDTA,1,1) NOT +
IN(''H'',''V'')')
RUNSQL REQUEST(&CMD) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/RTVQRYW2) MEMBER(*ADD/*FIRST)
GOTO CMDLBL(LOOP)
END:
RTVQRYQ (QRY)
The query used to report the findings.. feel free to change to suit your needs!
H QM4 05 Q 01 E V W E R 01 03 01/10/04 09:44
V 1001 050
V 5001 004 *HEX
SELECT
ALL SEL1||' '||SEL2 AS LIBQRY, SRCSEQ, SRCDTA
FROM QTEMP/RTVQRYW2 T01
ORDER BY LIBQRY ASC, SRCSEQ ASC
The Best Web Links: tips, tutorials and more.
Ask your programming questions--or help out your peers by answering them--in our live discussion forums.
Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.
