Question & Answer
Question
Is there a tool that will list all input and output file references made by my Query for i (Query/400) query definitions?
Cause
Planning upgrade, and trying to identify all input and output files referenced by all query definitions.
Answer
Use Display Program References (DSPPGMREF) with *OUTFILE support
Once the command completes, you can run a query over the outfile you just created
SELECT WHPNAM, WHLIB, WHFNAM, WHLNAM, WHFUSG
FROM -your library-/-your outfile-
(or use a . instead of a / as in library.outfile in FROM clause)
where the fields are:
WHPNAM - Query Definition Name
WHLIB - Query Definition Library Name
WHFNAM - Referenced File Name
WHLNAM - Reference File Library Name
WHFUSG - Referenced File Usage:
1 - Input
2 - Output
3 - Input/Output
4 - Update
5 - Input/Update
6 - Output/Update
7 - Input/Output/Update
8 - Not specified
0 - Not available
DSPFFD of the outfile will give you the field names and text descriptions if you wish to craft your own queries.
You can use the WHERE clause to filter out results:
// Find all input file references for query definition TESTQRY in library QGPL:
SELECT WHFNAM, WHLNAM
FROM -your library-/-your outfile-
WHERE WHFUSG = 1
AND WHPNAM = 'TESTQRY'
AND WHLIB = 'QGPL'
References
DSPPGMREF field definitions
https://www.ibm.com/support/pages/detailed-file-field-description-file-qadsppgm-format-qwhdrppr-dsppgmref-output-file
DSPPGMREF CL Reference
https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/dsppgmref.html
Database Programming
https://www.ibm.com/docs/en/i/7.5?topic=files-displaying-used-by-programs
DSPPGMREF PGM(*ALL/*ALL) OUTPUT(*OUTFILE) OBJTYPE(*QRYDFN) OUTFILE(-your library-/-your outfile-)
Once the command completes, you can run a query over the outfile you just created
SELECT WHPNAM, WHLIB, WHFNAM, WHLNAM, WHFUSG
FROM -your library-/-your outfile-
(or use a . instead of a / as in library.outfile in FROM clause)
where the fields are:
WHPNAM - Query Definition Name
WHLIB - Query Definition Library Name
WHFNAM - Referenced File Name
WHLNAM - Reference File Library Name
WHFUSG - Referenced File Usage:
1 - Input
2 - Output
3 - Input/Output
4 - Update
5 - Input/Update
6 - Output/Update
7 - Input/Output/Update
8 - Not specified
0 - Not available
DSPFFD of the outfile will give you the field names and text descriptions if you wish to craft your own queries.
You can use the WHERE clause to filter out results:
// Find all input file references for query definition TESTQRY in library QGPL:
SELECT WHFNAM, WHLNAM
FROM -your library-/-your outfile-
WHERE WHFUSG = 1
AND WHPNAM = 'TESTQRY'
AND WHLIB = 'QGPL'
References
DSPPGMREF field definitions
https://www.ibm.com/support/pages/detailed-file-field-description-file-qadsppgm-format-qwhdrppr-dsppgmref-output-file
DSPPGMREF CL Reference
https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/dsppgmref.html
Database Programming
https://www.ibm.com/docs/en/i/7.5?topic=files-displaying-used-by-programs
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CQbAAM","label":"IBM i Db2-\u003EQuery\/400"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
04 December 2024
UID
nas8N1021173