Using SQL for QSYSOPR Messages and Replies #IBMi

I'm always monitoring remote IBM i systems. I use SQL for the bulk of that monitoring because between #SQLTools and the IBM Services I can get just about anything I need. Whether that's using ACS Run SQL Scripts or, from the Green Screen, using SQL iQuery, monitoring remote IBM i partitions or systems has never been easier.

One thing I do a lot of is look at QSYSOPR message queue. I have an early morning Scheduled Job that emails a list of any jobs sitting in MSGW (Message Wait) status so that the day's work can being when User begin arriving to work or signing on remotely. But I also periodically check the active jobs. If I see one that has an unusual task or is in MSGW, I'll do a quick query of the QSYSOPR message.

But using just the standard IBM Service MESSAGE_QUEUE_INFO table function is well, kind of verbose. So I like to pair it down a bit. My preference is to see the message type, severity, message text and the from name. To do that I simply include the column names in the SELECT statement.

?SELECT M.message_type
???????? M.severity AS SEV,
???????? CAST(M.MESSAGE_TIMESTAMP AS TIMESTAMP(0)) AS POSTED,
???????? FROM_PROGRAM,
???????? M.MESSAGE_TEXT
????? FROM TABLE (
?????????????? qsys2.MESSAGE_QUEUE_INFO()
?????????? ) M;,        

I've been using this for a while. I created a source file member named QSYSOPR with this routine in it, and then using SQL iQuery I would just run it as follows:

RUNiQRY SRCMBR(QSYSOPR)        

The RUNiQRY command default to SRCFILE(*LIBL/QSQLSRC) so just using the SRCMBR parameter usually works the way you need it to. Also, remember, last month I made SQL iQuery a no-charge licensed program. Anyone can download and install it for free. We only charge for support if you request support so there really isn't anything stopping you for getting a copy onto your system any longer.

After using this SELECT statement for a while I was seeing a new location being installed that had a number of MSGW showing up. So I thought, "wouldn't it be great to see the message reply as well as just the message?"

So I solved this requirement by simply coding an outer join to the MESAGE_QUEUE_INFO function itself. I did a bit a inside-baseball research and found that the Message Key of the message and the associated Message Key for Replies is included in the resultSet. So I join them and used that to connect the two independent messages.

It looks like this:

? SELECT M.message_type
???????? M.severity AS SEV,
???????? CAST(M2.MESSAGE_TIMESTAMP AS TIMESTAMP(0)) AS REPLY_TIME,
???????? CASE
???????????? WHEN M.MESSAGE_TYPE = 'INQUIRY'
???????????????? AND M2.MESSAGE_TEXT IS NULL THEN '*WAITING'
???????????? ELSE M2.message_text
???????? END AS REPLY,
???????? CAST(M.MESSAGE_TIMESTAMP AS TIMESTAMP(0)) AS POSTED,
???????? M.FROM_PROGRAM,
???????? M.MESSAGE_TEXT
????? FROM TABLE (
?????????????? qsys2.MESSAGE_QUEUE_INFO()
?????????? ) M
?????????? LEFT OUTER JOIN (
?????????????????? SELECT M1.message_type,
????????????????????????? M1.severity AS SEV,
????????????????????????? M1.message_text,
????????????????????????? M1.ASSOCIATED_MESSAGE_KEY,
????????????????????????? MESSAGE_TIMESTAMP
?????????????????????? FROM TABLE (
?????????????????????????????? qsys2.MESSAGE_QUEUE_INFO()
?????????????????????????? ) M1
?????????????????????? WHERE M1.ASSOCIATED_MESSAGE_KEY IS NOT NULL
?????????????? ) M2
?????????????? ON M2.ASSOCIATED_MESSAGE_KEY = M.MESSAGE_KEY
????? WHERE M.ASSOCIATED_MESSAGE_KEY IS NULL
?? ORDER BY m.message_timestamp DESC;        

I thought this was awesome, except that it is a bit verbose. Storing it in a source member solved that issue when using iQuery, but while I'm in ASC RUN SQL Scripts I don't have the ability to run iQuery Scripts (we asked IBM if they'd open up ACS Run SQL Scripts to allow it t run iQuery Scripts as well; they told us to go pound sand) so I decided to create a VIEW instead.

Creating a view for this SELECT statement is painlessly easy:

create or repalce VIEW SQLTOOLS.QSYSOPR AS
( <include the above SQL statment (without the ;) here.
);        

In fact, we are now shipping this VIEW with SQL Tools but you can just as easily enter it into your own system using cut/paste.

The benefit of the VIEW is not just brevity. If you put this view on all your systems (hence, including it in SQL Tools) I can not only use SQL iQuery to run it, but I can just type a short SELECT * FROM CHICAGO.SQLTOOLS.QSYSOPR in ACS RUN SQL Scripts or even STRSQL and get the results you need.

That's all there is to it.