Last Updated on 13 October 2019 by Roberto De Pedrini
For reading the contents of a spool file, the CPYSPLF command has been available for decades; it allows you to download the text into a physical file that can then be read with a program on order to pull out the needed information.
But, as often happens, SQL has a better solution… Some years ago, IBM started distributing the SYSTOOLS library, a small “hidden treasure” that contains a lot of utility functions (especially for of HTTP communications, to publish and consume Web services). At this link, the IBM documentation. https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqsystools.htm
Among the many SYSTOOLS functions, we find SPOOLED_FILE_DATA, a UDTF (User Defined Table Function) that allows to read the contents of a spool file directly with SQL, without need of other commands or intermediate files. It has three parameters: job identifier, spool file name (if omitted, QPJOBLOG is assumed) and spool file number (optional, if there are several files with the same name for the job, only the last one is processed).
Let’s see a handy example: from a spool generated by the DSPLIB command we extrapolate the name, type and size of the objects listed and arrange them in descending order by size.
This is the report (excerpt):
5770SS1 V7R3M0 160422 Visualizzazione della libreria 10/10/19 22:14:57 Pag. 1
Libreria . . . . . . . . . . . . . . . : MDUCA1
Tipo . . . . . . . . . . . . . . . . . : PROD
Numero di oggetti . . . . . . . . . . . : 204
Numero ASP libreria . . . . . . . . . . : 1
Unità ASP libreria . . . . . . . . . . : *SYSBAS
Gruppo ASP libreria . . . . . . . . . . : *SYSBAS
Creazione autor. . . . . . . . . . . . : *EXCLUDE
Testo descrittivo . . . . . . . . . . . : MDUCA (*CURLIB)
Oggetto Tipo Attributo Dim. Descrizione
CMDEXIT *PGM CLLE 114688 Exit program
FP001 *PGM RPGLE 172032 File Procedure Exit
FP002 *PGM RPGLE 188416 File Procedure Exit
ORDPRX01 *PGM RPGLE 200704 Exit program
ORDPRZ01 *PGM RPGLE 135168
ORDPRZ01B *PGM RPGLE 135168
SAVXSQL *PGM CLLE 98304
TESTFRA01 *PGM CLLE 90112 test
TESTR01 *PGM RPGLE 159744
TESTR02 *PGM RPGLE 143360
. . . .
XMLTEST *FILE PF 2142208
XSQL *CMD 8192 XSQL - Comando
XSQL$ *CMD 8192 XSQL Command
AUTH *DTAARA 8192 Authorized users
CHARS *DTAARA 8192
ECKEY *DTAARA 8192
FONDICHR *DTAARA 8192
BNDDIR001 *BNDDIR 8192 BndDir per Service program
TEST *BNDDIR 8192
Dimens. totale : 119808000
* * * * F I N E D E L L A L I S T A * * * * This is the SQL statement to read the spool and pull out the information we want:
WITH a AS (
SELECT *
FROM TABLE (systools.spooled_file_data (
'188635/MDUCA/QPAD221221' , 'QPDSPLIB'
)) s
WHERE MOD(ordinal_position, 58) > 10
)
, b AS (
SELECT SUBSTR(spooled_data, 4, 10) obj,
SUBSTR(spooled_data, 16, 10) type,
SUBSTR(spooled_data, 42, 10) dim
FROM a
WHERE ordinal_position < (
SELECT MAX(ordinal_position) - 2 FROM a)
)
SELECT *
FROM b
WHERE type <> '*JRNRCV'
ORDER BY dim DESCExplanation: with the first CTE, which generates the table “a”, we obtain the data of the spool file discarding the first 10 lines of each page, which contain the page and column headings, and the lines from 58 to the end of the page. Lines 11 to 57 are then extracted for each page.
With the second CTE “b”, the three desired information are retrieved, in this report they appear at fixed positions. The last two lines of the report are also discarded, since they do not hold significant data.
Finally, with the last SELECT we get the desired list. Only one object type is excluded here, but of course it is possible to apply all kinds of filters and sorting.
Let’s see another example of how we can get, from the joblog of a batch job, all the lines in which a system command is executed and the related timestamp. This makes possible to check the duration of the executed commands and programs, and investigate on any performance issues.
WITH a AS (
SELECT *
FROM TABLE (systools.spooled_file_data (
'633734/MDUCA/BATCH01' , 'QPJOBLOG'
)) s
WHERE MOD(ordinal_position, 57) > 4
)
, b AS (
SELECT ordinal_position,
SUBSTR(spooled_data, 1, 7) msg,
SUBSTR(spooled_data, 12, 15) type,
SUBSTR(spooled_data, 51, 15) time
FROM a
WHERE ordinal_position < (
SELECT MAX(ordinal_position) - 2 FROM a)
AND SUBSTR(spooled_data, 12, 15) = 'Command '
)
SELECT b.*,
(SELECT SUBSTR(spooled_data, 58, 80) info
FROM a x WHERE SUBSTR(x.spooled_data, 38, 15) = 'Message . . . :'
AND x.ordinal_position > b.ordinal_position
LIMIT 1)
FROM b
;Here the results:
Documentation:
SPOOLED_FILE_DATA function: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfspooldata.htm
General documentation about SYSTOOLS: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzajq/rzajqsystools.htm
CPYSPLF CL command: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cl/cpysplf.htm
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
I was trying to scan through all job logs in the system for a particular command and this gave me the idea about how to achieve that.
Thanks a lot.