|
Problem IC62421
|
Status: Closed |
AN UNEXPECTED FAILURE MAY OCCUR DURING ASSIGNMENT TO AN ELEMENT IN AN ARRAY OF ROWS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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% |
| Problem Summary: |
****************************************************************
* 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. *
**************************************************************** |
| Local Fix: |
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% |
| available fix packs: |
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
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.08.2009
21.12.2009
21.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
