New columns added to HISTORY_LOG_INFO

As part of the latest round of Technology Refreshes, IBM i 7.5 TR1 and 7.4 TR7, three new columns were added to one of my favorites Db2 for i Table Functions, HISTORY_LOG_INFO. I often use this Table Function to search the IBM i partitions' history.

The new columns are all parts of the existing job name column, FROM_JOB:

• FROM_JOB_NAME:  Job name
• FROM_JOB_USER:  The user profile of the job
• FROM_JOB_NUMBER:  Job number

These columns might not sound like a big addition to HISTORY_LOG_INFO, but the first two, name and user, make it easier to find results I am looking for.

I am sure I am not the only person who wondered when the current user of the job, column FROM_USER, would not be the same as the user from the job name. I quickly put this statement together to show any jobs where to two are not the same:

01 SELECT MESSAGE_ID AS "Id", 02 MESSAGE_TYPE AS "Type", 03 SEVERITY AS "Sev", 04 MESSAGE_TIMESTAMP "Msg time", 05 FROM_USER AS "From user", 06 FROM_JOB AS "From job", 07 FROM_JOB_NAME, 08 FROM_JOB_USER 09 FROM TABLE(QSYS2.HISTORY_LOG_INFO()) 10 WHERE FROM_USER <> FROM_JOB_USER 11 ORDER BY ORDINAL_POSITION DESC 12 LIMIT 5

Lines 1 – 6: All the columns I have given new column headings to were in the original HISTORY_LOG_INFO Table Function.

Lines 7 and 8: These are two of the new columns.

Line 10: I only want where the current user is not equal to the job name's user.

Line 11: The ORDINAL_POSITION is a sequential number generated just for the Table Function's results. I am using it here to sort the results so that the most recent result comes first.

Line 12: As this is just an example, I only want five rows returned in my results.

The results look like:

Id Type Sev Msg time ------- ------------- --- -------------------------- CPIAD09 INFORMATIONAL 0 2023-04-04 07:09:09.738507 CPIAD09 INFORMATIONAL 0 2023-04-04 07:09:09.359145 CPF1164 COMPLETION 0 2023-04-04 07:09:09.238165 CPIAD09 INFORMATIONAL 0 2023-04-04 07:09:09.158348 CPF1164 COMPLETION 0 2023-04-04 07:08:59.229378 From FROM FROM_ user From job JOB_NAME JOB_USER ----- ----------------------- ---------- -------- SIMON 555876/QUSER/QZRCSRVS QZRCSRVS QUSER SIMON 554460/QUSER/QZDASOINIT QZDASOINIT QUSER SIMON 555877/QUSER/QZRCSRVS QZRCSRVS QUSER SIMON 555877/QUSER/QZRCSRVS QZRCSRVS QUSER SIMON 555677/QUSER/QZRCSRVS QZRCSRVS QUSER

All of these results are me using the "Run SQL Scripts" tool, which starts the job using QUSER and then uses my user profile, SIMON.

Before these TRs if I wanted to search for a job that was started by a user profile I would do something like:

01 SELECT MESSAGE_ID,MESSAGE_TYPE,SEVERITY,MESSAGE_TIMESTAMP, 02 FROM_USER,FROM_JOB, 03 FROM TABLE(QSYS2.HISTORY_LOG_INFO()) 04 WHERE FROM_JOB LIKE '%QUSER%' 05 ORDER BY ORDINAL_POSITION DESC

Line 4: I would need to use the WHERE LIKE for a wildcard search to find all jobs submitted by the profile QUSER. I am sure you can appreciate that a wildcard search for a string is not efficient.

With the new columns I can change this statement to be:

01 SELECT MESSAGE_ID,MESSAGE_TYPE,SEVERITY,MESSAGE_TIMESTAMP, 02 FROM_USER,FROM_JOB, 03 FROM_JOB_NAME,FROM_JOB_USER 04 FROM TABLE(QSYS2.HISTORY_LOG_INFO()) 05 WHERE FROM_JOB_USER LIKE 'QUSER' 06 ORDER BY ORDINAL_POSITION DESC

Line 5: Using the new FROM_JOB_USER I can use a straight comparison, which will be faster than the wildcard comparisons.

The same applies to search for all entries in the history log for a particular job. Before these additions I would need to use a wildcard search again:

01 SELECT MESSAGE_ID,MESSAGE_TYPE,SEVERITY,MESSAGE_TIMESTAMP, 02 FROM_USER,FROM_JOB, 03 FROM TABLE(QSYS2.HISTORY_LOG_INFO()) 04 WHERE FROM_JOB_NAME LIKE '%QZDASOINIT' 05 ORDER BY ORDINAL_POSITION DESC

Line 4: Another wildcard search, this time for the job name. Yet again this is not efficient as it would be with these additions.

The revised statement using the new job name column would be:

01 SELECT MESSAGE_ID,MESSAGE_TYPE,SEVERITY,MESSAGE_TIMESTAMP, 02 FROM_USER,FROM_JOB, 03 FROM_JOB_NAME,FROM_JOB_USER 04 FROM TABLE(QSYS2.HISTORY_LOG_INFO()) 05 WHERE FROM_JOB_NAME = 'QZDASOINIT' 06 ORDER BY ORDINAL_POSITION DESC

Line 5: I use the FROM_JOB_NAME column which allows me to do a straight comparison just with the job's name.

The addition of these three columns may not seem like a big deal, but in my daily work it makes the searching for results from HISTORY_LOG_INFO easier to code, and faster to return results.

 

You can learn more about the HISTORY_LOG_INFO SQL Table Function from the IBM website here.

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.