Tuesday, June 15, 2021

Getting to message queue data faster

getting data from message queue using sql table function faster than view

Prior to the latest round of Technology Refreshes for 7.4 and 7.3 if I wanted to get the data from a particular message queue I would need to use the MESSAGE_QUEUE_INFO SQL View. Being a View it contains all the data from all of the message queues, not just the one I would be interested. If I wanted to retrieve the messages from one message queue it would take a while for the results to be found and returned.

There is now a MESSAGE_QUEUE_INFO Table function. Table functions are faster than Views as they are sort of like an API, I pass the message queue name to the Table function and only that message queue is searched for the information I want.

The results returned by the View and Table function are almost identical. The only difference is that the View returns columns for the name of the message queue and the library it resides. As I pass that information to the Table function it is irrelevant to its results.

MESSAGE_QUEUE_INFO Table function has four parameters:

  1. QUEUE_LIBRARY:  Name of the library containing the message queue. This is optional, if no value is given the QSYS is assumed
  2. QUEUE_NAME:  Name of the message queue. This is optional, if none is given QSYSOPR is assumed
  3. MESSAGE_FILTER:  Allowed values
    • ALL:  All messages are returned, if no value is given this is the default
    • COMPLETE:  Message that do not require a reply only
    • INQUIRY:  Message that require a reply only
    • SENDER:  Copies of the inquiry messages that were sent to other message queues and still require a reply
  4. SEVERITY_FILTER:  Message severity, which is values of 0 to 99. The default is 0

If I wanted to use this Table function to view the messages in my user profile's message queue I could use the following SQL statement:

SELECT * 
FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO(
   QUEUE_LIBRARY => 'QUSRSYS',
   QUEUE_NAME => 'SIMON',
   MESSAGE_FILTER => 'ALL',
   SEVERITY_FILTER => 0
)) ;

The results are returned in a blink of an eye:

MESSAGE_ID  MESSAGE_TYPE   MESSAGE_TEXT  SEVERITY
----------  -------------  ------------  --------
<NULL>      INFORMATIONAL  Hello there!       80

I can get the same result without using the parameter names, and changing the message filter and severity filter values:

SELECT * 
FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO('QUSRSYS','SIMON','COMPLETE',40)) ;

I think we all know I am not going to checking messages on my message queue very often. What I will be doing is checking for messages in the QSYSOPR message queue. All of the defaults for this Table function is set for QSYSOPR, so I can just use the following to see all the messages contained within that message queue:

SELECT * FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO()) ;

If I am only interested in the messages that, for example, I generated using the SEND_MESSAGE SQL procedure I could use the following:

01  SELECT * 
02  FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO(MESSAGE_FILTER => 'COMPLETE'))
03  WHERE MESSAGE_ID = 'MINE123' 
04  ORDER BY MESSAGE_TIMESTAMP DESC 
05  LIMIT 1 ;

Line 2: By using the COMPLETE message filter I am only being returned messages that did not require a reply in the results.

Line 3: Then from those results I am only selecting those that have the message id that I created, "MINE123".

Line 5: And I am only returning one result.

The results are:

MESSAGE  MESSAGE        MESSAGE
_ID      _TYPE          _TEXT           SEVERITY
-------  -------------  --------------  --------
MINE123  INFORMATIONAL  This is a test	       0

In my opinion the MESSAGE_QUEUE_INFO Table function is a great addition. While I will not be going through all my old programs and modules to replace where I have used the MESSAGE_QUEUE_INFO View. I will be using the Table function in anything new I do.

 

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.4 TR4, and will work for 7.3 TR10 too.

5 comments:

  1. Great read Simon, thanks for sharing.

    ReplyDelete
  2. Guillermo Escotto TaylorNovember 17, 2021 at 6:37 AM

    Excellent Simon.

    ReplyDelete
  3. It would have been nice if they had include the MSGQ and MSGQ Library in this return.

    ReplyDelete
  4. Excellent info! Many thanks for sharing!

    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.