SQL equivalent of WRKSPLF, faster way of getting information about my spool files?

One of the first commands we all learn with IBM i is the Work with Spool Files command, WRKSPLF. With IBM i releases 7.5 and 7.4 TR6 comes a new SQL Table Function that is the equivalent of WRKSPLF.

The new Table Function, SPOOLED_FILE_INFO is found in library QSYS2, has parameters that match those of the WRKSPLF command. The syntax for the Table Function is:

SELECT * FROM TABLE(QSYS2.SPOOLED_FILE_INFO( USER_NAME => '*CURRENT', STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 6 MONTHS, ENDING_TIMESTAMP => CURRENT_TIMESTAMP - 2 DAYS, STATUS => '*READY *HELD', JOB_NAME => '*', OUTPUT_QUEUE => '*LIBL/MYOUTQ', USER_DATA => 'SQL', FORM_TYPE => '*STD', SYSTEM_NAME => 'DEV750'))

These parameters and their WRKSPLF equivalents are:

SPOOLED_FILE_INFO parameter	Description	WRKSPLF parameter
USER_NAME	Can be user profile, *ALL, or *CURRENT. Default is *CURRENT, like WRKSPLF>.	SELECT, user
STARTING_TIMESTAMP	Start of timestamp range for spool files to return.	PERIOD, beginning time and date
ENDING_TIMESTAMP	End of the timestamp range.	PERIOD, end time and date
STATUS	Status of the spool file. If more than one status is wanted they can be listed, separated by a space.	N/A
JOB_NAME	Full qualified job name.	JOB
OUTPUT_QUEUE	Qualified output queue name or *ALL. If given must as library/output_queue. Default is *ALL, like WRKSPLF.	SELECT, print device
USER_DATA	User data or *ALL. Default is *ALL, like WRKSPLF.	SELECT, user data
FORM_TYPE	Form type, *ALL, or *STD. Default is *ALL, like WRKSPLF	SELECT, form type
SYSTEM_NAME	The system the spool file was generated on, *ALL, or *CURRENT. Default is *ALL.	N/A

We are all familiar with the WRKSPLF screen:

Work with All Spooled Files Type options, press Enter. 1=Send 2=Change 3=Hold 4=Delete 5=Display 6=Release 7=Messages 8=Attributes 9=Work with printing status Device or Total Cur Opt File User Queue User Data Sts Pages Page Copy _ PGM01 SIMON MYOUTQ RDY 11 1 _ PGM01 SIMON MYOUTQ RDY 11 1 _ DSPF01 SIMON MYOUTQ RDY 4 1 _ PGM01 SIMON MYOUTQ RDY 11 1 _ LOAD SIMON MYOUTQ SQL RDY 3 1 _ LOAD SIMON MYOUTQ SQL RDY 3 1 _ LOAD SIMON MYOUTQ SQL RDY 3 1 _ LOAD SIMON MYOUTQ SQL RDY 3 1 Bottom

Before the new releases I would have used the OUTPUT_QUEUE_ENTRIES_BASIC View to retrieve the same information:

01 SELECT SPOOLED_FILE_NAME AS "File", 02 USER_NAME AS "User", 03 OUTPUT_QUEUE_NAME AS "Queue", 04 USER_DATA AS "User data", 05 STATUS AS "Sts", 06 TOTAL_PAGES AS "Pages", 07 'N/A' AS "Cur page", 08 COPIES AS "Copy" 09 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 10 WHERE USER_NAME = USER

I used OUTPUT_QUEUE_ENTRIES_BASIC instead of OUTPUT_QUEUE_ENTRIES as the "basic" version returns the results faster than the "full" version.

Lines 1 – 8: I have given the columns similar names to the columns shown in WRKSPLF. The one column I cannot return is the one that shows the current page that is being printed, line 7.

Line 10: As the results are returned for all spool files in all output queues I only need the ones that are mine.

File User Queue User data Sts Pages Cur page Copy ------- ------- ------ ---------- ----- ----- -------- ---- PGM01 SIMON MYOUTQ <NULL> READY 11 N/A 1 PGM01 SIMON MYOUTQ <NULL> READY 11 N/A 1 DSPF01 SIMON MYOUTQ <NULL> READY 4 N/A 1 PGM01 SIMON MYOUTQ <NULL> READY 11 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1

Null is returned in the results to indicate that the spool file does not have user data.

This is relatively slow as all the results have to be generated, and then only ones where the user profile is the same as mine are returned.

Using the new SPOOLED_FILE_INFO I can generate the same results with:

01 SELECT SPOOLED_FILE_NAME AS "File", 02 JOB_USER AS "User", 03 OUTPUT_QUEUE AS "Queue", 04 USER_DATA AS "User data", 05 STATUS AS "Sts", 06 TOTAL_PAGES AS "Pages", 07 'N/A' AS "Cur page", 08 COPIES AS "Copy" 09 FROM TABLE(QSYS2.SPOOLED_FILE_INFO())

Line 9: As I have omitted all of the Table Function's parameters it uses the defaults and generates a set of results that are the same as those from OUTPUT_QUEUE_ENTRIES_BASIC.

File User Queue User data Sts Pages Cur page Copy ------- ------- ------ ---------- ----- ----- -------- ---- PGM01 SIMON MYOUTQ <NULL> READY 11 N/A 1 PGM01 SIMON MYOUTQ <NULL> READY 11 N/A 1 DSPF01 SIMON MYOUTQ <NULL> READY 4 N/A 1 PGM01 SIMON MYOUTQ <NULL> READY 11 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1 LOAD SIMON MYOUTQ SQL READY 3 N/A 1

The table function returned the results for only the default user, me. This should make it faster compared to OUTPUT_QUEUE_ENTRIES_BASIC.

I don't see a real reason to need the information that is so simply shown by WRKSPLF. But there is other information that I can get from SPOOLED_FILE_INFO that would make my life easier. For example:

01 SELECT SPOOLED_FILE_NAME,QUALIFIED_JOB_NAME,SPOOLED_FILE_NUMBER, 02 STATUS,CREATION_TIMESTAMP 03 FROM TABLE(QSYS2.SPOOLED_FILE_INFO())

Which returns:

SPOOLED SPOOLED _FILE _FILE_ _NAME QUALIFIED_JOB_NAME NUMBER STATUS CREATION_TIMESTAMP ------- ----------------------- ------- ------ ------------------ PGM01 775949/SIMON/QPADEV0002 11 READY 2022-10-24 23:31:02 PGM01 775949/SIMON/QPADEV0002 12 READY 2022-10-24 23:31:38 DSPF01 783096/SIMON/QPADEV0002 1 READY 2022-10-25 14:05:27 PGM01 783096/SIMON/QPADEV0002 2 READY 2022-10-25 14:05:40 LOAD 793142/SIMON/QPADEV0002 1 READY 2022-10-27 00:33:59 LOAD 793142/SIMON/QPADEV0002 2 READY 2022-10-27 00:35:23 LOAD 793142/SIMON/QPADEV0002 3 READY 2022-10-27 00:38:14 LOAD 798710/SIMON/QPADEV0002 1 READY 2022-10-27 23:14:32

With the above information I can do just about anything with these spool files.

If I was only interested in spool files that were older than a week ago I use the ENDING_TIMESTAMP parameter:

01 SELECT SPOOLED_FILE_NAME,QUALIFIED_JOB_NAME,SPOOLED_FILE_NUMBER, 02 STATUS,CREATION_TIMESTAMP 03 FROM TABLE(QSYS2.SPOOLED_FILE_INFO( 04 ENDING_TIMESTAMP => CURRENT_TIMESTAMP - 7 DAYS))

Line 4: By subtracting 7 days from the current timestamp the parameter will always contain the time and date of this time last week.

The results are:

SPOOLED SPOOLED _FILE _FILE_ _NAME QUALIFIED_JOB_NAME NUMBER STATUS CREATION_TIMESTAMP ------- ----------------------- ------- ------ ------------------ PGM01 775949/SIMON/QPADEV0002 11 READY 2022-10-24 23:31:02 PGM01 775949/SIMON/QPADEV0002 12 READY 2022-10-24 23:31:38 DSPF01 783096/SIMON/QPADEV0002 1 READY 2022-10-25 14:05:27 PGM01 783096/SIMON/QPADEV0002 2 READY 2022-10-25 14:05:40

If I wanted the results for all users I would just:

SELECT * FROM TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => '*ALL'))

As this would return similar results to the OUTPUT_QUEUE_ENTRIES_BASIC I am not sure which would be faster, or which I would prefer.

 

You can learn more about the SPOOLED_FILE_INFO SQL Table Function from the IBM website here.

 

This article was written for IBM i 7.5 and 7.4 TR6.