SQLSTATE and SQLCODE are not changing when a SQL routine includes a NOT FOUND handler
Starting with version 9.5, DB2 will reset the SQLCODE and SQLSTATE after the first line of a handler is executed. This behavior also applies to empty handlers. . This may cause routines that process SQLCODE and SQLSTATE after the execution of the first line of a handler or an empty handler to fail. DB2 is working as designed and applications should save the SQLCODE AND SQLSTATE values in the first line of the handler.
SQLSTATE and SQLCODE are still zero when tested by the SQL Routine after being processed by an Empty Handler.
If a handler has been declared for a SQL routine, the SQLCODE and SQLSTATE are reset after the first line of the handler is executed. If the version of DB2 LUW is 9.5 or greater, the SQLCODE and SQLSTATE will be reset even if the handler only contains comments (this is referred to as an empty handler). Below is an example of an empty handler.
DECLARE CONTINUE HANDLER FOR NOT FOUND
-- Empty Handler, does nothing when data is not found.
Resetting the SQLCODE and SQLSTATE may cause some SQL routines which depend on the SQLCODE or SQLSTATE for their next actions to fail or to loop infinitely in cases where a non-zero SQLCODE or SQLSTATE terminates a loop,
Diagnosing the problem
Check that SQLCODE and SQLSTATE are used by the SQL routine and that there is a handler for the expected condition. Also check to see if the SQLCODE and SQLSTATE are being saved to variables in the first line of the Handler. If the SQL routine uses a handler and the SQLCODE and SQLSTATE are not saved to other variables in the first line of the handler, then you have likely encountered this issue.
Resolving the problem
DB2 LUW is working as designed when it resets the SQLCODE and SQLSTATE. SQL routines that process SQLCODE and SQLSTATE after the first line of the handler should save them to other variables in the first line of the handler. Below is an example of a line of code that will save the SQLCODE and SQLSTATE to a variable.
values(SQLSTATE, SQLCODE) into v_sqlstate, v_sqlcode;
More support for:
DB2 for Linux, UNIX and Windows
Application Programming - SQL Routines (SQL stored procedures)
Software version: 9.5, 9.7
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Software edition: Advanced Enterprise Server, Enterprise Server, Express, Personal, Workgroup Server
Reference #: 1502815
Modified date: 2012-05-30