SQLSTATE and SQLCODE are not changing when a SQL routine includes a NOT FOUND handler

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

SQLSTATE and SQLCODE are still zero when tested by the SQL Routine after being processed by an Empty Handler.


Cause

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
BEGIN
-- Empty Handler, does nothing when data is not found.
END;

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;

Rate this page:

(0 users)Average rating

Document information


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

Translate my page

Machine Translation

Content navigation