Which files are used by a Query?

This is a question I am asked enough for me to want to write this so I can direct the askers here. The question is:

How can I discover the files used by all the Query/400 objects in a library?

The Query/400 object is called a Query Definition, and is the following object type: *QRYDFN

To generate a list of the files they use is a two-step process.

The first process is to create an output file that contains the data. I use the Display Program Reference command, DSPPGMREF, to generate the output file. If I wanted to list all the files used by all of the Query definitions in the library MYLIB I would use the following:

01 DSPPGMREF PGM(MYLIB/*ALL) 02 OUTPUT(*OUTFILE) 03 OBJTYPE(*QRYDFN) 04 OUTFILE(MYLIB/OUTFILE)

Line 1: I only want the results for the all of Query Definitions in the library MYLIB.

Line 2: I want the results to be written to an output file, see line 4 for which file I want to use.

Line 3: This is where I must enter *QRYDFN so that I will only get the results for what I want.

Line 4: I have said I want my results in the same library as the one I getting the data for, but it could be any library. I have also called the output file OUTFILE, any valid name for a file could be used.

If you are going to be running this for a library that contains many Query Definitions you might want to submit this command to batch.

Once the output file is generated I can get to the information I desire by using SQL.

01 SELECT WHLIB AS "Query lib", 02 WHPNAM AS "Query", 03 WHSNAM AS "File id", 04 WHLNAM AS "File lib", 05 WHFNAM AS "File", 06 WHRFNM AS "Rec format" 07 FROM MYLIB.OUTFILE 08 WHERE WHOTYP = '*FILE' 09 ORDER BY WHLIB,WHPNAM,WHSNAM

Lines 1 – 6: I am only interested in some of the columns/files in the output file. I think the column headings I have given them adequately describe their contents.

Line 7: The name of the output file the results are contained within.

Line 8: I only want the results for files.

Line 9: I want my results in: Query library, Query name, and file id order.

I only have two Query Definitions in my library, therefore, my results from this SQL Select statement look like:

Query lib Query File id File lib File Rec format --------- ------- ------- -------- --------- ---------- MYLIB QUERY1 T01 MYLIB TESTFILE2 TESTFILER MYLIB QUERY1 T02 MYLIB TESTFILE1 TESTFILE1R MYLIB TESTQRY T01 MYLIB TESTFILE TESTFILER

If I wanted to do this for a several libraries I would create a file to contain the data I wanted to know about the Query Definitions.

First, I need to create a temporary output file created from the DSPPGMREF command that I will use when creating the Table I want to contain my final results. At a command line I type:

01 DSPPGMREF PGM(MYLIB/TESTQRY) 02 OUTPUT(*OUTFILE) 03 OBJTYPE(*QRYDFN) 04 OUTFILE(QTEMP/OUTFILE)

Line 01: I do not need the DSPPGMREF command to run for all Query Definitions, just the results from one, any Query in any library, will do.

Line 04: I am creating the output file in QTEMP, but I could create it in any library.

Now to create my final output file, I am calling QRYFILES.

01 CREATE TABLE MYLIB.QRYFILES 02 (QUERY_LIBRARY,QUERY_NAME,FILE_ID,FILE_LIBRARY,FILE_NAME, 03 RECORD_FORMAT) 04 AS 05 (SELECT WHLIB,WHPNAM,WHSNAM,WHLNAM,WHFNAM,WHRFNM 06 FROM QTEMP.OUTFILE) 07 DEFINITION ONLY ;

Line 1: This table will be created in the library MYLIB.

Lines 2 and 3: This is the column list of the columns that will be in this table. Their definitions are taken from the columns in the Select statement below.

Line 4: AS indicates that the Table is created with the attributes from the following SQL Select statement.

Line 5 and 6: This Select statement uses the columns in the output file I created just a little while ago.

Line 7: I only want to define the file not fill it with data.

Once I run this statement I have my final output file.

The first version of the program to add data to QRYFILES is a CL program:

01 PGM PARM(&LIB) 02 DCL VAR(&LIB) TYPE(*CHAR) LEN(10) 03 DSPPGMREF PGM(&LIB/*ALL) OUTPUT(*OUTFILE) + OBJTYPE(*QRYDFN) OUTFILE(QTEMP/OUTFILE) 04 RUNSQL SQL('INSERT INTO RPGPGM1.QRYFILES + 05 (SELECT WHLIB,WHPNAM,WHSNAM,WHLNAM,WHFNAM,WHRFNM + 06 FROM QTEMP.OUTFILE + 07 WHERE WHOTYP = ''*FILE'' + 08 ORDER BY WHLIB,WHPNAM,WHSNAM) + 09 ') COMMIT(*NC) 10 ENDPGM

Line 1: The name of the library within which to find all the Query Definitions is passed to this program.

Line 3: The DSPPGMREF command creates the list of all the Query's information in a file in QTEMP. This allows me to run more than one version of this program at a time as each one will generate its own output file in its own QTEMP.

Lines 4 – 9: I am using the Run SQL command, RUNSQL, to insert the columns I want from the output file in QTEMP into my final output file QRYFILES.

I can do the same with a RPG program too:

01 **free 02 dcl-pi *n ; 03 Library char(10) ; 04 end-pi ; 05 dcl-s String char(100) ; 06 String = 'DSPPGMREF PGM(' + %trimr(Library) + '/*ALL) OUTPUT(*OUTFILE) + OBJTYPE(*QRYDFN) OUTFILE(QTEMP/OUTFILE)' ; 07 exec sql CALL QSYS2.QCMDEXC(:String) ; 08 exec sql INSERT INTO QRYFILES (SELECT WHLIB,WHPNAM,WHSNAM,WHLNAM,WHFNAM,WHRFNM FROM QTEMP.OUTFILE WHERE WHOTYP = '*FILE' ORDER BY WHLIB,WHPNAM,WHSNAM) ; 09 *inlr = *on ;

Line 1: In 2022 who is not programming in free format RPG?

Lines 2 – 4: Here is where I define the parameter that is being passed to this program.

Line 5 and 6: This is just something I do. If I build a string that includes the values from variables, like the name of the library passed to this program, I like to create that it in a variable. On line 5 I define that variable. And on line 6 I place the value I want into it, which is the string for the DSPPGMREF command.

Line 7: I execute the DSPPGMREF command using the QCMDEXC SQL procedure. As I mentioned in the previous paragraph I like to use a variable, rather than build the string with the procedure, because if this fails I can debug the string passed to it for syntax errors easily.

Line 8: When the QCMDEXC procedure has completed I can then insert the contents from the output file into QRYFILES.

Personally I do not see any preference for using one of these programs rather than other. It is just whichever one you feel most comfortable with.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.