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:


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

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 * 
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:


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.

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