Wednesday, April 26, 2023

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.

1 comment:

  1. This is great Simon, thank you. I can't wait until we're on 7.4 soon.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.