Wednesday, December 9, 2015

Output queue entries information via SQL

output queue entries

When I heard what was coming in IBM i 7.2 TR3 and 7.1 TR11 I was excited to learn that two of the new introductions would be a SQL View and SQL Table Function to list spool files in output queues. Retention of spool files has always been a contentious issue in all the IBM i shops I have worked. The task of managing spool files is a thankless task, but without it I am always surprised how much disk space is lost to spool files after a few months.

I always wanted a quick and easy way to identify the following information about spool files:

  • How old is the spool file?
  • How big is it?
  • Who generated it?
  • What job generated it?

With the new View and Table function I can get my hands on this information in a couple of the minutes at the most.

To be able get information back from the View and the Table function you will need the following authority:

  • *SPLCTL
  • If not, read authority to the output queue
  • If not, *JOBCTL authority and the output queue has OPRCTL(*YES)

Both the View and the Table function reside in the library QSYS2 and are called:

What is the difference between a View and a Table function? A Table function requires parameters passed to it, a View does not.

 

View: OUTPUT_QUEUE_ENTRIES

When I looked through the available columns in this View I was tempted to call this post "Everything you wanted to know about Spool file, but never dared to ask". There are columns for things I don't think I would want to inquire about. As there are so many I am not going to list them all here, but I will refer you to IBM's page in the Knowledge Center here.

The columns I care about when trying to establish the worst spool file offenders are:

Column Description
SIZE Size of the spool file in kilobytes
SPOOLNAME Spool file name
OUTQ Output queue the spool file is in
OUTQLIB Library the output queue is in
CREATED When the spool file was created (timestamp)
USER_NAME User profile
STATUS Status of the spool file
PAGES Number of pages
JOB_NAME Qualified job name that produced the spool file
FILENUM Spool file number

To find the worst offenders I would want to sort the results of my SQL statement by size, in a descending order:

  SELECT SIZE,SPOOLNAME,OUTQ,OUTQLIB,CREATED,USER_NAME,STATUS,
         PAGES,JOB_NAME,FILENUM 
    FROM QSYS2/OUTPUT_QUEUE_ENTRIES ORDER BY SIZE DESC

The output I would get would look like this:

SIZE   SPOOLNAME   OUTQ        OUTQLIB     CREATED
3,144   QSYSPRT     HOLDQ       QGPL        2015-11-08-17.34.06.452000
  712   QPJOBLOG    PRT01       QGPL        2015-01-24-17.27.23.556000


USER_NAME   STATUS  PAGES   JOB_NAME                   FILENUM
ALLANSK     READY     425   310442/ALLANSK/CREATE_INV        1
SUSANB      HELD       90   066063/SUSANB/RACB1001           9

It would be better for the results to be in a file that I can then work through the list to eliminate the offenders. To do that I could create a simple CL program like this:

01  PGM

02  DLTF FILE(MYLIB/SPLFILES)
03  MONMSG MSGID(CPF2105)

04  RUNSQL SQL('CREATE TABLE MYLIB/SPLFILES AS +
                (SELECT SIZE,SPOOLNAME,OUTQ,OUTQLIB,CREATED,+
                        USER_NAME,STATUS,PAGES,JOB_NAME,FILENUM +
                   FROM QSYS2/OUTPUT_QUEUE_ENTRIES +
                  ORDER BY SIZE DESC +
                  FETCH FIRST 200 ROWS ONLY) +
                WITH DATA') +
           COMMIT(*NONE)

05  ENDPGM

Line 4: I have used the CREATE TABLE to create an output table/file that contains the results of my SQL statement. The SELECT is similar to the one above, the only difference is that I only want the top 200 worst offenders.

Now I can review the contents of my table/file and decided which spool files can be deleted. Using the columns SPOOLNAME, JOB_NAME, and FILENUM I can use the Delete Spool File command, DLTSPLF, to delete the spool files.

 

Table function: OUTPUT_QUEUE_ENTRIES()

The Table function is more restrictive than the View, as you can only run it for one output queue. That is also its advantage, if you only needed the information from one output queue it is faster to use this than it is to use the View.

This table function has three parameters:

  1. Output queue library
  2. Output queue name
  3. Detailed (*YES) or summary (*NO) information

The same columns are available in the detailed information of the Table function as are in the View. The exceptions are the information for the output queue and library, but you already know those to be able to call this function. The summary information contains all the columns I am interested in, therefore, most of the time I will be calling this function with the third parameter being '*NO'. While I did not experience any noticeable difference in the time taken to return the results when calling the function with the detailed and summary information IBM recommends that if you do not need the extra columns available in the detail, call the summary version as it will be quicker.

Unlike the View where the columns have short and long names, the columns names returned by the Table function only have long names (which happen to be the same long names as the View's columns). I am not going to list all of the columns returned by this function here, but I will refer you to the appropriate page in IBM's Knowledge Center here.

In this example I want to find the oldest spool files in the output queue QEZJOBLOG that are ready to print. As I am interested in the same columns as before I am going to use the summary information value.

SELECT CREATE_TIMESTAMP,SIZE,SPOOLED_FILE_NAME,
       TOTAL_PAGES,TOTAL_PAGES,JOB_NAME,FILE_NUMBER
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL','QEZJOBLOG','*NO')) A
WHERE STATUS = 'READY'
ORDER BY CREATE_TIMESTAMP

Notice that at the end of the FROM line there is a character, this can be any character from the alphabet. If this is omitted the statement will not run.

The results look like:

CREAT00001                   USER_NAME  SIZE   SPOOL00001  TOTAL00001
2015-12-04-18.52.29.269000   QPGMR        40   QPJOBLOG             2
2015-12-04-22.13.13.473000   QSECOFR     520   QPJOBLOG            76
2015-12-04-22.58.06.645000   QSYS         32   QPJOBLOG             1
2015-12-04-23.30.56.682000   QSYS         32   QPJOBLOG             1

JOB_NAME                FILE_00001
320855/QPGMR/C********           1
318044/QSECOFR/Q******          15
320849/QSYS/S*********           1
320845/QSYS/S*********           1

 

I can see myself using both of these new offerings frequently as part of my regular disk usage reporting, as to gather the information for old and large spool files is so much simpler than it was before.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.2 TR3 and 7.1 TR11, and will not work with earlier releases or TRs.

10 comments:

  1. Definitely a great enhancement. The machines I work with are still not in 7.1 TR11 but looking forward to it. Thanks a bunch for sharing the information and the examples.

    ReplyDelete
  2. Yes, great and useful point.
    I'll look better into.
    Thanks !!

    ReplyDelete
  3. Any way to get a similar result with previous versions of IBM?

    ReplyDelete
  4. I have been using SQL since 1983 but I still feel total SQL illiterate when I see posts like these.

    For some reason the IBM i community has still not caught up to SQL. There is a lot of fear for SQL. This fear not only puts them backward, but drags us progressives as well.

    There is so much to learn, and we are so far behind. Simon, you have not even begun to introduce all those OLAP techniques that Birgitta writes and makes even me scared.

    ReplyDelete
  5. Is this available in V7R1M0? I can't seem to locate it.

    ReplyDelete
    Replies
    1. It depends which TR you are on, and how you are searching for it.

      If you are running IBM i 7.1 < TR11 then you are not going to be able use this.

      Delete
  6. I'm on 7.1, TR11 and the only available options for anything close in QSYS2 are these:
    OUTQ_DTL, OUTQ_INFO.

    I have 2 SQL's written in ACS that look like this. Modify as needed.
    -- Output Queue Info SQL.
    -- Spooled files by library / output queue.
    Select * from qsys2.outq_info
    --- Where Output_priority <> 5
    --- Where ... there are multiple selections that can be made here. Bring up SQL for field names or use DSPFFD qsys2/outq_info. Using a global "order by" for all of the out's / library's will result in a long run time.
    Where output_queue_library_name = 'xxyyzz' and output_queue_name = 'abc1234'
    And user_data = 'xyz3456'
    Order by create_timestamp desc
    ********************************************************
    --- Output Queue List with count of files SQL.
    Select * from qsys2.outq_dtl
    --- Where
    --- Order by
    -----------------

    ReplyDelete
    Replies
    1. OUTQ_DTL and OUTQ_INFO are the "system" names for these views:

      OUTQ_DTL = OUTPUT_QUEUE_INFO
      OUTQ_INFO = OUTPUT_QUEUE_ENTRIES

      While you can use the "system" names in your SQL code I would recommend using the SQL (long) names so that everyone knows Views you are working with.

      Delete
  7. Why I do not manage to see USRDFNDTA?
    SELECT USER_DEFINED_DATA FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('DETH0PE26', 'DE2601MAIL', 'YES'))
    Column or global variable USER_DEFINED_DATA not found.
    But in the documentation they mention this column...
    Next problem will probably be how to convert it - they defined it as binary and it is a text ... Could you help me out, please.
    I would need to find spoolfiles containing certain text in their USRDFNDTA.

    ReplyDelete
    Replies
    1. This column appears to have only been added to IBM i 7.4 and 7.5 only, and probably via a recent TR.

      SELECT USER_DEFINED_DATA FROM QSYS2.OUTPUT_QUEUE_ENTRIES

      Delete

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.