DB2 FOR LUW / DB2FORLUW / A10 - DB2

If your PL/SQL application opens a cursor using dynamic SQL 
text, the cursor will be closed after a COMMIT operation.  This 
is incorrect for PL/SQL code, where cursors should remain open 
after a commit.  In other words, they should exhibit the same 
behaviour as an equivalent SQL PL cursor declared with the WITH 
HOLD attribute. 
 
The following test script demonstrates the behaviour.  The 
CLOSE statement should complete successfully; instead, it fails 
with error SQL0501N: 
 
SQL0501N  The cursor specified in a FETCH statement or CLOSE 
statement is not open or a cursor variable in a cursor scalar 
function reference is not open. 
 
 
/* db2set DB2_COMPATIBILITY_VECTOR=ORA */ 
 
set sqlcompat plsql; 
set serveroutput on; 
 
declare 
  dummy number(10); 
 
  type r_cursor is REF CURSOR; 
  dyn_cursor r_cursor; 
 
  sql_text varchar(2000); 
 
begin 
  -- Open a dynamic cursor 
  sql_text := 'SELECT 1 FROM DUAL' ; 
 
  open dyn_cursor for sql_text; 
  dbms_output.put_line('Dynamic cursor opened.'); 
 
  loop 
    fetch dyn_cursor into dummy; 
    exit when dyn_cursor%NOTFOUND; 
 
    commit; 
 
    dbms_output.put_line('Value fetched from dynamic cursor.'); 
  end loop; 
 
  -- The cursor should still be open after the COMMIT. 
  close dyn_cursor; 
  dbms_output.put_line('Dynamic cursor closed.'); 
end; 
/ 
 
connect reset; 
terminate;

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 Fix Pack 2.                      * 
****************************************************************

If a dynamic cursor is required and it is necessary to keep the 
cursor open through a commit operation, consider writing a 
helper routine in SQL PL (instead of PL/SQL) to dynamically 
open a cursor variable WITH HOLD.

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

This problem is fixed in DB2 Version 10.1 Fix Pack 2.

not known / see Local fix

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

22.10.2012
18.01.2013
18.01.2013