DB2 FOR LUW / DB2FORLUW / 970 - DB2

Insensitive cursors are meant to have all result sets computed 
at cursor open time and thus be insensitive to any changes to 
the content of the table after the opening of the cursor. 
 
 
Note that the Sybase skin implicitly uses INSENSITIVE cursors 
when the cursor is specified as 'with return' 
 
However this is not the case when the operation after the open 
of the cursor is a TRUNCATE.   Consider the following example: 
 
create global temporary table x.t1 (x int) 
insert into x.t1 values 1,2,3 
 
create procedure p1 () 
begin 
 declare c1 insensitive cursor with hold with return to client 
      for select x from x.t1; 
 open c1; 
 commit work; 
 truncate table x.t1; 
end 
 
 
call p1 () 
 
 
In this case rather than return the rows, the call p1() will 
return a -910 sqlcode when attempting to fetch from the result 
set.

**************************************************************** 
* USERS AFFECTED:                                              * 
* use of INSENSITIVE cursors and truncate table                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* result set for cursor not returned                           * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to v97fp3                                            * 
****************************************************************

Use a delete from table rather than a truncate

DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 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
 

Fixed in v97fp3

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC69647 
follow-up : 

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

28.06.2010
19.12.2011
19.12.2011

9.7.FP3