Wednesday, August 30, 2023

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.

No comments:

Post a Comment

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.