DB2 FOR LUW / DB2FORLUW / 970 - DB2

An SQL procedure with one or more array of row parameters may 
cause an SQL0901N error.  The following set of preconditions are 
required: 
 
- an array of row parameter appears in the list of parameters 
before another array parameter.  For example, if procedure PROC1 
has three parameters (P1, P2, P3), this problem can occur if P1 
or P2 is an array with a row element type and P2 or P3 is an 
array with any element data type. 
- one or more fields from the array of row parameter is 
explicitly referenced in the procedure body. 
 
This is known to lead to an SQL0901N error with the text, "bad 
element size".  You may encounter other, unexpected 
consequences, including other SQL0901N errors, abnormal 
program termination, or incorrect data in the array 
parameters. 
 
For example, calling this procedure will return an SQL0901N 
error in v9.7 fix pack 4. 
 
create type row1 as row (f1 int, f2 varchar(128)) 
create type arr1 as row1 array[15] 
create type arr2 as char(10) array[15] 
 
create procedure proc1 (in p1 arr1, in p2 arr2) 
begin 
 set p1[1].f1 = 1; 
 set p1[1].f2 = 'a'; 
 set p2[1] = 2; 
end

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of DB2 LUW version 9.7 up to and including fix     * 
* pack 4, as well as SQL procedures migrated to later fix      * 
* packs.                                                       * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* An SQL procedure with one or more array of row parameters    * 
* may cause an SQL0901N error with the error message text "bad * 
* element type".  This problem can occur when a procedure      * 
* contains more than one array parameter, and an array of row  * 
* parameter appears in the parameter list before other array   * 
* parameters.                                                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* If the procedure has one array of row parameter, it can be   * 
* moved to the end of the list of parameters. If version 9.7   * 
* fix pack 5 or later has been applied and the problem         * 
* persists, the procedure will need to be dropped and          * 
* recreated.  SQL procedures newly created on version 9.7 fix  * 
* pack 5 or later will not hit the error described in this     * 
* APAR.                                                        * 
****************************************************************

If the procedure has one array of row parameter, move it to the 
end of the list of parameters.  If version 9.7 Fix Pack 5 or 
later 
has been applied and the problem persists, drop and recreate 
the procedure. 
SQL procedures newly created on DB2 Version 9.7 Fix Pack 5 or 
 
later will not encounter the error described in this APAR. 
 
In the example provided in the ERROR DESCRIPTION, the order of 
the parameters can be reversed to avoid the SQL0901N error. 
 
create procedure proc1 (in p2 arr2, in p1 arr1) 
begin 
 set p1[1].f1 = 1; 
 set p1[1].f2 = 'a'; 
 set p2[1] = 2; 
end

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

See LOCAL FIX.

Fix release: DB2 LUW version 9.7 fix pack 5 
Fix commitment level: 999

forerunner  : APAR is sysrouted TO one or more of the following: IC76838 
follow-up : 

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

10.05.2011
25.05.2011
25.05.2011

9.7.