IBM Support

IC86035: INTERMITTENT -435 WHEN SIGNALLING SQLSTATE IN A SQL ROUTINE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

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

Local fix

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

Problem summary

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

Problem conclusion

  • First fixed in DB2 Version 9.7, Fixpak 8
    

Temporary fix

  • see Local Fix.
    

Comments

APAR Information

  • APAR number

    IC86035

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-08-21

  • Closed date

    2013-04-15

  • Last modified date

    2013-04-15

  • APAR is sysrouted FROM one or more of the following:

    LI76946

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC86035

Modified date: 15 April 2013