Retrieving the printer name from a Query

The question was posed if it possible to retrieve the name of printer that has been entered into a Query for all the Queries in a library?

I already knew how to retrieve the SQL statement from a Query. Alas, the information about the printer is not found that way.

After some searching I found a SQL procedure that will give me the information I need: PRINT_QUERY_DEFINITION. For some reason there is no mention of this in IBM's documentation portal. I found reference to it in the IBM Support portal.

PRINT_QUERY_DEFINITION generates a spool file that lists all the information about the Query, including the choice of output.

My scenario is that I want a list of the Queries in a library and which printers they are defined to use.

Not all Queries use printers. Some will only display, and others will output to an output file. For this example, I created four Queries. The files they are built over, fields selected, selection criteria, column formatting, etc. is irrelevant. All that matters is the output type. This can have three values:

1. = Display the results on the screen
2. = Direct the output to a spool file
3. = Direct the output to an output file

I created four Queries:

• TESTQRY:  generates spool file
• TESTQRY1:  generates an output file
• TESTQRY2:  generates spool file
• TESTQRY3:  displays the results on the screen

The choice is made in the "Select output type and output form" selection from the "Define the Query" screen.

Define the Query Query . . . . . . : TESTQRY Option . . . . . : CHANGE Library . . . . : MYLIB CCSID . . . . . . : 37 Type options, press Enter. Press F21 to select all. 1=Select Opt Query Definition Option _ > Specify file selections _ Define result fields _ Select and sequence fields _ Select records _ Select sort fields _ Select collating sequence _ Specify report column formatting _ Select report summary functions _ Define report breaks 1 > Select output type and output form _ Specify processing options

For TESTQRY and TESTQRY2 I selected Output type 2 on the first screen:

Select Output Type and Output Form Type choices, press Enter. Output type . . . . . . . . . . . 2 1=Display 2=Printer 3=Database file

And entered the printer's name on the second:

Define Printer Output Type choices, press Enter. Printer . . . . . . . . . PRT01 *PRINT, name

For TESTQRY1 I selected option 3 for the output to database file:

Select Output Type and Output Form Type choices, press Enter. Output type . . . . . . . . . . . 3 1=Display 2=Printer 3=Database file

FOR TESTQRY3 I left it at the default, for display:

Select Output Type and Output Form Type choices, press Enter. Output type . . . . . . . . . . . 1 1=Display 2=Printer 3=Database file

Now I can use PRINT_QUERY_DEFINITION to print the information about each of these Queries. The syntax is simple:

CALL QSYS2.PRINT_QUERY_DEFINITION( < Library > , < query > , < report type > )

The only parameter that need explanation is the report type. There are two allowed values:

• 0:  Print the results where the library is "*LIBL"
• 1:  Print the results in English

I have found that I get a shorter report that still contains the information I desire if I use "1", therefore, I always use "1".

For my first Query the statement would be:

CALL QSYS2.PRINT_QUERY_DEFINITION('MYLIB','TESTQRY',1)

I am not going to show the entire spool file. Just the "Selected output attributes" section as this allows me to differentiate between the types of output. For TESTQRY and TESTQRY2:

Selected output attributes Output type . . . . . . . . . . . . . . Printer Form of output . . . . . . . . . . . . Detail Line wrapping . . . . . . . . . . . . . No Printer Output Printer device . . . . . . . . . . . . PRT01

For TESTQRY1:

Selected output attributes Output type . . . . . . . . . . . . . . Database file Form of output . . . . . . . . . . . . Detail Line wrapping . . . . . . . . . . . . . No Database file output File . . . . . . . . . . . . . . . . . QQRYOUT Library . . . . . . . . . . . . . . . QTEMP

And finally for TESTQRY3:

TESTQRY3 Selected output attributes Output type . . . . . . . . . . . . . . Display

I can now say if the text "Printer device" appears in the spool file, starting in the fourth position, I know that my Query has been created to output to a spool file.

I am going to need somewhere to place the name of the printer I will be getting using from PRINT_QUERY_DEFINITION's spool file:

01 CREATE TABLE MYLIB.QUERY_PRINTER 02 FOR SYSTEM NAME "QRYPRNTR" 03 (LIBRARY VARCHAR(10), 04 QUERY VARCHAR(10), 05 PRINTER VARCHAR(10)) ;

Line 1: My SQL Table has a long name…

Line 2: And a system name too.

Lines 3 – 5: I only need three pieces of information:

• LIBRARY:  Name of the library the Query is in
• QUERY:  Query's name
• PRINTER:  The printer name defined in the Query

I wrote a RPG program with embedded SQL statements to populate the Table. The program consists of three procedures. The first is the main procedure:

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-pr Main extpgm ; 04 *n char(10) const ; 05 end-pr ; 06 dcl-ds Queries qualified dim(*auto : 10000) ; 07 Library char(10) ; 08 Queries char(10) ; 09 end-ds ; 10 dcl-proc Main ; 11 dcl-pi *n ; 12 inLib char(10) const ; 13 end-pi ; 14 ListQueries(inLib) ; 15 if (%elem(Queries) = 0) ; 16 dsply ('There are no Queries in library ' + inLib) ; 17 return ; 18 endif ; 19 PrintQueries() ; 20 return ; 21 end-proc ;

Line 2: It is always better not to have your program use the RPG cycle. The only way to do this is to use a Main procedure. This is followed by my favorite control options.

Lines 3 – 5: This is the "input parameter list" for the Main procedure. A ten long character value will be passed to the program, this is the library for the Queries in question.

Lines 6 – 9: This is the only "global variable" within this program, a variable that is available to all the subprocedures. It is the definition of the data structure that will contain the list of Queries retrieved from the library. I have defined the data structure as a auto-extending length array.

Line 10: Start of the Main procedure.

Lines 11 – 13: This is the procedure interface for the Main procedure. This defines that the only input parameter is the library name.

Lines 14: I am calling the subprocedure to get the list of Queries. I am passing to it the name of the library that was passed to the Main procedure.

Lines 15 – 18: If the array contains zero elements then there were no Queries found in the previous subprocedure. The Program sends a message to the screen and then returns (ends).

Line 19: As there are Queries the subprocedure is called that prints the Queries and insert their information into the SQL Table I created earlier.

Line 21: End of the Main procedure.

Next is the subprocedure to get the list of Queries in the library:

22 dcl-proc ListQueries ; 23 dcl-pi *n ; 24 Library char(10) const ; 25 end-pi ; 26 dcl-s Rows uns(10) inz(%elem(Queries : *max)) ; 27 exec sql DECLARE C0 CURSOR FOR 28 SELECT OBJLIB,OBJNAME 29 FROM TABLE (OBJECT_STATISTICS(:Library,'*QRYDFN','*ALL')) 30 FOR READ ONLY ; 31 exec sql OPEN C0 ; 32 exec sql FETCH C0 FOR :Rows INTO :Queries ; 33 exec sql CLOSE C0 ; 34 return ; 35 end-proc ;

Lines 22 – 24: I passed the name of the library to the subprocedure, therefore, I need a procedure interface in this subprocedure.

Line 26: As I am only going to use the variable in this subprocedure, I have defined it in the procedure. This means that it is a "local" variable, that cannot be used outside of this subprocedure.

Lines 27 – 30: I am using the OBJECT_STATISTICS SQL Table function to get a list of all the Queries in the library. The only columns I need in the results the object library and object name.

Line 31: I open the cursor.

Line 32: I am performing a multi-row fetch to get the results directly into the data structure array.

Line 33: Close the cursor.

The final subprocedure uses the PRINT_QUERY_DEFINITION to list the Queries' definitions, determines if the definition includes a printer, and if so inserts into the output Table.

36 dcl-proc PrintQueries ; 37 dcl-ds SingleDS likeds(Queries) ; 38 dcl-s Printer char(10) ; 39 for-each SingleDS in Queries ; 40 exec sql CALL QSYS2.PRINT_QUERY_DEFINITION 41 (:SingleDS.Library,:SingleDS.Queries,1) ; 42 Printer = ' ' ; 43 exec sql SELECT SUBSTR(SPOOLED_DATA,44,10) INTO :Printer 44 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 45 JOB_NAME => '*', 46 SPOOLED_FILE_NAME => 'QPQUPRFIL', 47 SPOOLED_FILE_NUMBER => '*LAST')) 48 WHERE SUBSTR(SPOOLED_DATA,4,14) = 'Printer device'; 49 if (Printer <> ' ') ; 50 exec sql INSERT INTO QUERY_PRINTER 51 VALUES(:SingleDS.Library,:SingleDS.Queries,:Printer) ; 52 endif ; 53 exec sql call QSYS2.QCMDEXC('DLTSPLF FILE(QPQUPRFIL) SPLNBR(*LAST)') ; 54 endfor ; 55 return ; 56 end-proc ;

Line 37: I need this non-array data structure, which I define with the LIKEDS, as I will be using a For each loop in this procedure.

Line 38: I need a variable name for the printer name, if I find one.

Line 39: Here is the For each loop. With each iteration of the For each one element at a time is moved the data structure array, Queries, to a non-array data structure, SingleDS.

Lines 40 and 41: Call the PRINT_QUERY_DEFINITION passing to it the Query's library, Query's name, and I want the shorter spool file.

Lines 43 – 48: I am using the SQL Table function to SYSTOOLS.SPOOLED_FILE_DATA to search the generated spool file for the string "Printer device". The parameters I used for this Table function make it so easy to get the data from the spool file.

Line 45: I pass the "*" for the job name, we all know that means the current job.

Line 46: The spool file name is always going to be QPQUPRFIL.

Line 47: As I want the last spool file generated by this job and for this spool file I do not have to give the spool file number, I can just pass "*LAST".

Line 48: Then I only want the rows that have "Printer device" starting in the fourth position of the line.

Line 43: When I find the row I want to substring the printer name from the same row, and place the result in the RPG variable Printer.

Line 49: If the above SQL statement fails, it will contain the value of the last successful find in the Printer variable, which is why I move blanks to the printer name on line 42. If the printer name is not blank, I know that a printer was found for this Query.

Lines 50 and 51: I insert a row into the output Table.

Line 53: As I have finished with this spool file I delete it using the Delete spool file command in the SQL QCMDEXC procedure.

When compiling this program, I get a level 10, warning error, for this line:

03 dcl-pr Main extpgm ;

To stop the warning I would need to change the line to include the name of this program:

03 dcl-pr Main extpgm(TESTRPG) ;

I do not care about the warning error, as I know that my first line 3 can only apply to this program. Therefore, I leave line 3 without the name of the external program.

After running the program I use the following SQL statement to check if any of the Queries in my library have a printer defined:

01 SELECT * FROM QUERY_PRINTER

Which returns to me the two Queries I expect:

Library QUERY PRINTER ------- --------- -------- MYLIB TESTQRY PRT01 MYLIB TESTQRY2 PRT01

What I have described here is only to retrieve the name of the printer from the Query's definition. If I want a list of which Queries print I would need to take into consideration all occurrences of the Run Query CL command, RUNQRY, as using it I can:

01 RUNQRY QRY(TESTQRY) OUTTYPE(*PRINTER) PRTDEV(PRT02) 02 RUNQRY QRY(TESTQRY1) OUTTYPE(*PRINTER) PRTDEV(PRT01) 03 RUNQRY QRY(TESTQRY) OUTTYPE(*OUTFILE) OUTFILE(QTEMP/OUTFILE)

Line 1: TESTQRY was defined to use PRT01, this will send its spool file to PRT02.

Line 2: TESTQRY1 was defined to output to a database file, I have overridden it to print to PRT01.

Line 3: I can also direct TESTQRY to an output file, and not print.

Just keep this in mind.

You can learn more about the PRINT_QUERY_DEFINITION SQL Procedure from the IBM Support website here.

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