Pages

Wednesday, May 10, 2017

Using SQL for message queue data

new view for viewing messages in message queues

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.

6 comments:

  1. Can´t find msgfiles in 6.1, as above? Do You know where I can find it in 6.1?

    ReplyDelete
  2. Yet another awesome useful informtion.. Thanks again Simon!!

    ReplyDelete
  3. Perfect, thank you.

    It made it possible to write a command/program, to remove messages from a MSGQ based on text.

    ReplyDelete
  4. I'll keep this in mind

    ReplyDelete
  5. this is very helpful. Thanks again for all the neat stuff you write.

    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.