System view QSYS2/OUTPUT_QUEUE_ENTRIES performance

[tdavis]

I am using system view QSYS2/OUTPUT_QUEUE_ENTRIES to get a listing of spool files, in an RPG program to display those files for the user.
It works ok except that it is slow. I used debug and ACS to see if OS400 has any performance suggestions. Appears that it does not.

Anyone run into this and might have an idea of how to improve performance when reading this system view?
Or maybe a different approach that might be more efficient?

Thanks!

[matt______]

The approach I use is this

select
char(QE.SPOOLED_FILE_NAME)    as SFFILE
,char(QE.JOB_USER)             as SFUSER
,char(QE.SYSTEM)               as SFSYS
,char(QE.OUTPUT_QUEUE)         as SFOUTQ
,char(QE.OUTPUT_QUEUE_LIBRARY) as SFOUTL
,char(ifnull(QE.USER_DATA,' '))            as SFUDTA
,case QE.STATUS
    when 'READY' then 'RDY '
    when 'OPEN' then 'OPN '
    when 'CLOSED' then 'CLO '
    when 'SAVED' then 'SAV '
    when 'WRITING ' then 'WTR '
    when 'HELD' then 'HLD '
    when 'MESSAGE WAITING' then 'MSG'
    when 'PENDING' then 'PND '
    when 'PRINTING' then 'PRT '
    when 'DELETED' then 'FIN '
    when 'SENDING' then 'SND '
    when 'DEFERRED' then 'DFR '
else trim(QE.STATUS) end as SFSTS
,dec(ifnull(QE.TOTAL_PAGES,0),9)   as SFNPAG
,dec(0,9,0)              as SFWRTP
,dec(QE.COPIES,9)        as SFCPY
,QE.FORM_TYPE            as SFFTYP
,varchar(QE.OUTPUT_PRIORITY)      as SFPTY
,varchar_format(QE.CREATION_TIMESTAMP,'YYMMDD') as SFDAT
,varchar_format(QE.CREATION_TIMESTAMP,'HH24MISS') as SFTIM
,QE.JOB_NAME             as SFJNAM
,QE.JOB_NUMBER           as SFJNBR
,varchar(QE.SPOOLED_FILE_NUMBER)  as SFFNB2
from table (QSYS2.SPOOLED_FILE_INFO(USER_NAME => ucase(:S1PUSER)) ) QE
where FILE_AVAILABLE = '*FILEEND'​

This statement is very fast. I have run it over users that have 100000+ spooled files and it returns within about 30 seconds.

[tdavis]

Mine is similar and also takes 30 seconds or less for 1,000 or so spool files. At first, I was creating an output file using DECLARE GLOBAL, but that was VERY slow, so I changed to using FETCH and it runs faster, loading one subfile page at a time. But I consider 30 second to be a little slow, not fast, but I guess the users can live with that. Thank you for your response.

Select
OUTPUT_QUEUE_NAME as OUTQ,
OUTPUT_QUEUE_LIBRARY_NAME as OUTQLIB,
CREATE_TIMESTAMP as CREATED,
SPOOLED_FILE_NAME as SPOOLNAME,
USER_NAME as USER_NAME,
USER_DATA as USER_DATA,
TOTAL_PAGES as PAGES,
JOB_NAME as JOB_NAME
,FILE_NUMBER as FILENUM
From QSYS2/OUTPUT_QUEUE_ENTRIES
WHERE JOB_NAME like '%PAL_DS_PUT%' and SPOOLNAME = 'QPRINT'
Order by CREATED desc

[B.Hauser]

If you you know the (qualified) Outqueue and do not need *ALL OutQueues you should use the Output_Queue_Entries User Defined Table Function:
https://www.ibm.com/docs/en/i/7.5?topic=services-output-queue-entries-table-function

If you do not need all columns of the QUTPUT_QUEUE_ENTRIES view you may also check the Output_Queue_Entries_Basic view

https://www.ibm.com/docs/en/i/7.5?topic=services-output-queue-entries-basic-view

[tdavis]

I will take a look at OUTPUT_QUEUE_ENTRIES_BASIC. Although my program does not know specifically what output queues the user is looking for. My program prompts them for a job name. Then the program gathers those jobs spool files and displays a list, from which they can display, email, delete, etc. Similar to WRKJOB (except for email) but that command allows only one selection at a time, which is annoying. Thank you for your response!

[MFisher]

Deleted... wrong thread