Using SQL for message queue data

One of the new enhancements that came with the latest TRS for IBM i 7.3 and 7.2 is a new SQL View: MESSAGE_QUEUE_INFO. This view returns one line for each message in a message queue, in a similar manner to what is returned by the Display Messages command, DSPMSG.

This will allow me to have a way to quickly, and easily, search a message queue for a message. I must have *USE authority to the message queue, and *EXECUTE authority to the library it is in. One thing to remember is that message queues do get full, and when they do messages from them will be deleted. Also certain message queues are cleared when the IBM i partition is IPL-ed. If you are looking for a message that may have been issued sometime ago you might have better luck searching the History log via SQL.

It should come as no surprise that this view is in the library QSYS2. I am not going to list all of the columns it contains, only reference the ones I will be using in these examples. If you want a full list of the columns I am providing a link to IBM's KnowledgeCenter page for this view at the bottom of this post. I will also be using the "System Column Names" here, rather than the long names due to the width limitations of this page.

As I mentioned above I can only see the messages in the message queues I am authorized to, so let me list those:

01 SELECT DISTINCT MSGQ_LIB,MSGQ_NAME 02 FROM QSYS2.MESSAGE_QUEUE_INFO

Line 1: By using a SELECT DISTINCT I am only going to receive one row in my results for the combination of message queue library and message queue. The columns I want are:

• MSGQ_LIB = message queue library
• MSGQ_NAME = message queue name

Line 2: Gives the name of the view and its library.

My results would look something like this:

MESSAGE_QUEUE_LIBRARY MSGQ_NAME QSYS DSP01 QSYS QSYSOPR QUSRSYS QLWISVR QUSRSYS QNETSPLF QUSRSYS QSECOFR QUSRSYS QTCP QUSRSYS QTMHHTTP QUSRSYS ZENDADMIN

What if I want to look in my own message queue with the most recent messages first, it is very easy to do:

01 SELECT MSG_TIME AS TIME,MSGID AS MSGID,MSG_TYPE AS TYPE, 02 CAST(MSG_TEXT AS CHAR(100) CCSID 37) AS TEXT, 03 SEVERITY,FROM_USER,FROM_JOB,FROM_PGM 04 FROM QSYS2.MESSAGE_QUEUE_INFO 05 WHERE MSGQ_NAME = 'SIMON' 06 ORDER BY MSG_TIME DESC

Line 1: I don't want to use the default column heading for this view. By using the AS I am changing the column name and, therefore, what appears at the top of the column.

Line 2: I am going to make the column that contains the first level message text to a more reasonable size, 1024 to 100, and I need to change the CCSID too as its default, CCSID 1200, looks like weird characters to me. As I am in the USA and by changing it to CCSID 37 I can read it. The columns I want in my results are:

• MSGQ_TIME = timestamp the message was sent
• MSGID = message id of the message, if null was an "impromptu" message
• MSG_TYPE = type of the message (values given in the IBM documentation)
• MSG_TEXT = first level message text
• SEVERITY = message severity
• FROM_USER = current user profile when message sent
• FROM_JOB = qualified job name
• FROM_PGM = program that sent the message

Line 5: I am only concerned with my user profile's message queue.

Line 6: And I want the most recent result first.

The results are too long to fit on one line so I am breaking them up into multiple lines, below.

TIME MSG_ID TYPE 2017-99-99-22.59.43.489722 CPF1241 COMPLETION 2017-99-99-22.45.30.989747 CPF1240 COMPLETION TEXT Job 667786/SIMON/TESTCLP completed normally on 04/17/17 at Job 667774/SIMON/SIMON ended abnormally. SEVERITY FROM_USER FROM_JOB FROM_PGM 0 SIMON 667786/SIMON/TESTCLP QWTMCEOJ 50 SIMON 667774/SIMON/SIMON QWTMCEOJ

This final example I have taken from the IBM documentation page for this view. It is a pretty cool piece of code to retrieve an error message and the reply it received. I have just added some additional columns I would want to have to diagnose the error.

01 SELECT A.MSG_TIME,A.MSGID, 02 CAST(A.MSG_TEXT AS CHAR(50) CCSID 37) AS MSG_TEXT, 03 A.FROM_USER,A.FROM_JOB, 04 B.MSG_TIME,CAST(B.MSG_TEXT AS CHAR(1) CCSID 37) AS RPLY, 05 B.FROM_USER 06 FROM QSYS2.MESSAGE_QUEUE_INFO A INNER JOIN 07 QSYS2.MESSAGE_QUEUE_INFO B 08 ON A.MSG_KEY = B.ASSOC_KEY 09 WHERE A.MSG_TYPE = 'INQUIRY' 10 AND B.MSG_TYPE = 'REPLY' 11 ORDER BY B.MSG_TIME DESC

The thing I like about this statement is the way the view is joined to itself.

Lines 1 – 5: The columns that will be displayed in the results are:

• Columns from the first instance of the view (A)
  • A.MSG_TIME = timestamp the message was sent
  • A.MSGID = message id of the message, if null was an "impromptu" message
  • A.MSG_TEXT = first level message text
  • A.FROM_USER = current user profile when message sent
  • A.FROM_JOB = qualified job name
• Columns from the second instance of the view (B)
  • B.MSG_TIME = timestamp the message was sent
  • B.MSG_TEXT = first level message text, I am only interested in the first character and I am calling this column REPLY
  • B.FROM_USER = current user profile when message sent

Lines 6 – 8: By joining the view to itself using a inner join means that only rows that match the ON criteria in both will be included. I must admit I still use my SQL joins diagram to find the right type of join. I am joining using the message key, MSG_KEY which is a unique number generated when the message is sent, to the associated message key, ASSOC_KEY for a reply message it contains the message key of the message that was answered.

Lines 9 and 10: I only want inquiry (error) messages and their replies.

Line 11: And I want to see the most recent first.

My results would look something like this:

MESSAGE_TIMESTAMP MSGID 2017-99-99-23.21.59.891938 CPA0702 2017-99-99-22.10.14.888894 CPA3387 MSG_TEXT CPF0001 received by procedure TESTCLP. (C D I R) Device PRT01A not available. (C R) FROM_USER FROM_JOB SIMON 667799/SIMON/TESTCLP QSPLJOB 665012/QSPLJOB/PRT01A MSG_TIME RPLY FROM_USER 2017-99-99-23.22.10.264822 I SIMON 2017-99-99-22.59.47.855670 C SYSADMIN1

I have to commend IBM for making another extremely useful view that I know I will be using to help find error messages when I get one of those "I got an error earlier today. Can you fix it?" telephone calls.

 

You can learn more about the MESSAGE_QUEUE_INFO command from the IBM website here.

 

This article was written for IBM i 7.3 TR2, and should work for 7.2 TR6 too.