New parameters added to JOBLOG_INFO makes sorting and selecting so much better

One of what, IMHO, is the best modifications in the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, has been the new parameters added to the JOBLOG_INFO table function. These being:

• MESSAGE_ORDER:  The order of the results returned
• MESSAGE_LIMIT:  The number of messages to return in the results
• MESSAGE_TIMESTAMP:  All messages that are equal or greater (newer) than the entered timestamp

I can view all of my job's joblog using the following SQL statement:

01 SELECT * 02 FROM TABLE(JOBLOG_INFO( 03 JOB_NAME => '731185/SIMON/QPADEV0008' 04 ))

Line 3: I want to display the job log for this particular job.

Most of time I do not want all the joblog entries, I only want to view the most recent entries. I would achieve this with the following:

01 SELECT ORDINAL_POSITION,MESSAGE_TIMESTAMP,MESSAGE_ID 02 FROM TABLE(JOBLOG_INFO( 03 JOB_NAME => '731185/SIMON/QPADEV0008' 04 )) 05 ORDER BY ORDINAL_POSITION DESC 06 LIMIT 5

Line 1: For the following examples I am only going to display these columns from the results:

• ORDINAL_POSITION:  A unique generated number of each row of the results
• MESSAGE_TIMESTAMP:  The time when the message was issued
• MESSAGE_ID:  Message id for this message

Line 5: I am ordering the results from the table function in descending order.

Line 6: And then only select five from all of those results.

Notice in the results, below, that the Ordinal Position values show that the results are in descending order.

ORDINAL_ POSITION MESSAGE_TIMESTAMP MESSAGE_ID -------- -------------------------- ---------- 15 2026-10-17 16:01:12.787100 SQL0199 14 2026-10-17 16:01:07.989270 <NULL> 13 2026-10-17 16:00:58.978980 <NULL> 12 2026-10-17 16:00:46.474429 <NULL> 11 2026-10-17 16:00:43.356077 RNS9310

This is a lot of processing for just the last five results. The entries from the entire joblog are generated by JOBLOG_INFO, those results are then sorted into descending order, and from those sorted results only the first five are returned.

The new MESSAGE_ORDER makes it simpler. It has two allowed values:

• ASCENDING:  The results are returned in ascending order, oldest first. This is the default
• DESCENDING:  The results are returned in descending order, newest first

I could change my previous SQL statement to use this new parameter, rather than use the ORDER BY clause for sort the returned results:

01 SELECT ORDINAL_POSITION,MESSAGE_TIMESTAMP,MESSAGE_ID 02 FROM TABLE(JOBLOG_INFO( 03 JOB_NAME => '731185/SIMON/QPADEV0008', 04 MESSAGE_ORDER => 'DESCENDING' 05 )) 06 LIMIT 5

Line 4: I have added the MESSAGE_ORDER here, as I have given its parameter name, I could place it in the first position if I so desired.

The results are:

ORDINAL_ POSITION MESSAGE_TIMESTAMP MESSAGE_ID -------- -------------------------- ---------- 1 2026-10-17 16:01:12.787100 SQL0199 2 2026-10-17 16:01:07.989270 <NULL> 3 2026-10-17 16:00:58.978980 <NULL> 4 2026-10-17 16:00:46.474429 <NULL> 5 2026-10-17 16:00:43.356077 RNS9310

The results are almost the same as previous ones, except the Ordinal Position values are different. As this column is informational rather than part of the joblog's data, therefore, I consider the different Ordinal Position values irrelevant.

That still returns all the results from JOBLOG_INFO, and then selects the first five. If I just wanted five results, I could use the MESSAGE_LIMIT parameter:

01 SELECT ORDINAL_POSITION,MESSAGE_TIMESTAMP,MESSAGE_ID 02 FROM TABLE(JOBLOG_INFO( 03 JOB_NAME => '731185/SIMON/QPADEV0008', 04 MESSAGE_ORDER => 'DESCENDING', 05 MESSAGE_LIMIT => 5 06 ))

Line 4: The results will be returned in descending order.

Line 5: Only five results will be returned.

The results look the same as the previous two, which confirms this does what the others did, just in an easier way.

ORDINAL_ POSITION MESSAGE_TIMESTAMP MESSAGE_ID -------- -------------------------- ---------- 1 2026-10-17 16:01:12.787100 SQL0199 2 2026-10-17 16:01:07.989270 <NULL> 3 2026-10-17 16:00:58.978980 <NULL> 4 2026-10-17 16:00:46.474429 <NULL> 5 2026-10-17 16:00:43.356077 RNS9310

For the above to work I need to know the number of joblog entries that happened after the event I want to capture. An easier approach would be to retrieve all the entries that happened after a certain time, for example just before an error happened. This is where the MESSAGE_TIMESTAMP is useful:

01 SELECT ORDINAL_POSITION,MESSAGE_TIMESTAMP,MESSAGE_ID 02 FROM TABLE(JOBLOG_INFO( 03 JOB_NAME => '731185/SIMON/QPADEV0008', 04 MESSAGE_ORDER => 'DESCENDING', 05 MESSAGE_TIMESTAMP => '2026-10-17 16:00:40.000000' 06 ))

Line 4: I am still retrieving the results in descending order.

Line 5: I want to have the results for everything that happened at this time or greater (later).

The results show:

ORDINAL_ POSITION MESSAGE_TIMESTAMP MESSAGE_ID -------- -------------------------- ---------- 1 2026-10-17 16:01:12.787100 SQL0199 2 2026-10-17 16:01:07.989270 <NULL> 3 2026-10-17 16:00:58.978980 <NULL> 4 2026-10-17 16:00:46.474429 <NULL> 5 2026-10-17 16:00:43.356077 RNS9310 6 2026-10-17 16:00:43.356000 CPC0904 7 2026-10-17 16:00:43.351651 RNS9308

Seven results were returned.

I can combine the MESSAGE_TIMESTAMP and MESSAGE_LIMITS parameters to return the five joblog entries that happened after a particular time. The statement I would use to do this is:

01 SELECT ORDINAL_POSITION,MESSAGE_TIMESTAMP,MESSAGE_ID 02 FROM TABLE(JOBLOG_INFO( 03 JOB_NAME => '731185/SIMON/QPADEV0008', 04 MESSAGE_TIMESTAMP => '2026-10-17 16:00:40.000000', 05 MESSAGE_LIMIT => 5 06 ))

The MESSAGE_ORDER parameter is not present as I want the results in default, ascending, order

Line 4: I want the results starting at this time.

Line 5: I only want five results.

The results show the five job log entries that happened either on or after that time:

ORDINAL_ POSITION MESSAGE_TIMESTAMP MESSAGE_ID -------- -------------------------- ---------- 1 2026-10-17 16:00:43.351651 RNS9308 2 2026-10-17 16:00:43.356000 CPC0904 3 2026-10-17 16:00:43.356077 RNS9310 4 2026-10-17 16:00:46.474429 <NULL> 5 2026-10-17 16:00:58.978980 <NULL>

The addition of these three new parameters is going to make my life easier look at job logs. Will you make use of them too?

 

You can learn more about the JOBLOG_INFO SQL table function the IBM website here.

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.