Wednesday, May 10, 2023

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:

 

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

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.