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.