New columns added to OUTPUT_QUEUE_ENTRIES

Two new columns were added to the OUTPUT_QUEUE_ENTRIES SQL View and SQL Table Function as part of IBM i 7.5 Technology Refresh 1 and IBM i 7.4 TR7, and while I wonder how often I will use them I do find the information they return interesting.

These columns are:

1. TOTAL_RECORDS:  Total number of records in the spool file. Will be null unless the spool file is *AFPDS, *AFPDSLINE or *LINE, or the file is open
2. MAXIMUM_RECORDS:  The maximum number of records that the spool file can contain, when the file was first opened

It is possible for the number of TOTAL_RECORDS to be greater than the MAXIMUM_RECORDS if the file full message was encountered and I answered it either by extending the file by a number of records, or changing it to a size of *NOMAX.

As the total records column is only populated for "special" spool files let me go ahead and create one:

01 OVRPRTF FILE(QPQUPRFIL) DEVTYPE(*AFPDS) MAXRCDS(*NOMAX) SPLFNAME(SIMON_TEST) OVRSCOPE(*JOB) 02 RUNQRY QRY(*NONE) QRYFILE((TESTTABLE)) OUTTYPE(*PRINTER)

Line 1: I am overriding the default spool file for output from Query to be *AFPDS, change the file size to be *NOMAX, and change the spool file name.

Line 2: Then I just run an ad hoc Query statement to the printer. What I mean by ad hoc is that there is no pre-existing Query.

I am going to look at the spool files in my output queue, MYOUTQ which is in the library QGPL. In this first example I am going to use the OUTPUT_QUEUE_ENTRIES View:

01 SELECT OUTPUT_QUEUE_LIBRARY_NAME AS "Lib", 02 OUTPUT_QUEUE_NAME AS "Outq", 03 SPOOLED_FILE_NAME AS "Splf", 04 TOTAL_PAGES AS "Pages", 05 TOTAL_RECORDS, 06 TO_CHAR(MAXIMUM_RECORDS,'999G999') AS "Max recs" 07 FROM QSYS2.OUTPUT_QUEUE_ENTRIES 08 WHERE (OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME) = ('QGPL','MYOUTQ')

Lines 1 – 4: These columns were in the old version of OUTPUT_QUEUE_ENTRIES.

Line 5: New column TOTAL_RECORDS, remember this will be null for spool files that are not AFPD or line type.

Line 6: New column MAXIMUM_RECORDS, this will be null if the spool file is set with a size of *NOMAX. I have used the TO_CHAR to insert thousand separators into the number, which makes the result easier to understand.

line 8: Just another way of doing a Where clause, containing two columns and two comparisons.

The results look like:

TOTAL_ Lib Outq Splf Pages RECORDS Max records ---- ------ ---------- ----- ------- ----------- QGPL MYOUTQ TESTRPG 7 <NULL> 100,000 QGPL MYOUTQ QPQUPRFIL 3 <NULL> 100,000 QGPL MYOUTQ SIMON_TEST 3 44 <NULL>

The first two spool files have the "standard" device type, therefore, they do not return a value for the Total Records column. They both have a default size of 100,000 records, which is shown in the 'Max records' column.

The third spool file is the one I created above. The file is AFPDS, therefore. the Total Records contains the count of records in this spool file. As I overrode the spool file's size to *NOMAX the 'Max records' is null.

Using the View is not the efficient way to get data about the spool files in a specific output queue, using the Table Function is preferred way, as I already know the output queue and its library.

The statement below is what I would use to get the same information as I did from the View, except I do not need the output queue's name nor the library that it is in.

01 SELECT SPOOLED_FILE_NAME AS "Splf", 02 TOTAL_PAGES AS "Pages", 03 TOTAL_RECORDS, 04 TO_CHAR(MAXIMUM_RECORDS,'999G999') AS "Max recs" 05 FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES( 06 OUTQ_LIB => 'QGPL', 07 OUTQ_NAME => 'MYOUTQ', 08 DETAILED_INFO => 'YES'))

Lines 6 – 8: The Table Function has three parameters:

1. OUTQ_LIB:  Name of the library the output queue is in
2. OUTQ_NAME:  Output queue name
3. DETAILED_INFO:  Can either be 'YES' or 'NO', the default is 'NO'. I need the detailed information to have data in these two columns.

The results for the above statement are:

TOTAL_ Splf Pages RECORDS Max records ---------- ----- ------- ----------- TESTRPG 7 <NULL> 100,000 QPQUPRFIL 3 <NULL> 100,000 SIMON_TEST 3 44 <NULL>

If had used this instead:

08 DETAILED_INFO => 'NO'))

Values for these new columns are not returned, and they are null:

TOTAL_ Splf Pages RECORDS Max records ---------- ----- ------- ----------- TESTRPG 7 <NULL> <NULL> QPQUPRFIL 3 <NULL> <NULL> SIMON_TEST 3 <NULL> <NULL>

As I said at the start of this post I am not sure how I would ever use this information in a practical way, but I do find the information interesting.

You can learn more about this from the IBM website:

• OUTPUT_QUEUE_ENTRIES SQL View
• OUTPUT_QUEUE_ENTRIES SQL Table Function

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.