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_DATA 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.

15 comments:

  1. Hi,
    When I run your SQL statement, I receive the following error message :
    SQL0204 : MESSAGE_FILE_DATA de type *FILE dans QSYS2 non trouvé.

    ReplyDelete
  2. I also have a issue with the view not being out there in QSYS or QSYS2

    Message: [SQL0204] MESSAGE_FILE_DATA in QSYS2 type *FILE not found

    We're running on V7.3
    Is it that we don't have TR6 installed?

    ReplyDelete
    Replies
    1. Yes, this is only available for 7.3 TR6 and up.
      If you are at a lower level of PTFS, as you have found, it is not there.

      Delete
  3. I'll suggest to our IT Director that we install 7.3 TR6.

    Thanks!

    DSPPTF LICPGM(5770SS1) SELECT(SF99727) RLS(V7R3M0)
    PTF 5770SS1-SF99727 V7R3M0 not found

    ReplyDelete
    Replies
    1. Don't forget to check if there are any separate PTFs for the enhancements to Db2 for i and RPG not included in the standard TR6 PTF.

      Delete
  4. Hi Simon,

    It returns gibrish texts

    MESSAGE_TEXT

    ä ê á è ñ | + à è á ä + + | è â á å ê á è á ê è ç + ä è ñ | + à è á
    à Á Ã / Í % È / > À Ä ? Ê Ê Á Ä È Ñ ? > È Ê / > Ë / Ä È Ñ ? > È ` ø Á Ä ? _ Â Ñ > /
    ä ? Ê Ê Á Ä È Ñ ? > È Ê / > Ë / Ä È Ñ ? > Ç / Ë È Ç Á Ë / _ Á Ë Ñ Å > / Ë À Á Ã / Í % È È Ê / > Ë / Ä
    å ê | í & & ê | ã ñ < á + à ê í < á ä | ( â ñ + è ñ | + + | è ã | í + à ñ + á à ë ê í < á ã
    + ? / ø ø % Ñ Ä / Â % Á Ê Í % Á Á Ì Ñ Ë È Ë Ã ? Ê È Ê / > Ë / Ä È Ñ ? >

    ReplyDelete
    Replies
    1. You need to CAST the column to be another CCSID.
      I use CCSID 37 as I am in the USA.

      Delete
  5. I have been using SQL to access this view and it's great! My current problem is I'm trying to create a subfile display retrieval of messages in the view that contain a particular text string. The SELECT statement in RPGLE chokes and returns a bizarre SQLCODE value that looks to be the largest number that field can hold.

    Any ideas?

    ReplyDelete
    Replies
    1. If you are using a load all subfile and a multi-row fetch this should be easy.

      An example of how to do the multi-row fetch is here. There are other example that you can find by searching this blog.

      Delete
    2. I agree it ought to be easy... but for reasons I haven't figured out yet SQL is choking on the SELECT statement against MSGF_DATA. The select simply asks for matches for the library and message file name, uses LIKE for contents of message text and the message ID. Nothing fancy. Nothing I haven't used on dozens of other tables and views...

      Delete
    3. Then insert the code to use GET DIAGNOSTICS and check what it returns. That should give you the reason the statement has failed.

      Delete
  6. Hi, did you mean MESSAGE_FILE_INFO or MESSAGE_FILE_DATA in the intro?

    ReplyDelete
    Replies
    1. Oops! Thank you for bringing that to my attention. The correction has been made.

      Delete
  7. Is it possible to insert / update / delete a message using sql?

    ReplyDelete
  8. This saved me a ton of time! Thanks Simon!

    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.