Query the System Log on IBM i 7.1 using CL and SQL

UPDATE: Christian Jorgensen sent me an easier method to accomplish this after I posted this article: https://bitbucket.org/christianjorgensen/sql-udtf-history_log_info/src/master/ I have not tried it yet but it looks to be everything that’s needed and easier than the below code. Feel free to read on here because there are some other useful items in the post as well.

I ran into an issue that I couldn’t directly solve with SQL running IBM i on 7.1; in later releases 7.2+ there are DB2 Services tables for this. Since I like using SQL as much as possible I wanted to find a way to accomplish getting data from the system log, DSPLOG. You might be asking yourself, “Why doesn’t he just upgrade to 7.2 or later?” Trust me, if it was my system I would. We still have customers that run application packages that cannot upgrade beyond 7.1, unfortunately. Here is the solution I came up with the help of 2 different sites:

This helped me build the CL I needed: https://code400.com/forum/forum/iseries-programming-languages/sql/5658-reading-spool-files-through-sql and this helped me convert the data into something usable: https://www.rpgpgm.com/2019/05/using-sql-to-retrieve-data-from-spooled.html

Explanation of what I am trying to accomplish

I want to query the system log using DSPLOG for certain MSGIDs for the current day, write the results to a file, query that file, and convert the data to be more readable in my SQL table that it will be stored in.

My DSPLOG command looks like this DSPLOG OUTPUT(*PRINT) MSGID(CPF3837 CPC3721 CPC370C CPI5922 CPF5101) the default for the command when you don’t specify the date is to use *CURRENT for the day. Since I am printing the results I want to use the CPYSPLF command to copy the last spool file to a PF that I specify. My command looks like this CPYSPLF FILE(QPDSPLOG) TOFILE(CMALIB/MYSPOOL) SPLNBR(*LAST). *LAST grabs the last spool file generated, in my case this worked but I am not sure if you have an incredibly active system if this will cause issues (I will continue to test and post any updates if I find out different).

My Process

Create the CL

PGM                                                      
                                                         
DSPLOG     OUTPUT(*PRINT) MSGID(CPF3837 CPC3721 CPC370C CPI5922 CPF5101)                            
                                                         
CPYSPLF    FILE(QPDSPLOG) TOFILE(TEMP/MYSPOOL) SPLNBR(*LAST)                               
                                                         
ENDPGM

I wanted to grab a few items returned in the results: MSGID, the text associated with the message, and the date and time the message was created. Here is what the MYSPOOL file looks like. Based on reviewing this, I will need records 3 and 4 to get the data I need. Additionally, I need to take the date and time fields and create a timestamp.

I generated the SQL needed to grab the specific data I listed above (read Simon’s article in the link at the beginning of this article for more explanation on this)

DECLARE var_bkmsgid CHAR(10);
DECLARE var_bkmsgtimestamp timestamp;
DECLARE var_bkmsgtxt CHAR(50);

select substr(myspool,0,8), substr(myspool,27,40) 
    INTO var_bkmsgid, var_bkmsgtxt
    FROM temp.myspool
    WHERE myspool like '%CPF3837%' or myspool like '%CPC370C%';
    
select timestamp(DATE(SUBSTR(myspool,70,6) || '20' || SUBSTR(myspool,70,2)),time(SUBSTR(myspool,79,8)))               
    INTO var_bkmsgtimestamp
    FROM temp.myspool 
    WHERE myspool like '%DSP%';

The results of the first statement:

The results of the second statement:

NOTE: If your results show in Hexadecimal in Run SQL Scripts you can fix that by going to the Connection tab, Edit JDBC Configuration…, clicking the Translation tab, checking Translate CCSID 65535 and then Save to save the settings.