Extracting the same data from multiple spool files with SQL

The idea for this post came from a question I was asked. There were a number of program dump spool files, QPGMDMP, and the manager wanted to know what was the value in a same variable from all of the spool files. While others said there was no alternative than to browse each spool file for the information. I knew there was a better way using SQL.

In these examples I am not using program dump spool files as you would be unable to replicate what I am going to show. Therefore, I am using the output from the Work Output Queue command, WRKOUTQ, with it outputting to a spool file:

01 WRKOUTQ OUTPUT(*PRINT)

And I want to retrieve the time the command was run, which is found in the first record of the spool file.

I used the WRKOUTQ command three times, using two different jobs and user profiles, to create three QPRTSPLQ spool files. I can show these using the OUTPUT_QUEUE_ENTRIES_BASIC View. I used the following statement to list the spool files generated by WKROUTQ:

01 SELECT SPOOLED_FILE_NAME AS "Splf", 02 JOB_NAME AS "Job name", 03 FILE_NUMBER AS "File No." 04 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 05 WHERE SPOOLED_FILE_NAME = 'QPRTSPLQ'

Lines 1 – 3: I am only interested in the spool file name, job name, and file number for these spool files.

Lines 4: I am using OUTPUT_QUEUE_ENTRIES_BASIC as it faster to get the results I desire from, rather than from OUTPUT_QUEUE_ENTRIES which has more columns, therefore, takes longer to get the results.

Line 5: I only want the results for the WRKOUTQ spool files.

I can see that three rows of results are returned:

Splf Job name File No. --------- ---------------------- -------- QPRTSPLQ 166055/SIMON/SIMON_1 1 QPRTSPLQ 166055/SIMON/SIMON_1 2 QPRTSPLQ 166056/SOMEONE/DSP01 1

Now I need to look inside these spool files and retrieve the time they were generated. I will be using the SPOOLED_FILE_DATA Table function. If I want to look at the contents of the first spool file, I would use the following:

01 SELECT SPOOLED_DATA 02 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 03 JOB_NAME => '166055/SIMON/SIMON_1', 04 SPOOLED_FILE_NAME => 'QPRTSPLQ', 05 SPOOLED_FILE_NUMBER => 1))

Line 1: SPOOLED_DATA is the record from the spool file.

Lines 3 – 5: SPOOLED_FILE_DATA requires the following: job name, spool file name, and spool file number.

I am only going to show the first few lines of the results:

SPOOLED_DATA ----------------------------------------------------------------------------------- 5770SS1 V7R5M0 220415 Work With All Output Queues 7/17/25 7:11:28 CDT Queue Library Files Writer Status QDKT QGPL 0 RLS QJOBLOG QGPL 782 RLS

The time is always going to be in the first record in the same place. In these examples I am going to scan for 'CDT' just in case this code could be in various places on the first record. 'CDT' stands for US Central time zone with daylight saving times. If you are in a different time zone, check the spool file to find what code is used instead.

01 SELECT SPOOLED_DATA 02 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 03 JOB_NAME => '166055/SIMON/SIMON_1', 04 SPOOLED_FILE_NAME => 'QPRTSPLQ', 05 SPOOLED_FILE_NUMBER => 1)) 06 WHERE SPOOLED_DATA LIKE '%CDT%'

Line 6: I have added this line to scan for 'CDT', using the LIKE.

One result is returned:

SPOOLED_DATA ----------------------------------------------------------------------------------- 5770SS1 V7R5M0 220415 Work With All Output Queues 7/17/25 7:11:28 CDT

If I know 'CDT' is always going to be in the same place I could use this instead:

01 SELECT SPOOLED_DATA 02 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 03 JOB_NAME => '166055/SIMON/SIMON_1', 04 SPOOLED_FILE_NAME => 'QPRTSPLQ', 05 SPOOLED_FILE_NUMBER => 1)) 06 WHERE SUBSTR(SPOOLED_DATA,92,3) = 'CDT'

Line 6: I am using a substring, in place of the like, as 'CDT' always starts in the 92nd position, and it always three long.

The result from this statement is the same as the previous one.

Now I can combine the results from OUTPUT_QUEUE_ENTRIES_BASIC with SPOOLED_FILE_DATA for my final statement:

01 SELECT A.SPOOLED_FILE_NAME "Splf", 02 A.FILE_NUMBER AS "File No.", 03 SUBSTR(B.SPOOLED_DATA,83,8) AS "Time" 04 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC A, 05 LATERAL 06 (SELECT SPOOLED_DATA 07 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 08 JOB_NAME => A.JOB_NAME, 09 SPOOLED_FILE_NAME => A.SPOOLED_FILE_NAME, 10 SPOOLED_FILE_NUMBER => A.FILE_NUMBER)) 11 WHERE SUBSTR(SPOOLED_DATA,92,3) = 'CDT') B 12 WHERE A.SPOOLED_FILE_NAME = 'QPRTSPLQ' 13 AND A.CREATE_TIMESTAMP > CURRENT_TIMESTAMP - 12 HOURS

I have added spaces to make it easier to understand the parts of the statement, they are not needed.

Lines 1 – 3: I want three columns of results:

1. Spool file name
2. Spool file number
3. Time extracted from the spool files. I substring the time from the spool files' record

Line 4: The first two columns come from OUTPUT_QUEUE_ENTRIES_BASIC. I have prefixed the columns from that View with the letter 'A'.

Line 5: To join a View to a Table function I need to use a LATERAL. This means I can use the values from the View as the parameters for the Table function.

Line 6: The only column I want from the Table function is the spool file record, SPOOLED_DATA.

Line 8 – 10: I am using the job name, spooled file name, and spool file number from the View as the parameters for SPOOLED_FILE_DATA.

Line 11: I only want the Table function to return rows that have 'CDT' in the 92nd position.

Line 12: This WHERE applies to the final combined results to select only rows where the spool file name is QPRTSPLQ.

Line 13: I thought it would be good to show that I can limit the number of spool files to those generated in the past twelve hours.

The results are:

Splf File No. Time -------- -------- -------- QPRTSPLQ 1 7:11:28 QPRTSPLQ 2 7:13:43 QPRTSPLQ 1 7:18:15

By using the same technique I have described you can see how simple it was to provide information I was asked for.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.