DB2 FOR LUW / DB2FORLUW / 970 - DB2

In PL/SQL code, the default cursor attribute SQL%ROWCOUNT 
may not be updated after an EXECUTE IMMEDIATE statement. 
If your EXECUTE IMMEDIATE statement performs an UPDATE, 
DELETE, INSERT, or SELECT INTO, SQL%ROWCOUNT will not be 
updated unless your EXECUTE IMMEDIATE statement contains 
a BULK COLLECT clause. 
 
This can be demonstrated using the following test case: 
 
---- 
connect to test; 
 
set sqlcompat plsql; 
 
drop table T1; 
drop table T2; 
 
create table T1(C1 integer); 
 
insert into T1 values 
  2, 3, 5, 7, 11, 13, 17, 19, 23; 
 
create table T2(C1 integer); 
 
create or replace procedure test(r1 out bigint, r2 out bigint) 
as 
begin 
  insert into T2(C1) 
    select C1 from T1 where C1 < 6; -- 3 rows 
  r1 := SQL%ROWCOUNT; 
 
  execute immediate 
    'insert into T2(C1) select C1 from T1 where C1 < 3'; -- 1 
row 
  r2 := SQL%ROWCOUNT; 
end; 
/ 
 
call test(?, ?); 
 
connect reset; 
terminate; 
---- 
 
call test(?, ?) 
 
  Value of output parameters 
  -------------------------- 
  Parameter Name  : R1 
  Parameter Value : 3 
 
  Parameter Name  : R2 
  Parameter Value : 3 
 
  Return Status = 0 
 
The expected result in this case should be R1=3, but 
R2=1.

**************************************************************** 
* USERS AFFECTED:                                              * 
* none                                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* SQL%ROWCOUNT is not updated after a PL/SQL EXECUTE           * 
* IMMEDIATE statement; please see the Error Description        * 
* for more details.                                            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 5.                       * 
****************************************************************

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
 

This problem is fixed in DB2 Version 9.7 Fix Pack 5.

not known / see Local fix

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

15.06.2011
27.02.2012
27.02.2012

9.7.FP5