Report of the success of the nightly saves

My manager asked me if I could provide him with a report of all the libraries that were backed up as part of the night process job, NBACKUP. "You know what I mean: one line for each library" was his final statement to me.

I did want to browse a different job log for each day. I wanted to design something that was simple. I decided to use the HISTORY_LOG_INFO table function. I could select results using:

• Starting timestamp of 1 day (24 hours) ago
• I know the job name

I started with this SQL statement:

01 SELECT MESSAGE_TEXT,MESSAGE_TIMESTAMP,FROM_JOB, 02 FROM TABLE(QSYS2.HISTORY_LOG_INFO(CURRENT_TIMESTAMP - 1 DAY)) 03 WHERE FROM_JOB_NAME = 'NBACKUP' ;

Line 1: I am only interested in these three columns:

1. MESSAGE_TEXT:  The message text will display the information I want
2. MESSAGE_TIMESTAMP:  When the save command started
3. FROM_JOB:  I know that the job name will be the same for all the results, but I want it there in case anyone needs to go look for the job log

Line 2: As I only have one timestamp in the parameters for HISTORY_LOG_INFO this is taken as the starting timestamp. The current timestamp is taken as the end of the range.

Line 3: I only want results for the job NBACKUP.

My results included more than the rows from the saves. Therefore, I need to find something that is unique to the rows I desire. This happens to be the word "saved". If the word is in the MESSAGE_TEXT then I want to select the row for my results.

I changed the SQL statement to be:

01 SELECT MESSAGE_TEXT,MESSAGE_TIMESTAMP,FROM_JOB, 02 FROM TABLE(QSYS2.HISTORY_LOG_INFO(CURRENT_TIMESTAMP - 1 DAY)) 03 WHERE FROM_JOB_NAME = 'NBACKUP' 04 AND MESSAGE_TEXT LIKE '%saved%' 05 ORDER BY MESSAGE_TIMESTAMP ;

Line 4: If MESSAGE_TEXT contains the string "saved" then select the row.

Line 5: Sort the results by the message timestamp.

An example of possible results are:

MESSAGE_TEXT MESSAGE_TIMESTAMP --------------------------------------------------------- -------------------------- 302 objects saved from library LIB1. 2025-04-01 HH:MM:07.863705 3 objects saved from library LIB2. 2025-04-01 HH:MM:20.437882 No objects saved from library LIB3. All objects excluded. 2025-04-01 HH:MM:21.512279 FROM_JOB ---------------------- 999999/NIGHTLY/NBACKUP 999999/NIGHTLY/NBACKUP 999999/NIGHTLY/NBACKUP

The first line, for library LIB1, was created by the Save library command, SAVLIB.

The second, for LIB2, is for a save that used the Save object command, SAVOBJ, to save just three objects in the library.

The third, for LIB3, used the Save change object command, SAVCHGOBJ, where no objects had changed.

I can create a program to send this information to my manager. I chose to do it with a RPG program:

01 **free 02 dcl-s Command varchar(200) ; 03 dcl-s ReturnCode int(5) ; 04 exec sql DROP TABLE IF EXISTS MYLIB.OUTFILE ; 05 exec sql CREATE TABLE MYLIB.OUTFILE AS 06 (SELECT MESSAGE_TEXT,MESSAGE_TIMESTAMP,FROM_JOB 07 FROM TABLE(QSYS2.HISTORY_LOG_INFO(CURRENT_TIMESTAMP - 1 DAY)) 08 WHERE FROM_JOB_NAME = 'NBACKUP' 09 AND MESSAGE_TEXT LIKE '%saved%' 10 ORDER BY MESSAGE_TIMESTAMP) 11 WITH DATA ; 12 exec sql SET :ReturnCode = SYSTOOLS.IFS_UNLINK('/home/MyFolder/saved_libraries.xlsx') ; 13 Command = 'OVRDBF FILE(STDOUT) TOFILE(QTEMP/STDOUT) OVRSCOPE(*JOB)' ; 14 exec sql CALL QSYS2.QCMDEXC(:Command) ; 15 exec sql SET :ReturnCode = 16 SYSTOOLS.GENERATE_SPREADSHEET( 17 PATH_NAME => '/home/MyFolder/saved_libraries', 18 LIBRARY_NAME => 'MYLIB', 19 FILE_NAME => 'TESTFILE', 20 SPREADSHEET_TYPE => 'xlsx', 21 COLUMN_HEADINGS => 'LABEL') ; 22 exec sql CALL QSYS2.QCMDEXC('DLTOVR FILE(STDOUT) LVL(*JOB)') ; 23 exec sql SET :ReturnCode = SYSTOOLS.SEND_EMAIL( 24 TO_EMAIL => 'manager@email.com', 25 SUBJECT => 'Library saves', 26 BODY => 'See attached file', 27 ATTACHMENT => '/home/MyFolder/saved_libraries.xlsx') ; 28 *inlr = *on ;

Lines 2 and 3: Definition for the variables used in this program.

Line 4: Delete the output file if it exists.

Lines 5 – 11: Create an outfile table from the SQL statement above. I need the output file in a library that is not QTEMP.

Line 12: Use the IFS_UNLINK SQL statement to delete the file in the IFS.

Lines 13 and 14: I need to redirect the STDOUT to a file, therefore, I use the OVRDBF command, within the QCMDEXC SQL procedure, to do so.

Lines 15 – 21: I am generating a Microsoft Excel compatible spreadsheet by using the GENERATE_SPREADSHEET scalar function.

Line 22: I can now delete the override of the STDOUT.

Lines 23 – 27: I use the SEND_EMAIL SQL scalar function to send the email to my manager, with the spreadsheet attached.

As you can see this is an easy to provide my manager with the information he wants.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.