DB2 FOR LUW / DB2FORLUW / 970 - DB2

When an SQL routine executes a statement that completes with 
SQLCODE -952 (Processing was cancelled due to an interrupt), 
DB2 incorrectly treats the -952 error as a regular exception 
condition.  If your SQL routine contains an appropriate general 
condition handler for SQLEXCEPTION, or a specific condition 
handler for SQLSTATE '57014', the condition handler will 
activate.  It is therefore possible for your SQL routine to 
continue execution after the interrupt has occurred. 
Subsequent SQL statements may fail with SQLCODE -20139: 
 
SQL20139N  SQL statements may not be issued in routine 
"<routine-name>" (specific name "<specific-name>") because of a 
previous statement failed or was interrupted. 
 
The intended behaviour is that DB2 will interrupt the currently 
executing SQL routine when SQLCODE -431 or SQLCODE -952 is 
detected.  These errors will be treated as unhandled exception 
conditions, even if an appropriate condition handler exists. 
The exception condition for the interrupt will be propagated to 
the caller. 
 
Example: 
 
create procedure test 
language sql 
begin 
  declare continue handler for sqlexception 
  begin 
    insert into errlog values sqlstate, sqlcode; 
  end; 
 
  -- Let's say that this called routine fails with SQLCODE -952: 
  call some_other_routine(); 
 
  insert into T1 values 42; 
end 
 
Here we have a simple SQL procedure that calls another 
procedure.  If the nested procedure call is interrupted with 
SQLCODE -952, instead of terminating routine TEST, DB2 will 
incorrectly proceed to dispatch the continue handler for the 
exception condition.  In this case, it will attempt an INSERT 
from the handler, which will fail with SQLCODE -20139; we will 
never reach the INSERT into table T1. 
 
This is incorrect. 
 
What should happen here is that immediately after the call to 
SOME_OTHER_ROUTINE, procedure TEST should terminate with 
SQLCODE -952; the condition handler should not be executed.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of version 9.7 on Linux, Unix and Windows          * 
* platforms.                                                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 LUW Version 9.7 Fix Pack 7 or higher levels.   * 
****************************************************************

When coding condition handlers for SQLEXCEPTION, be mindful 
that this may possibly include SQLCODE -952, until a fix is 
available.  Because condition handlers for specific SQLSTATEs 
always take precedence over general condition handlers 
(SQLWARNING, SQLEXCEPTION, NOT FOUND), you can add a specific 
condition handler for SQLSTATE '57014', which corresponds to 
SQLCODE -952.  Because this interrupt will not be catchable by 
condition handlers in the future, your handler should do 
nothing but propagate SQLSTATE '57014' to the caller.  We 
recommend an EXIT handler in the outermost scope of your 
routine.  For example: 
 
create procedure test 
language sql 
begin 
  declare exit handler for sqlstate '57014' 
    resignal; -- propagate SQLSTATE '57014' to the caller 
 
  -- Your statements here... 
end

DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 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 LUW Version 9.7 Fix Pack 7.

When coding condition handlers for SQLEXCEPTION, be mindful 
that this may possibly include SQLCODE -952, until a fix is 
available.  Because condition handlers for specific SQLSTATEs 
always take precedence over general condition handlers 
(SQLWARNING, SQLEXCEPTION, NOT FOUND), you can add a specific 
condition handler for SQLSTATE '57014', which corresponds to 
SQLCODE -952.  Because this interrupt will not be catchable by 
condition handlers in the future, your handler should do 
nothing but propagate SQLSTATE '57014' to the caller.  We 
recommend an EXIT handler in the outermost scope of your 
routine.  For example: 
 
create procedure test 
language sql 
begin 
  declare exit handler for sqlstate '57014' 
    resignal; -- propagate SQLSTATE '57014' to the caller 
 
  -- Your statements here... 
end

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

11.06.2012
20.10.2012
20.10.2012

9.7.FP7