DB2 FOR LUW / DB2FORLUW / 970 - DB2

An unexpected SQL0901N or SQL1224N error may occur when 
assigning a value to an element in an array of rows.  In order 
for the error to occur, the following conditions must be met: 
- the assignment must be to a local variable or parameter in a 
compound SQL (compiled) statement.  The compound SQL (compiled) 
statement may be within an SQL procedure, SQL function, or 
trigger definition, or it may be a standalone statement, and 
- the source row must have one or more fields where the data 
type is not an exact match to the corresponding field in the 
target row. 
 
For example, in the following scenario the source field with 
type VARCHAR(20) does not match the target with type 
VARCHAR(12). 
 
CREATE TABLE TT (C1 INTEGER, C2 VARCHAR(12))% 
CREATE TYPE ROW1 AS ROW (F1 INTEGER, F2 VARCHAR(20))% 
CREATE TYPE ARRAY1 AS ANCHOR ROW TT ARRAY[10]% 
CREATE TYPE ARRAY2 AS ROW1 ARRAY[10]% 
 
BEGIN 
DECLARE VARR1 ARRAY1; 
DECLARE VARR2 ARRAY2; 
SET VARR2[1] = (3, 'abc'); 
SET VARR1[1] = VARR2[1]; 
END%

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 UDB systems on Linux, Unix, and Windows platforms at * 
* service levels on Version 9.7.0.                             * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* An unexpected SQL0901N or SQL1224N error may occur when      * 
*                                                              * 
* assigning a value to an element in an array of rows.  In     * 
* order                                                        * 
* for the error to occur, the following conditions must be     * 
* met:                                                         * 
* - the assignment must be to a local variable or parameter in * 
* a                                                            * 
* compound SQL (compiled) statement.  The compound SQL         * 
* (compiled)                                                   * 
* statement may be within an SQL procedure, SQL function, or   * 
*                                                              * 
* trigger definition, or it may be a standalone statement, and * 
*                                                              * 
* - the source row must have one or more fields where the data * 
*                                                              * 
* type is not an exact match to the corresponding field in the * 
*                                                              * 
* target row.                                                  * 
*                                                              * 
* For example, in the following scenario the source field with * 
*                                                              * 
* type VARCHAR(20) does not match the target with type         * 
* VARCHAR(12).                                                 * 
*                                                              * 
* CREATE TABLE TT (C1 INTEGER, C2 VARCHAR(12))%                * 
* CREATE TYPE ROW1 AS ROW (F1 INTEGER, F2 VARCHAR(20))%        * 
*                                                              * 
* CREATE TYPE ARRAY1 AS ANCHOR ROW TT ARRAY[10]%               * 
* CREATE TYPE ARRAY2 AS ROW1 ARRAY[10]%                        * 
*                                                              * 
* BEGIN                                                        * 
* DECLARE VARR1 ARRAY1;                                        * 
* DECLARE VARR2 ARRAY2;                                        * 
* SET VARR2[1] = (3, 'abc');                                   * 
* SET VARR1[1] = VARR2[1];                                     * 
* END%                                                         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to product version 9.7 fix pack 1.                   * 
****************************************************************

1) Declare a local variable with the same row data type as the 
target array element. 
2) Assign the source expression from the assignment that is 
causing the unexpected failure to the local row variable. 
3) Replace the source expression in the failing statement with 
the local row variable. 
 
Using the example from the error description: 
 
CREATE TABLE TT (C1 INTEGER, C2 VARCHAR(12))% 
CREATE TYPE ROW1 AS ROW (F1 INTEGER, F2 VARCHAR(20))% 
CREATE TYPE ARRAY1 AS ANCHOR ROW TT ARRAY[10]% 
CREATE TYPE ARRAY2 AS ROW1 ARRAY[10]% 
 
BEGIN 
DECLARE VARR1 ARRAY1; 
DECLARE VARR2 ARRAY2; 
DECLARE VROW1 ANCHOR ROW TT; 
SET VARR2[1] = (3, 'abc'); 
SET VROW1 = VARR2[1]; 
SET VARR1[1] = VROW1; 
END%

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 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 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
 

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

06.08.2009
21.12.2009
21.12.2009

9.7.FP1