Getting to message queue data faster

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.