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 DESC

Explanation: 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

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

Senior IBM i Analyst/Developer and technical writer. Former collaborator of the Italian edition of "System i News" magazine and author of several publications about tools and development practices for the IBM i platform.

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *