Another way to list files defined in Querys

After writing the post about Retrieving the print file name from a Query it struck me that I could use the same methodology to answer a question I have been asked on several occasions: Which Querys are using a particular file?

I cannot answer which Querys are using a file. I can list all the files that Querys are defined to use.

What are the steps I need to perform to get that information:

1. Make a list of all the Querys in a particular library
2. Print their definitions using PRINT_QUERY_DEFINITION
3. Extract the file names from the spool file, and write them to an output file

I am going to show you how I manually executed the above steps. And then I will show a program I created to do the same thing.

First, I need to make a list of all the Querys in a library, MYLIB. I will use the OBJECT_STATISTICS SQL Table function.

01 SELECT OBJLIB,OBJNAME 02 FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*QRYDFN','*ALL'))

Line 1: I only want the Query library and name in my results.

Line 2: I pass to OBJECT_STATISTICS the library name, MYLIB, the object type, *QRYDFN, and I want all of these objects.

The results show that I have six Querys.

OBJLIB OBJNAME ---------- ---------- MYLIB PERSON_QRY MYLIB QUERY1 MYLIB TESTQRY MYLIB TESTQRY1 MYLIB TESTQRY2 MYLIB TESTQRY3

I can now call PRINT_QUERY_DEFINITION:

01 CALL PRINT_QUERY_DEFINITION('MYLIB','QUERY1',1)

Line 1: I want to print the definition of QUERY1.

The definition is printed to the spool file QPQUPRFIL. I can use the Work with Spool File command, WRKSPLF, to show all my spool files and select the one I want. Or I could use the Display Spool File command, DSPSPLF, and open the file directly:

01 DSPSPLF FILE(QPQUPRFIL) SPLNBR(*LAST)

Why am I displaying the contents of the spool file with a command rather than SQL? I find it easier to determine the position the columns of information from the command's output as it has a ruler at the top.

I searched through the spool file until I found the information about the files:

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+. Selected files ID File Library Member Record Format Format Level ID T01 TESTFILE2 MYLIB *FIRST TESTFILER 353273F775B90 T02 TESTFILE1 MYLIB *FIRST TESTFILE1R 2E59045D652A0

The above shows me the following columns of information:

Data	Start position	Length
File name	11	10
Library	26	10
Member	40	10
Record format	54	10
Format level id	71	13

Why do I care about the Formal level id? I needed to find something that is unique to records in the spool file about the files. It just so happens that if starting in position 71 and 13 long, does not contain a blank this is a record about the files.

I use the SPOOLED_FILE_DATA Table function to display the contents of the spool file. This statement creates the columns of data I desire, and select the file records:

01 SELECT SUBSTR(SPOOLED_DATA,26,10) AS "Library", 02 SUBSTR(SPOOLED_DATA,11,10) AS "File", 03 SUBSTR(SPOOLED_DATA,40,10) AS "Member", 04 SUBSTR(SPOOLED_DATA,54,10) AS "Record format" 05 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 06 JOB_NAME => '781215/SIMON/QPRTJOB', 07 SPOOLED_FILE_NAME => 'QPQUPRFIL', 08 SPOOLED_FILE_NUMBER => '*LAST')) 09 WHERE NOT REGEXP_LIKE(SUBSTR(SPOOLED_DATA,71,13),'.[ ]')

Lines 1 – 4: These are the columns I want in my results.

Line 6: As this is only an example I have hardcoded the job name into the statement.

Line 7: The spool file is called QPQUPRFIL.

Line 8: And I want the last spool file that name.

Line 9: I am using a Regular Expression to check if this is a record about the files. The expression to use to check characters is REGEXP_LIKE. The expression is checking if any of the characters are a blank. The period ( . ) acts as a wild card, and the "[ ]" is a blank. As there is a "NOT" following the Where this will select only records where that are not any blanks in the 71st – 83rd positions.

LIBRARY FILE MEMBER RCDFMT ---------- ---------- ---------- ---------- MYLIB TESTFILE2 *FIRST TESTFILER MYLIB TESTFILE1 *FIRST TESTFILE1R

I am going to use this statement to create a SQL View. I can then use this View to return the columns of information I want for the files:

01 CREATE OR REPLACE VIEW MYLIB.QUERY_DEFINITION_FILES 02 FOR SYSTEM NAME "QRYDFNFILE" 03 (LIBRARY,FILE,MEMBER,RCDFMT) 04 AS 05 SELECT SUBSTR(SPOOLED_DATA,26,10), 06 SUBSTR(SPOOLED_DATA,11,10), 07 SUBSTR(SPOOLED_DATA,40,10), 08 SUBSTR(SPOOLED_DATA,54,10) 09 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 10 JOB_NAME => (SELECT JOB_NAME 11 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 12 WHERE USER_NAME = CURRENT_USER 13 ORDER BY CREATE_TIMESTAMP DESC 14 LIMIT 1), 15 SPOOLED_FILE_NAME => 'QPQUPRFIL', 16 SPOOLED_FILE_NUMBER => '*LAST')) 17 WHERE NOT REGEXP_LIKE(SUBSTR(SPOOLED_DATA,71,13),'.[ ]')

Line 1: I always like to give my SQL objects a long descriptive name.

Line 2: I also like to give them a system name, rather than have the operating system make one for me.

Line 3: This is the column list, giving the substring-ed columns their names.

Lines 5 – 9: This is the same as the previous SQL statement.

Lines 10 – 14: I need to retrieve the job name from the most recent spool file using the OUTPUT_QUEUE_ENTRIES_BASIC View.

lines 15 – 17: Same as the previous statement.

After creating the View I then execute the following:

01 SELECT * FROM MYLIB.QUERY_DEFINITION_FILES

And the same results as before are returned.

LIBRARY FILE MEMBER RCDFMT ---------- ---------- ---------- ---------- MYLIB TESTFILE2 *FIRST TESTFILER MYLIB TESTFILE1 *FIRST TESTFILE1R

Having established how to get to the information, I created a program to capture this information for all the Querys in a library.

First I need an output file to contain the information. This is the DDL table I created:

01 CREATE TABLE MYLIB.QUERY_USED_FILES 02 FOR SYSTEM NAME "QRYUSEDFIL" 03 ( QUERY_LIBRARY FOR COLUMN "QRYLIB" VARCHAR(10), 04 QUERY_NAME FOR COLUMN "QRYNAME" VARCHAR(10), 05 FILE_LIBRARY FOR COLUMN "FILELIB" VARCHAR(10), 06 FILE_NAME FOR COLUMN "FILENAME" VARCHAR(10), 07 FILE_MEMBER FOR COLUMN "FILEMBR" VARCHAR(10), 08 FILE_RECORD_FORMAT FOR COLUMN "FILERCDFMT" VARCHAR(10)) ; 09 LABEL ON TABLE QUERY_USED_FILES IS 'QUERY_USED_FILES' ;

Line 1: The long name for this table.

Line 2: The short system name for the table.

Lines 3 – 8: All of the column names are self-explanatory. I have given short system names too.

Line 9: This will give the new table its description text.

As I now have an output file I need a RPG program to fill it with the file information for all the Querys in a library. Let me start with the global level definitions and the Main procedure:

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-s Library char(10) inz('MYLIB') ; 04 dcl-ds Querys qualified dim(*auto : 9999) ; 05 Library char(10) ; 06 Name char(10) ; 07 end-ds ; 08 dcl-proc Main ; 09 GetQuerys() ; 10 WriteFileInfo() ; 11 return ; 12 end-proc ;

Line 2: A Main procedure stops the RPG cycle code from being included in the program when it is compiled. I always use the Source statement control option, as it will make the compiler use the source line numbers, rather than generate its own line numbers. I need not to be in the default activation group as I have two subprocedures.

Line 3: This variable is initialized with the name of the library I want to retrieve the definition of the Querys from.

Lines 4 – 7: This data structure is in the global section, therefore it can be used by all subprocedures. It's an auto-extending data structure. It will be used to contain the details of all the Querys found in the library.

Line 8: Start of the Main procedure.

Line 9: Call the subprocedure to return the data structures found in the library, it populates the data structure array Querys.

Line 10: Call the subprocedure to generate the spool file, extract the file information from it, and write it to the output file.

This is the subprocedure to get the list of Querys:

13 dcl-proc GetQuerys ; 14 dcl-s Rows uns(10) inz(%elem(Querys : *max)) ; 15 exec sql DECLARE C0 CURSOR FOR 16 SELECT OBJLIB,OBJNAME 17 FROM TABLE(QSYS2.OBJECT_STATISTICS(:Library , '*QRYDFN' , '*ALL')) 18 FOR READ ONLY ; 19 exec sql OPEN C0 ; 20 exec sql FETCH C0 FOR :Rows INTO :Querys ; 21 on-exit ; 22 exec sql CLOSE C0 ; 23 end-proc ;

Line 14: Define a variable that contains the maximum possible array elements for the array Querys. This allows me to just change the size of the data structure array, without needing to make any other changes.

Line 15 – 18: Declare the cursor to return all of the Querys from the library.

Line 19: Open the cursor.

Line 20: Fetch the library and Query names from the results into the data structure array.

Line 21 and 22: The close cursor is in the ON-EXIT section so that it will be performed whether this subprocedure ends successfully or not.

Onto the subprocedure that gets the information about the files, and writes it to the output file:

24 dcl-proc WriteFileInfo ; 25 dcl-ds SingleQuery likeds(Querys) ; 26 exec sql DELETE FROM QUERY_USED_FILES ; 27 for-each SingleQuery in Querys ; 28 exec sql CALL PRINT_QUERY_DEFINITION(:SingleQuery.Library, 29 :SingleQuery.Name, 30 1) ; 31 exec sql INSERT INTO QUERY_USED_FILES 32 SELECT :SingleQuery.Library, 33 :SingleQuery.Name, 34 A.* 35 FROM QUERY_DEFINITION_FILES A ; 36 exec sql CALL QSYS2.QCMDEXC('DLTSPLF FILE(QPQUPRFIL) SPLNBR(*LAST)') ; 37 endfor ; 38 end-proc ;

Line 25: I am going to need this data structure when I use the FOR-EACH operation code to "read" the data structure array.

Line 26: I delete any existing records from the output file.

Line 27: Start of the FOR-EACH loop, which takes, in turn, each element from the data structure array and places it in the other data structure I defined on line 25.

Lines 28 - 30: Print the Query definition.

Lines 31 – 35: Copy all the rows from the View, I created, into the output file, along with the Query library and name as the first two columns.

Line 36: Delete the spool file, using the QCMDEXC SQL procedure, as it is no longer needed.

Once created, I called the program, and checked on the contents of the output file:

01 SELECT * FROM MYLIB.QUERY_USED_FILES

Which returned the list of the files used by the Querys in my library:

FILE_ QUERY_ FILE_ FILE_ FILE_ RECORD_ LIBRARY QUERY_NAME LIBRARY NAME MEMBER FORMAT ---------- ---------- ---------- ---------- ---------- ---------- MYLIB PERSON_QRY MYLIB PERSON *FIRST RPERSON MYLIB QUERY1 MYLIB TESTFILE2 *FIRST TESTFILER MYLIB QUERY1 MYLIB TESTFILE1 *FIRST TESTFILE1R MYLIB TESTQRY MYLIB PERSON *FIRST RPERSON MYLIB TESTQRY1 MYLIB TESTFILE *FIRST TESTFILER MYLIB TESTQRY2 MYLIB PERSON *FIRST RPERSON MYLIB TESTQRY3 MYLIB TESTFILE *FIRST TESTFILER

A warning, as I said that the start of this post "I can list all the files that Querys are defined to use". Why do I say that? I can use different files with the Run Query command, RUNQRY, than the ones I used in the definition. For example, if FILE_X has the same definition as TESTFILE2 and FILE_Y is the same as TESTFILE1 I can still use QUERY1 to report:

01 RUNQRY QRY(QUERY1) QRYFILE((FILE_X) (FILE_Y))

If you are looking for a definitive list of all the files that are used by the Querys in a library you will need to run a source member search for all occurrence of the RUNQRY command too.

 

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