Wednesday, December 22, 2021

Using SQL to retrieve information about output queues

get output queue data from sql view

I have written so many posts there are times when I think I have written one about something useful, and I find to my surprise that I have not. I use the SQL view OUTPUT_QUEUE_INFO often, and I found that I have not written about it. This post makes amends for that oversight.

OUTPUT_QUEUE_INFO has been around since IBM i 7.2. It returns similar information to the Work Output Queue command, WRKOUTQ, whose output is familiar to everyone who uses IBM i.

                         Work with All Output Queues

Type options, press Enter.
  2=Change   3=Hold     4=Delete   5=Work with   6=Releas
  9=Work with Writers   14=Clear

Opt   Queue       Library      Files    Writer     Status 
      OUTPUT      #SYSLOADX        0                RLS
      CGIDEV2     CGIDEV2          0                RLS
      DATAOUTQ    DATALIB          0                RLS
      IASAUDIT    IASUSR13         0                HLD

The view OUTPUT_QUEUE_INFO makes it so much easier to select and sort the information I desire.

For example, if I wanted to list the output queues with the most spool files in them I can simply use the following:

01  SELECT OUTPUT_QUEUE_NAME AS "Queue",
02         OUTPUT_QUEUE_LIBRARY_NAME AS "Library",
03         TO_CHAR(NUMBER_OF_FILES,'999G999G999') AS "Files",
04         OUTPUT_QUEUE_STATUS AS "Status"
05    FROM QSYS2.OUTPUT_QUEUE_INFO 
06   WHERE NUMBER_OF_FILES >= 10
07   ORDER BY 3 DESC,2,1

Lines 1 – 4: I want my result to contain the columns for the output queue, the library it resides in, the number of spool files it contains, and whether the output queue is released or not.

Line 3: I always find it easier to understand a number if thousand separators are present. To "add" those to the number I use the TO_CHAR function.

Line 6: I only want the output queues that contain ten or more spool file returned.

Line 7: Rather than listing the column names in the ORDER BY I can use a number that refers to the column's position in the results. In this case the results will be sorted by:

  1. 3 = NUMBER_OF_FILES in descending order
  2. 2 = OUTPUT_QUEUE_LIBRARY_NAME
  3. 1 = OUTPUT_QUEUE_NAME

When I executed the statement above on the server I use for testing the results were:

Queue      Library  Files   Status
---------  -------  ------  --------
QEZJOBLOG  QUSRSYS  10,930  RELEASED
QPRINT     QGPL      1,937  RELEASED
PEOPLE9    QGPL         57  RELEASED
PEOPLE8    QGPL         37  RELEASED
PEOPLE1    QGPL         35  RELEASED
PEOPLE2    QGPL         23  RELEASED
PEOPLE5    QGPL         21  RELEASED
PEOPLE7    QGPL         21  RELEASED
OBTPGM02   QGPL         14  RELEASED

If wanted to "drill down" into the output queues' entries to determine which ones I could delete I would use the OUTPUT_QUEUE_ENTRIES view or table function.

I often use this view to check that certain output queues are released. If they are held, I release them. I can get a list of all held output queues using the following statement:

SELECT OUTPUT_QUEUE_NAME AS "Queue",
       OUTPUT_QUEUE_LIBRARY_NAME AS "Library",
       TO_CHAR(NUMBER_OF_FILES,'999G999G999') AS "Files",
       OUTPUT_QUEUE_STATUS AS "Status"
FROM QSYS2.OUTPUT_QUEUE_INFO 
WHERE OUTPUT_QUEUE_STATUS = 'HELD'
ORDER BY 1,2

The results are as follows:

Queue      Library  Files   Status
---------  -------  ------  --------
AUDIT1     QGPL          0  HELD
JONPAUL    QGPL          0  HELD

I can even write a RPG program to check if an output queue is released, and if it is held release it.

01  **free
02  dcl-s Status char(10) ;

03  exec sql SELECT OUTPUT_QUEUE_STATUS INTO :Status
04             FROM QSYS2.OUTPUT_QUEUE_INFO
05            WHERE OUTPUT_QUEUE_LIBRARY_NAME = 'MYLIB'
06              AND OUTPUT_QUEUE_NAME = 'MYOUTQ' ;

07  if (Status = 'HELD') ;
08    exec sql CALL QSYS2.QCMDEXC('RLSOUTQ OUTQ(MYLIB/MYOUTQ)') ;
09  endif ;

10  *inlr = *on ;

Line 2: This variable will contain the status of the output queue returned from the SQL statement.

Lines 3 – 6: The SQL statement to retrieve the status of my output queue. By using the INTO I place the value from OUTPUT_QUEUE_STATUS into the RPG variable Status.

Lines 7 – 9: If the status of my output queue is held I release it using the appropriate IBM i command, using the SQL QCMDEXC procedure.

Another piece of information I can get from this View is for output queues configured for an IP connection. Here I can go and get the IP address and the name of the remote output queue:

01  SELECT OUTPUT_QUEUE_NAME AS "Queue",
02         OUTPUT_QUEUE_LIBRARY_NAME AS "Library",
03         NETWORK_CONNECTION_TYPE AS "Type",
04         REMOTE_SYSTEM_NAME AS "Rmt sys",
05         REMOTE_PRINTER_QUEUE AS "Rmt outq"
06    FROM QSYS2.OUTPUT_QUEUE_INFO
07   WHERE NETWORK_CONNECTION_TYPE = '*IP'

Lines 3 – 5: There are three columns I have not mentioned before:

  • NETWORK_CONNECTION_TYPE this returns the type of connection to the remote partition. In this case I am only interest in IP connections. Including this column in the results is redundant, but I wanted to include it so there is no confusion what these results show.
  • REMOTE_SYSTEM_NAME name of the remote partition or system.
  • REMOTE_PRINTER_QUEUE Name of the remote printer.

Line 7: Here is where I select only those remote output queues that connect via IP.

I only have a few results. Two to non-IBM i system, and one to another partition.

Queue   Library  Type  Rmt sys      Rmt outq
------- -------  ----  -----------  --------
VPRTPDF QGPL     *IP   99.99.99.01  PDFPRT
VPRT01  QGPL     *IP   OTHERSYS     PRT01
VPRT02  QGPL     *IP   99.99.99.02  LP

These are just a few ways I have used the OUTPUT_QUEUE_INFO view. I am sure you can use the same or create your own SQL statements to get to the information you desire.

 

You can learn more about the OUTPUT_QUEUE_INFO SQL view from the IBM website here.

 

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

3 comments:

  1. You did a fine job. Thanks.

    ReplyDelete
  2. Simon, thanks for sharing, the things you can do with SQL. Great read and examples.

    ReplyDelete

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.