Pages

Wednesday, May 29, 2019

Using SQL to retrieve data from spooled files.

spooled_file_data sql table function to extract data from a spool file

One of the additions to IBM i with release 7.3 TR6 was a Db2 for i (SQL) table function that allows me to retrieve the contents of any spool file in my IBM i. I am sure that there are some people who are thinking "So what, I can do that with DSPSPLF"

Yes, I can view the contents of a spool file, but I cannot copy data from it. Why would I want to copy data from a spool file?

I am sure we all have reports in our ERP applications that the users would prefer as a spreadsheet. You are reluctant to change the ERP program as by doing so it will invalidate the support contract. Therefore, you copy the spool file to a physical file, and then parse the report's columns into fields, that are then written to the output file.

Using this new table function it becomes, in my opinion, a whole lot easier.

Let me start with a spool file we can all create in our IBM i: a list of spool files in an output queue.

If you want a list of spool files in an output queue I do not recommend the method described here. Use the OUTPUT_QUEUE_ENTRIES view instead.

The following command I used to generate my spool file:

WRKOUTQ OUTQ(QPRINT) OUTPUT(*PRINT)

When the command completes a spool file called QPRTSPLQ has been generated in my default output queue. Now I can use SPOOLED_FILE_DATA table function to view the contents of this spool file.

01  SELECT * 
02    FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
03               JOB_NAME => '512094/SIMON/QPADEV0001',
04               SPOOLED_FILE_NAME => 'QPRTSPLQ',
05               SPOOLED_FILE_NUMBER => 2))

Line 2: The SPOOLED_FILE_DATA is found in the SYSTOOLS library, and I always qualify view, table function, etc.

Lines 3 – 5: SPOOLED_FILE_DATA table function has three parameters:

Parameter Description
JOB_NAME The full job name of the job that generated the spool file
SPOOLED_FILE_NAME Spool file's name
SPOOLED_FILE_NUMBER Spool file's number. I have found that this optional, but if there is more than one spool file with the same name in the job then this table function will error unless you give this value

All of the information you need can be found using the Work Spool Files command, WRKSPLF.

SPOOLED_FILE_DATA returns two columns:

  1. ORDINAL_POSITION:  equivalent of the spool file line number
  2. SPOOLED_DATA:  the spool file line

When SPOOLED_FILE_DATA is executed it creates a file, QIBM_SFD, in the library QTEMP. This file contains the data copied from the spool file. If you have any of the parameters wrong you will receive a message about the CPYSPLF command having failed. If you receive this message check and correct the parameters you used.

The returned data looks just the same as is shown using the DSPSPLF command.

ORDINAL_POISTION    SPOOLED_DATA
                1   5770SS1 V7R3M0 160422         Work With Output Queue
                2   File       User       User Data  Status Pages Copies
                3   QSYSPRT    TOSCA                  RDY       1     1
                4   QPDSPJOB   QTCP       FFDC        RDY       7     1
                5   QPDSPJOB   QUSER      FFDC        RDY       7     1

Note:  I have not included the entire SPOOLED_DATA column as it will not fit on this page.

I am only interested in some of the columns, therefore, I will use the SQL substring function to extract those columns.

01  SELECT SUBSTR(SPOOLED_DATA,2,10) AS "Spool file",
02         SUBSTR(SPOOLED_DATA,13,10) AS "User",
03         SUBSTR(SPOOLED_DATA,42,5) AS "Pages",
04         SUBSTR(SPOOLED_DATA,50,3) AS "Copies",
05         SUBSTR(SPOOLED_DATA,73,6) AS "File No.",
06         SUBSTR(SPOOLED_DATA,84,10) AS "Job name",
07         SUBSTR(SPOOLED_DATA,95,6) AS "Job No.",
08         SUBSTR(SPOOLED_DATA,102,8) AS "Date",
09         SUBSTR(SPOOLED_DATA,111,8) AS "Time"
10  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
12  JOB_NAME => '512094/SIMON/QPADEV0001',
13  SPOOLED_FILE_NAME => 'QPRTSPLQ',
14  SPOOLED_FILE_NUMBER => 2))
15  WHERE SUBSTR(SPOOLED_DATA,36,3) IN ('RDY','HLD','SAV')
16  ORDER BY ORDINAL_POSITION

Lines 1 – 9: I am substring out the columns of data I want, and giving each of the new columns a name.

Lines 10 – 14: These are the same table function parameters I used before.

Line 15: I only want spool file rows that are in a ready, held, or saved status. This also eliminates the reports headers, etc. as they do not contain those values in that position.

The results look like:

Spool file  User  Pages  Copies  File No. Job name   Job No. Date     Time
QSYSPRT     TOSCA    1    1          1    QPADEV0002 263871  02/28/19 17:14:16
QPDSPJOB    QTCP     7    1          3    QTVTELNET  286281  03/11/19 20:12:13
QPDSPJOB    QUSER    4    1          3    QRWTSRVR   287609  03/12/19 11:53:38
QPDSPJOB    QTCP    15    1          3    QTVTELNET  296444  03/17/19 10:48:36
QPDSPJOB    QTCP     7    1          3    QTVTELNET  300675  03/18/19 23:40:35
QPDSPJOB    QTCP     3    1          3    QTVTELNET  334146  04/03/19 01:32:09
QPDSPJOB    QTCP     7    1          3    QTVTELNET  361967  04/16/19 20:41:04
QPDSPJOB    QUSER    1    1          3    QRWTSRVR   385048  04/21/19 12:19:46

But all the results are character. The numbers, date, and time are not those data types, they are character. I can change my SQL statement to convert those character values to their true values.

01  SELECT SUBSTR(SPOOLED_DATA,2,10) AS "Spool file",
02         SUBSTR(SPOOLED_DATA,13,10) AS "User",
03         TO_NUMBER(SUBSTR(SPOOLED_DATA,42,5)) AS "Pages",
04         TO_NUMBER(SUBSTR(SPOOLED_DATA,50,3)) AS "Copies",
05         TO_NUMBER(SUBSTR(SPOOLED_DATA,73,6)) AS "File No.",
06         SUBSTR(SPOOLED_DATA,95,6) || '/' || 
07           RTRIM(SUBSTR(SPOOLED_DATA,13,10)) || '/' || 
08           SUBSTR(SPOOLED_DATA,84,10) AS "Job name",
09         DATE(SUBSTR(SPOOLED_DATA,102,6) || 
10           '20' || SUBSTR(SPOOLED_DATA,108,2)) AS "Date",       
11         TIME(SUBSTR(SPOOLED_DATA,111,8)) AS "Time"
12  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
13  JOB_NAME => '512094/SIMON/QPADEV0001',
14  SPOOLED_FILE_NAME => 'QPRTSPLQ',
15  SPOOLED_FILE_NUMBER => 2))
16  WHERE SUBSTR(SPOOLED_DATA,36,3) IN ('RDY','HLD','SAV')
17  ORDER BY ORDINAL_POSITION

What did I change?

Lines 3 – 5: I have used the TO_NUMBER to convert the character values to numeric.

Lines 6 – 8: I decided to create the true job name by concatenating the job number, job user, and job name. I have used the RTRIM function to remove any trailing spaces from the job user.

Lines 9 and 10: Converting the character "MM/DD/YY" caused me trouble. This IBM i job uses the *USA date format, therefore, I need to add the century (20) to the date so it will be a valid date for this statement.

Line 11: No trouble converting the character time to a true time.

My results now look like:

Spool file  User   Pages Copies File No. Job name                Date       Time
QSYSPRT     TOSCA  1     1      1        263871/TOSCA/QPADEV0002 2019-02-28 17.14.16
QPDSPJOB    QTCP   7     1      3        286281/QTCP/QTVTELNET   2019-03-11 20.12.13
QPDSPJOB    QUSER  4     1      3        287609/QUSER/QRWTSRVR   2019-03-12 11.53.38
QPDSPJOB    QTCP   15    1      3        296444/QTCP/QTVTELNET   2019-03-17 10.48.36
QPDSPJOB    QTCP   7     1      3        300675/QTCP/QTVTELNET   2019-03-18 23.40.35
QPDSPJOB    QTCP   3     1      3        334146/QTCP/QTVTELNET   2019-04-03 01.32.09
QPDSPJOB    QTCP   7     1      3        361967/QTCP/QTVTELNET   2019-04-16 20.41.04
QPDSPJOB    QUSER  1     1      3        385048/QUSER/QRWTSRVR   2019-04-21 12.19.46

The columns I converted to numbers are now all aligned to the left. And the date and time look like date and time values.

All of this playing around with SPOOLED_FILE_DATA is fine and dandy, but it is not really useful until I output it into something like a file or table from which I can share it. I created the following in RPG program to do just that.

01  **free
02  exec sql SET OPTION COMMIT = *NONE,DATFMT = *MDY ;

03  exec sql DROP TABLE QTEMP.TABLE1 ;

04  exec sql CREATE OR REPLACE TABLE QTEMP.TABLE1
05           (SPOOL_FILE VARCHAR(10),
06            USER VARCHAR(10),
07            PAGES INT,
08            COPIES INT,
09            FILE_NUMBER INT,
10            JOB_NAME VARCHAR(28),
11            CREATE_DATE DATE,
12            CREATE_TIME TIME) ;

13  exec sql INSERT INTO QTEMP.TABLE1
14          SELECT SUBSTR(SPOOLED_DATA,2,10),
15                 SUBSTR(SPOOLED_DATA,13,10),
16                 TO_NUMBER(SUBSTR(SPOOLED_DATA,42,5)),
17                 TO_NUMBER(SUBSTR(SPOOLED_DATA,50,3)),
18                 TO_NUMBER(SUBSTR(SPOOLED_DATA,73,6)),
19                 SUBSTR(SPOOLED_DATA,95,6) || '/' ||
20                   RTRIM(SUBSTR(SPOOLED_DATA,13,10)) || '/' ||
21                   SUBSTR(SPOOLED_DATA,84,10),
22                 DATE(SUBSTR(SPOOLED_DATA,102,8)),
23                 TIME(SUBSTR(SPOOLED_DATA,111,8))
24          FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
25          JOB_NAME => '512094/SIMON/QPADEV0001',
26          SPOOLED_FILE_NAME => 'QPRTSPLQ',
27          SPOOLED_FILE_NUMBER => 2))
28          WHERE SUBSTR(SPOOLED_DATA,36,3) IN ('RDY','HLD','SAV') 
29          ORDER BY ORDINAL_POSITION ;

30  *inlr = *on ;

Line 1: I just find it so easy to write everything in totally free RPG.

Line 2: The SQL options are here as I do not want to commit the rows I insert into any table to a journal. And I can set my program's date format to *MDY.

Line 3: If there is a TABLE1 already in QTEMP, this statement will delete it.

Lines 4 – 12: Here I create a new table, TABLE1 to contain the data I will be extracting from SPOOLED_FILE_DATA. I have defined all the character columns as VARCHAR, variable length character. The number columns are defined as INT, integer. And the date and time columns are defined as their data types.

Lines 13 – 29: This statement is the same as my previous example, except for line 22. As I have made the default date format *MDY, by using the option statement on line 2, I can just move the data I extract from SPOOLED_DATA straight into a date format column.

The results are as I expect:

SPOOL_FILE USER  PAGES COPIES FILE_NUMBER  JOB_NAME                CREATE_DATE CREATE_TIME
QSYSPRT    TOSCA     1      1           1  263871/TOSCA/QPADEV0002 2019-02-28   17.14.16
QPDSPJOB   QTCP      7      1           3  286281/QTCP/QTVTELNET   2019-03-11   20.12.13
QPDSPJOB   QUSER     4      1           3  287609/QUSER/QRWTSRVR   2019-03-12   11.53.38
QPDSPJOB   QTCP     15      1           3  296444/QTCP/QTVTELNET   2019-03-17   10.48.36
QPDSPJOB   QTCP      7      1           3  300675/QTCP/QTVTELNET   2019-03-18   23.40.35
QPDSPJOB   QTCP      3      1           3  334146/QTCP/QTVTELNET   2019-04-03   01.32.09
QPDSPJOB   QTCP      7      1           3  361967/QTCP/QTVTELNET   2019-04-16   20.41.04
QPDSPJOB   QUSER     1      1           3  385048/QUSER/QRWTSRVR   2019-04-21   12.19.46

If I ever need to extract the same information from a QPRTSPLQ spool file I can use this program time and again to get a table with the data I desire in it.

 

This shows that by using SPOOLED_DATA table function I can easily extract data from spool files and insert data into a table, or file, that I could use elsewhere. SPOOLED_DATA is going to save me time with several programs I know I am going to need to write in the future.

 

You can learn more about the SPOOLED_FILE_DATA table function command from the IBM website here.

 

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

28 comments:

  1. I was just looking at this capability yesterday, and your post makes it a lot easier then the example from ACS. Thanks for sharing.

    ReplyDelete
    Replies
    1. It is because Simon is a user like you and me :)

      Delete
  2. That is too cool.

    ReplyDelete
  3. Love it! Can't wait for access to that

    ReplyDelete
  4. Carlos Pravia SequeiraFebruary 21, 2020 at 10:16 AM

    Elegant

    ReplyDelete
  5. answer to a frequent challenge

    ReplyDelete
  6. Thanks for sharing :)

    ReplyDelete
  7. surely useful one, thanks Simon.

    ReplyDelete
  8. Thanks for sharing. Useful option.

    ReplyDelete
  9. Rosario RuggieroMay 1, 2021 at 1:28 PM

    Molto utile, grazie

    ReplyDelete
  10. Very useful tip!

    ReplyDelete
  11. Thanks for sharing

    ReplyDelete
  12. Once again, a great, useful article. Just used this today to pull a field from a report to put into the user data for result file naming. Thanks!

    ReplyDelete
  13. Thanks for the information. But the length of the column SPOOLED_DATA in service SPOOLED_FILE_DATA is too short. Only 200 characters. The maximum width of a PRTF is 378. And we have such spool files.

    ReplyDelete
    Replies
    1. That is a serious shortcoming.

      Have you submitted an RFE for it to be changed to match the meximum width of a spool file.

      Delete
    2. The length of 200 was on a V7R3 machine. Now I checked it on a V7R4 machine with current CUMPTF. There the file QTEMP/QIBM_SFD and also the column SPOOLED_DATA have now a length of 378. But they didn't change their documentation: https://www.ibm.com/docs/en/i/7.4?topic=services-spooled-file-data-table-function

      Delete
  14. Is there a SYSTOOL command to change the spool file status using SQL? I see there are other functions for deleting spool files and retrieving the status and other attributes using the OUTPUT_QUEUE_ENTRIES_BASIC.

    ReplyDelete
  15. Hi Thanks good work. But why do you have those "job_number =>" or "Spool_file_name =>" ? What are these for ? Thanks.

    this syntax doesn work. You wrote it 2 times there, in the code additional the values of your job.
    What do i Wrong ?

    ReplyDelete
    Replies
    1. Db2 table functions allow you to either enter the parameters with the parameter names, for example JOB_NUMBER, or you have to enter them without the parameter names in the sequence the parameters are in. You cannot use just "=" as that means equals, I have no idea why Db2 chose "=>" to mean the value for this parameter is.

      If you having issues first check that the table function is present in your partition. If you do not have the right release, TR, or PTF loaded it will not be present.

      Delete
  16. Hi Simon, does this systools function works with V7R2? I tried with this version and is not working, please let me know

    ReplyDelete
    Replies
    1. Alas, 7.2 is too old of a release. SPOOLED_FILE_DATA was introduced in 7.3 TR6 and 7.4 .

      Delete
  17. what about CPYSPLF? how is this different? or did I not read carefully enough?

    ReplyDelete
    Replies
    1. You could use CPYSPLF if you want to make a more time consuming process.
      If I build a SQL View then you can use it every time.
      With CPLYSLF I'll need a program to do the mapping.
      Embrace SQL and find how much simpler it makes your life.

      Delete

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.