Pages

Wednesday, May 29, 2024

Getting the message for the SQL code quickly

All of us who use SQL have encountered errors, and when we do a SQL code is returned. This then leaves us trying to find the text associated with the SQL code to understand what had happened.

If this is in a RPG program I always recommend that you use GET DIAGNOTISTICS, which will return all you wanted to know about the message and a whole lot more too. If I am quickly writing a RPG program that will only be used one, or maybe twice, or a CL program and I get a SQL code I need to look it up to understand what happened.

There are two types of SQL codes:

  1. Those less than zero: Errors
  2. Those greater than zero: Warnings

All SQL codes have an equivalent IBM i message id. Basically it is 'SQL' followed by the SQL code. For example, SQL code -423 becomes message id SQL0423.

All message ids' information in IBM i are held in message files. SQL messages have their own message file QSQLMSG in the library QSYS.

If I wanted to look up the message for SQL code -423, message id SQL0423, I could use the Display Message Description command,DSPMSGD:

01  DSPMSGD RANGE(SQL0423) MSGF(QSQLMSG)

Alternatively I could use the MESSAGE_FILE_DATA View to find the message id:

01  SELECT MESSAGE_ID,MESSAGE_TEXT,MESSAGE_SECOND_LEVEL_TEXT
02    FROM QSYS2.MESSAGE_FILE_DATA
03   WHERE MESSAGE_FILE = 'QSQLMSG'
04     AND MESSAGE_ID = 'SQL0423'

The disadvantage of using this View is as it contains all messages from all message files it can take some time to return the result for the message id I want.

In the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, comes a new Table function, SQLCODE_INFO, that allows me to get the information for just one SQL code. The Table function just has one parameter the SQL code. For example:

01  SELECT * 
02    FROM TABLE(SYSTOOLS.SQLCODE_INFO(
03                 P_SQLCODE => -423)) ;

The parameter can be used with the parameter's name, P_SQLCODE, or without. As there is only one parameter I am using it without the parameter name, as there is no confusion what the value is:

01  SELECT * FROM TABLE(SYSTOOLS.SQLCODE_INFO(-423))

Notice that the Table function SQLCODE_INFO is in the library SYSTOOLS.

It returns three columns, which will help me determine the cause of the issue.

MESSAGE
_ID      MESSAGE TEXT           MESSAGE_SECOND_LEVEL_TEXT
-------  ---------------------  ---------------------------------------- 
SQL0423  Locator &1 not valid.  Cause . . . . . :   The value of loca...

These columns contain the same data as I retrieved from the MESSAGE_FILE_DATA View, and the same as I would see using the DSPMSGD.

This is a very useful addition that I know I will be using.

 

You can learn more about the SQCLCODE_INFO SQL Table function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.

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.