Using SQL to retrieve information about output queues

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.