Using SQL to retrieve data from spooled files.

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.