DB2 FOR LUW / DB2FORLUW / 970 - DB2

A SQL routine may fail with the following error intermittently: 
 
SQL0435N  An invalid application defined SQLSTATE "<sqlstate>" 
was specified. 
 
This would happen if there was a SIGNAL or RESIGNAL statement 
where the SQLSTATE value was being supplied to a variable.   The 
issue happens because a random junk byte (in the range 0-9,A-Z) 
gets stored after the SQLSTATE variable causing us to believe 
the length of the SQLSTATE variable is longer than expected.  If 
the length is more than 5 bytes it would violate the rules for 
an SQLSTATE in a SIGNAL/RESIGNAL statement and thus return an 
SQL0435N error at run-time.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7, Fixpak 8                         * 
****************************************************************

Instead of using a CHAR(5) value for the SQLSTATE variable, 
create a new type outside of the routine that has two elements 
(a CHAR(5) value to use for the SQLSTATE variable, and a CHAR(1) 
value to use as a fence character) like this: 
 
CREATE TYPE state_holder AS ROW (state CHAR(5), fence CHAR(1)); 
 
Then inside the routine declare a new variable by using that new 
type that was created.  We then give the STATE field a copy of 
the SQLSTATE output variable and we give the FENCE field an 
invalid SQLSTATE character (ie, outside the range 0-9,A-Z) as 
follows: 
 
BEGIN 
             DECLARE HOLDER state_holder; 
             DECLARE SQLSTATE_OUT CHAR(5); 
 
             ... 
 
             -- Before the signal, copy SQLSTATE_OUT to the 
holder, and fence it off 
             -- with a known invalid SQLSTATE character: 
             set holder = (SQLSTATE_OUT, '#'); 
             SIGNAL SQLSTATE holder.state SET MESSAGE_TEXT = 
...; 
 
 
With the SQLSTATE output variable now fenced off with an invalid 
character, DB2 will only read up to the 5 characters for the 
SQLSTATE and not fail if the 6th character was also a valid 
character, thus having DB2 think the total size of the SQLSTATE 
is larger than it should be.

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 9.7, Fixpak 8

see Local Fix.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

21.08.2012
15.04.2013
15.04.2013

9.7.