Reading a spooled file with SQL

It used to take CPYSPLF into a physical file and then a query on that. From release 7.3 the contents can be read directly.

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 available from 7.3; on earlier releases it came with a Db2 group PTF that may not have been applied. 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.

Sull'esempio: le due query non sono state eseguite su uno spool vero, perché la prova è stata fatta con WHERE 1 = 0 e un nome di job inventato. Le posizioni 95 e 14 sono inventate anche loro, e il testo lo dice esplicitamente: vanno lette sul report reale. Quello che è verificato è la funzione, la forma della chiamata e i nomi delle due colonne.