Reading a spooled file with SQL

Without this function it takes CPYSPLF into a physical file and then a query on that. With SYSTOOLS.SPOOLED_FILE_DATA the contents can be read directly: IBM provides it as an example built on CPYSPLF itself, and if the spooled file name is omitted it reads QPJOBLOG.

SELECT ORDINAL_POSITION, SPOOLED_DATA
  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
       JOB_NAME          => '123456/USER/CLOSE',
       SPOOLED_FILE_NAME => 'QPJOBLOG'))
 ORDER BY ORDINAL_POSITION

To search for a line inside a job log:

SELECT SPOOLED_DATA
  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
       JOB_NAME          => '123456/USER/CLOSE',
       SPOOLED_FILE_NAME => 'QPJOBLOG'))
 WHERE SPOOLED_DATA LIKE '%CPF%'

Example: re-checking a report without re-running it

The month-end report is already printed, and somebody asks whether a total is right. Re-running the processing to answer costs time and, if the data has moved meanwhile, gives a different number, which does not answer the question.

The lines that matter are pulled from the spool as it stands:

SELECT ORDINAL_POSITION, SPOOLED_DATA
  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
       JOB_NAME          => '123456/USER/CLOSE',
       SPOOLED_FILE_NAME => 'QSYSPRT'))
 WHERE SPOOLED_DATA LIKE '%TOTAL%'
 ORDER BY ORDINAL_POSITION

Reports are fixed-width, so from there the number is isolated by position and summed:

SELECT SUM(DEC(REPLACE(SUBSTR(SPOOLED_DATA, 95, 14), ',', ''), 15, 2)) AS TOTAL
  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
       JOB_NAME          => '123456/USER/CLOSE',
       SPOOLED_FILE_NAME => 'QSYSPRT'))
 WHERE SPOOLED_DATA LIKE '%TOTAL%'

The positions (95 and 14 here) have to be read off the real report, and change with every different layout. The point is not this query: it is that a printed document becomes a queryable source, and the check happens against what the customer is holding rather than against a re-run.

Releases. SYSTOOLS.SPOOLED_FILE_DATA is in IBM's 7.5 documentation. To find out whether it is there, just run the query: if it is missing the system answers SQL0204 (object not found).

On systems without it, the long way remains: CPYSPLF into a physical file and query that.

Sources

• IBM: SPOOLED_FILE_DATA table function
• IBM: DECIMAL or DEC