Retrieve information about SQL state

For over a year we have had a SQL table function to be able to retrieve information about the SQL codes. Now we can do the same kind of thing for SQL states. The major difference is that we retrieve the information for the SQL statuses from a SQL View: SQLSTATE_INFO in the library QSYS2.

When working with SQL codes and statuses it is important to remember that they are not all a one-to-one relationship. There are some SQL states that are associated with more than one SQL code, and there are some SQL codes that are associated with more than one SQL state.

The SQLSTATE_INFO view returns three columns:

• SQLSTATE_VALUE:  SQL state
• SQLCODE_VALUE:  SQL code
• SQLSTATE_DETAIL:  Text for the SQL state. Interestingly this is returned only in English

If SQL state is associated with more than one SQL code each combination will have its own row.

In its simplest form I can get the details for the SQL code 01515:

01 SELECT * 02 FROM QSYS2.SQLSTATE_INFO 03 WHERE SQLSTATE_VALUE = '01515'

Line 3: Notice that the SQL state value is character.

Which returns the following result:

SQLSTATE SQLCODE _VALUE _VALUE SQLSTATE_DETAIL -------- ------- ------------------------------------------------ 01515 304 The null value has been assigned to a variabl...

I can retrieve the message text for SQL code with the following:

01 SELECT MESSAGE_ID,MESSAGE_TEXT 02 FROM TABLE(SYSTOOLS.SQLCODE_INFO(304))

Which returns:

MESSAGE _ID MESSAGE_TEXT ------- ---------------------------------------------- SQL0304 Conversion error in assignment to variable &2.

When I find a SQL status that has two SQL codes I can use the following to display the two codes:

01 SELECT A.*, 02 SUBSTR(B.MESSAGE_SECOND_LEVEL_TEXT,21) AS "Msg 2nd level" 03 FROM QSYS2.SQLSTATE_INFO A, 04 LATERAL 05 (SELECT MESSAGE_SECOND_LEVEL_TEXT 06 FROM TABLE(SYSTOOLS.SQLCODE_INFO(A.SQLCODE_VALUE))) B 07 WHERE A.SQLSTATE_VALUE = '24502'

Line 1: I am selecting all the columns from SQLSTATE_INFO.

Line 2: I am substring the contents of SQLCODE_INFO's MESSAGE_SECOND_LEVEL_TEXT starting at the twenty first position. Why? The starting twenty characters are "Cause . . . . . : ", which I do not care about.

Line 4: I use a Lateral to join a view to a table function.

This returns two results:

SQLSTATE SQLCODE _VALUE _VALUE SQLSTATE_DETAIL Msg 2nd level -------- ------- ------------------ ---------------------------------- 24502 -502 The cursor iden... The cursor specified in an OPEN... 24502 -7055 The cursor iden... Cursor &1 will become ambiguous...

What about the other way? One SQL code that is associated with more than one SQL state:

01 SELECT SQLCODE_VALUE,SQLSTATE_VALUE,SQLSTATE_DETAIL 02 FROM QSYS2.SQLSTATE_INFO 03 WHERE SQLCODE_VALUE = -30090

This returns three results:

SQLCODE SQLSTATE _VALUE _VALUE SQLSTATE_DETAIL ------- -------- ----------------------------------------- -30090 25000 An insert, update, or delete operation... -30090 2D528 Dynamic COMMIT or COMMIT ON RETURN pro... -30090 2D529 Dynamic ROLLBACK is invalid for the ap...

 

These two, SQLSTATE_INFO and SQLCODE_INFO, complement each other's information well.

 

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

 

This article was written for IBM i 7.6 and 7.5 TR6.