Thursday, September 29, 2022

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.

No comments:

Post a Comment

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.