home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC99842 Status: Geschlossen

SQLSTATE 01503 WARNING RETURNED AFTER FETCH TO ANCHORED ROW TYPE GLOBAL
VARIABLE

Produkt:
DB2 FOR LUW / DB2FORLUW / A10 - DB2
Problembeschreibung:
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:
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB version 10.1 fixpack 4.                   * 
****************************************************************
Local-Fix:
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@
verfügbare FixPacks:
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in DB2 UDB Version 10.1 FixPack 4.
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC99902 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
05.03.2014
09.06.2014
09.06.2014
Problem behoben ab folgender Versionen (IBM BugInfos)
Problem behoben lt. FixList in der Version
10.1.0.4 FixList