List *QRYDFN Files DirectoryDirectory .ZIP
A tool for retrieving the names of files referenced within a *qrydfn object has been available for some time. It's called RtvQryF -- Retrieve Query Files -- and can be downloaded through the freeware/shareware section of News400.com and possibly elsewhere. The latest version works very well and I have no problem recommending it.
As well as it works, I needed something more. RtvQryF will examine a single *qrydfn object and return all file references for input and output files. But I wanted to handle multiple *qrydfns at once, libraries full of them if possible. So, I added some command shells, a CLP and some simple SQL and the result is posted here.Note: The RtvQryF tool must be downloaded and installed on your AS/400 before the objects here can be useful.An example usage would be:This builds a list of all *qrydfn objects in library MYLIB, runs LSTQRYF for each of them and then displays the resulting table.BLDOBJLST mylib/*ALL OBJTYP(*QRYDFN) PRCOBJLST CMD(LSTQRYF QRY(@@L/@@O) OPT(*ADD)) 'select * from qtemp/qrydfnfil'
Please read the @GENERAL DISCLAIMER document for any items you should be aware of if you download any of these items.
LSTQRYF -- List Query Files:This program is the CPP for LSTQRYF. The program accepts a qualified query name and a processing option. The query name is passed to RtvQryF for processing. When the file names are returned, they are placed into a table for later use. The processing option can be *ADD or *REPLACE. When *ADD is used, LSTQRYF can be called again for a different *qrydfn and the table will have new rows added.
LSTQRYF -- List Query Files:The command definition object source for LSTQRYF. This command accepts parameters defining a qualified query name and a processing option. The processing option is restricted to *ADD and *REPLACE.
RTVQRYF -- Retrieve Query Files:The command definition object source for the RTVQRYF tool. This command accepts parameters naming a query, a query library, an input file data structure (a CL variable to return the input file list into), an output file data structure (a CL variable to return the output file list into) and a return code. The input and output file data structures can be parsed to extract actual file names.Note: Read the documentation for RtvQryF to understand the ouput file data structure.
QRYDFNFIL -- Query Definition Files:This SQL source member is used in a RUNSQLSTM to create a table for holding file names. With a RUNSQL tool, this isn't actually required; the CREATE TABLE statement can be executed dynamically. I'd claim it was done this way simply to externalize the definition, but the UPDATE statements in the given CL are dynamic, so the claim would fail. Note, however, that the UPDATE statements could be externalized through direct use of STRQMQRY.