Wednesday, June 26, 2019

It is now easier searching message files

new sql view for searching message files

I have always found it a bother to search for IBM i message ids. Which message file contains the particular message id I want? What messages could I use for a date validation error? Etc. I always had wished for an easier way to perform searches like this.

Fortunately the latest Technology Refresh, IBM i 7.3 TR6, has a new Db2 for i view to make my searches for messages so much easier. The view MESSAGE_FILE_INFO returns a row for each message from all the message files in the IBM i partition I am using. I am not going to describe what the columns are here, as I think their names explain what they contain. For a full list of all the columns contained in this view click on the link to IBM's documentation at the bottom of this post.

Everything you can see with the Display Message Description command, DSPMSGD, is in this view. Using this view I do not have to give the message file's name when searching for a particular message id. For example if I am looking for the description for the message "RSC0082" I would have used the DSPMSGD command:

DSPMSGD RANGE(RSC0082)

And I would have received the message: "Message identifier RSC0082 not found in message file QCPFMSG in QSYS". To find the message's description using DSPMSGD I would either have to know which message file contains the "RSC" messages, or I would have to repeat this command for one message file at a time until I found the right one.

Using the MESSAGE_FILE_INFO view I could just use the following statement:

01  SELECT MESSAGE_FILE_LIBRARY,MESSAGE_FILE,MESSAGE_ID
02    FROM QSYS2.MESSAGE_FILE_DATA
03   WHERE MESSAGE_ID = 'RSC0082'

Alas, when I run this command on the IBM i I use for testing the statement errors with a SQL status of 42501 and SQL code of -403. When I look in the job log I found that I am not authorized to access the message file QQRYLIB/QQUMSG. Therefore, I need to make an addition to the where clause to exclude the message file QQUMSG, line 3.

01  SELECT MESSAGE_FILE_LIBRARY,MESSAGE_FILE,MESSAGE_ID
02    FROM QSYS2.MESSAGE_FILE_DATA
03   WHERE MESSAGE_FILE <> 'QQUMSG'
04     AND MESSAGE_ID = 'RSC0082'

The results show that this message is found in three message files:

MESSAGE_FILE_LIBRARY  MESSAGE_FILE  MESSAGE_ID
#RPGLIB               QRPG2MSG      RSC0082
QDEVTOOLS             QRPGMSG       RSC0082
QRPG38                QRPG3MSG      RSC0082

Another way I waste time with message files is looking for the right message. We all build message files to contain messages we want displayed on display files, etc., and over the years the message file gets bigger and bigger with all variations of errors that are checked for. To find the one I want to use for a specific error becomes a chore of scrolling through list of messages looking for the right text. Using MESSAGE_FILE_DATA I can just perform a wild card search on the message text.

01  SELECT MESSAGE_ID,MESSAGE_TEXT,
02         MESSAGE_SECOND_LEVEL_TEXT
03    FROM QSYS2.MESSAGE_FILE_DATA
04   WHERE MESSAGE_FILE = 'TESTMSGF'
05     AND MESSAGE_FILE_LIBRARY = 'MYLIB'
06     AND UPPER(MESSAGE_TEXT) LIKE '%DATE%'

Line 6: I need to convert the case of the characters in the MESSAGE_TEXT column to find every occurrence of "date". If I did not do this then searching for "DATE", "date", or "Date" would return different results.

My results show that I have two messages that are basically the same, TST0001 and TST0901.

MESSAGE ID  MESSAGE_TEXT                     MESSAGE_SECOND_LEVEL_TEXT
TST0001     Don't like that date!            The date you have enter...
TST0082     Date range invalid               From date must be less ...
TST0682     Date is outside of agreed range  The data entered is eit...
TST0901     Date entered is invalid          The date you have enter...

I can now pick the message id to use, and not risk creating another duplicate message.

 

This may not be the sexiest thing added to this Technology Refresh, but it is one I am going to be using frequently.

 

You can learn more about the MESSAGE_FILE_DATA view from the IBM website here.

 

This article was written for IBM i 7.4 and 7.3 TR6.

No comments:

Post a Comment

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.