Faster way to find who answered a message

Six years ago I wrote a post about using the MESSAGE_QUEUE_INFO SQL View for finding jobs that had errored, and who had answered those messages. What I would like to do today is to give an example of more efficient, faster, way to get the same results using the MESSAGE_QUEUE_INFO Table Function.

On the whole retrieving results from Table Functions tend to be faster than getting the same results from a View. Most Table Functions have parameters that are used to narrow down the results to a smaller set of results, which can then be interrogated with the Where clause. With a View I have all the results there can be, and then the Where clause has to search through all of those to find what I want.

The MESSAGE_QUEUE_INFO Table Function has four parameters. I am going to be using three in these examples:

1. QUEUE_LIBRARY:  The library that contains the message queue
2. QUEUE_NAME:  The message queue I want the information from
3. SEVERITY_FILTER:  Minimum severity of the messages I want returned

The following SQL statement will return the jobs in the message queue QSYSOPR that have a severity level of 99:

01 SELECT MESSAGE_TIMESTAMP,MESSAGE_ID,FROM_JOB,MESSAGE_TEXT, 02 MESSAGE_KEY,ASSOCIATED_MESSAGE_KEY 03 FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO( 04 QUEUE_LIBRARY => 'QSYS', 05 QUEUE_NAME => 'QSYSOPR', 06 SEVERITY_FILTER => 99)) 07 ORDER BY 1

Lines 1 and 2: I think the of the column names describe their contents. ASSOCIATED_MESSAGE_KEY is not null when the result is a reply for a message, it will contain the message id of the original message.

Lines 3 – 6: This is the Table Function, including the parameters passed to it. I want the results from QSYSOPR, in the library QSYS, with a severity of 99.

Line 7: I want my results sorted by the first column in the results, MESSAGE_TIMESTAMP.

This is a sample of the returned results. I did not want to return too many as I have to break the results out into three sections to show all I need to:

MESSAGE_TIMESTAMP _ID _JOB -------------------------- ------- ------------------------- 2023-08-26 10:17:06.356371 CPA5305 168096/USER451/JOB451 2023-08-26 10:17:06.376819 <NULL> 168096/OPER030/DSP901 2023-08-26 10:27:33.538743 CPA4263 168096/OPER030/SAV031 2023-08-26 10:27:33.555951 <NULL> 168096/OPER030/DSP901 MESSAGE_TEXT ----------------------------------------------------- Record not added. Member CSTNOTES is full. (C I 9999) 9999 Volume 140009 not loaded or device TAP01 not ready... R MESSAGE ASSOCIATED _KEY MESSAGE_KEY -------- ----------- 00015060 00015130 00015060 00015700 000158E0 00015700

The first and second results are associated. The first result is the original message, therefore, its associated message key is null. The second result's associated message key contains the message id of the first, thus, it must be the reply to the error.

The associated message key of the third and fourth results show that they are associated. The third result being the error, and the fourth the reply to it.

It would be easier to see the original message and the reply if I were to combine this into one result. To do so I used the following statement:

01 SELECT A.MESSAGE_TIMESTAMP,A.MESSAGE_ID,A.FROM_JOB,A.MESSAGE_TEXT, 02 B.MESSAGE_TIMESTAMP as "Reply time", 03 SUBSTR(B.MESSAGE_TEXT,1,5) AS "Reply", 04 B.FROM_USER AS "Reply user" 05 FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO( 06 QUEUE_LIBRARY => 'QSYS', 07 QUEUE_NAME => 'QSYSOPR', 08 SEVERITY_FILTER => 99)) A, 09 LATERAL 10 (SELECT MESSAGE_TIMESTAMP,MESSAGE_TEXT,FROM_USER 11 FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO( 12 QUEUE_LIBRARY => 'QSYS', 13 QUEUE_NAME => 'QSYSOPR', 14 SEVERITY_FILTER => 99)) 15 WHERE ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B 16 ORDER BY MESSAGE_TIMESTAMP DESC

Lines 1 – 4: The columns prefixed with the "A" come from the error result, and those "B" from the reply result. Some of the columns from the reply result I have given different column headings to distinguish them from the error result columns.

Lines 5 – 8: Table Function to get the errors. Do notice that line 8 ends with a comma ( , ).

Line 9: I use the Lateral expression to join the Table Function for the error and the one for its reply together.

Lines 10 – 15: Table Function statement to get the results for the reply result. By joining, on line 15, the associated message key in the reply to the message id in the error I combine the two sets of results into one.

Line 16: I want to return the results sorted by the message timestamp in descending order.

The results have so many columns I am displaying them in three sections:

MESSAGE MESSAGE_TIMESTAMP _ID FROM_JOB -------------------------- ------- --------------------- 2023-08-26 10:17:06.356371 CPA5305 168096/USER451/JOB451 2023-08-26 11:01:51.612658 CPA4263 168096/OPER030/SAV031 MESSAGE_TEXT ----------------------------------------------------- Record not added. Member CSTNOTES is full. (C I 9999) Volume 140009 not loaded or device TAP01 not ready... Reply time Reply Reply user -------------------------- ----- --------- 2023-08-26 10:17:06.376819 9999 OPER030 2023-08-26 11:06:05.954239 R OPER030

It may be a bit hard to see but now all the information I need about the error message and its reply are in one row of result. And it is much faster than the same approach using the equivalent View.

To remind myself of the difference I ran the equivalent with the MESSAGE_QUEUE_INFO View. It took much longer than the above with the MESSAGE_QUEUE_INFO Table Function to return the same results.

 

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

 

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