DB2 FOR LUW / DB2FORLUW / A50 - DB2

When fetching a cursor to a global variable, module variable, or 
PL/SQL package variable, a warning with SQLSTATE '01503' may be 
incorrectly returned, when the variable is an anchored row type 
variable. 
 
The following script demonstrates the problem: 
 
$ cat << EOF > repro.sql 
CREATE OR REPLACE MODULE cursor_rowtype_m@ 
ALTER MODULE cursor_rowtype_m ADD VARIABLE p_inpara SMALLINT@ 
ALTER MODULE cursor_rowtype_m ADD VARIABLE cur_staff CURSOR 
CONSTANT 
        (CURSOR WITH HOLD FOR 
                SELECT id,name FROM staff 
                WHERE dept=p_inpara 
                )@ 
ALTER MODULE cursor_rowtype_m ADD VARIABLE rec_cur_staff ANCHOR 
ROW OF cur_staff@ 
ALTER MODULE cursor_rowtype_m PUBLISH PROCEDURE procCursorDef(IN 
inpara SMALLINT,OUT outpara VARCHAR(10)) 
READS SQL DATA 
LANGUAGE SQL 
BEGIN 
 
DECLARE sqlcode INTEGER DEFAULT 0; 
DECLARE sqlcode2 INTEGER; 
  DECLARE sqlstate CHAR(5) DEFAULT '00000'; 
  DECLARE sqlstate2  CHAR(5); 
  DECLARE sqlerrm VARCHAR(32672); 
  DECLARE token VARCHAR(1020); 
  DECLARE msgid VARCHAR(12); 
  DECLARE msgid_sep INT; 
 
        DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING 
      BEGIN 
 
      GET DIAGNOSTICS EXCEPTION 1 sqlerrm = message_text; 
        GET DIAGNOSTICS EXCEPTION 1 token = db2_token_string; 
        SET (sqlstate2, sqlcode2) = (sqlstate, sqlcode); 
 
        CALL dbms_output.put_line('SQLCODE:' || sqlcode2 || ' 
SQLSTATE:' || sqlstate2 || ' MSG:' || sqlerrm); 
        CLOSE cur_staff; 
      END; 
 
        SET p_inpara = inpara; 
 
        OPEN cur_staff; 
        FETCH cur_staff INTO rec_cur_staff; 
                CALL dbms_output.put_line('ID:' || 
TO_CHAR(rec_cur_staff.id) || ' @ NAME:' || rec_cur_staff.name); 
 
        CLOSE cur_staff; 
END 
@ 
EOF 
 
$ db2 connect to sample 
$ db2 -td@ -vf repro.sql 
$ db2 set serveroutput on 
$ db2 -v "CALL cursor_rowtype_m.procCursorDef(10,?)" 
CALL cursor_rowtype_m.procCursorDef(10,?) 
 
  Value of output parameters 
  -------------------------- 
  Parameter Name  : OUTPARA 
  Parameter Value : - 
 
  Return Status = 0 
 
SQLCODE:0 SQLSTATE:01503 MSG:

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB version 10.5 fixpack 4.                   * 
****************************************************************

Declare the global, module, or package variable using an 
explicit row type, instead of an anchored type. 
 
In this case, add 
ALTER MODULE cursor_rowtype_m3 ADD TYPE type_cur_staff AS ROW 
ANCHOR TO ROW OF cur_staff@ 
before 
ALTER MODULE cursor_rowtype_m3 ADD VARIABLE rec_cur_staff 
type_cur_staff@

DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 10.5 Fix Pack 4.

not known / see Local fix

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

06.03.2014
22.09.2014
22.09.2014