DB2 FOR LUW / DB2FORLUW / A50 - DB2

When trying to reference a column returned from an associative 
array of records in Oracle compatibility mode, the following 
error may occur: 
 
select * from table (p1.mytabfunc()) where f1 > 10 
SQL0206N  "F1" is not valid in the context where it is used. 
SQLSTATE=42703 
 
This occurs because the columns in the function are being 
returned as system generated names such as 1, 2, 3 instead of 
the original name such as "F1". 
 
The function must return a array of records, as in this example: 
 
create or replace package p1 
as 
 type myrec is record ( 
   f1 number(5), 
   f2 varchar(10) 
 ); 
 type mycoll is table of myrec index by binary_integer; 
 
 function mytabfunc return mycoll; 
end; 
 
describe select * from table (p1.mytabfunc()) 
 
 Column Information 
 
 Number of columns: 3 
 
 SQL type        Type length  Column name   Name length 
 --------------  -----------  ------------  ----------- 
 497   INTEGER             4  1                       1 
 485   DECIMAL          5, 0  2                       1 
 449   VARCHAR            10  3                       1

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 LUW                                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 Fix Pack 3.                      * 
****************************************************************

As a workaround, the SQL statement can be re-written to rename 
the columns using the "AS" clause such as below: 
 
select * from table (p1.mytabfunc()) as t (idx, field1, field2) 
where field1 > 1

DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
 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
 

This problem is first fixed in DB2 Version 10.5 Fix Pack 3.

not known / see Local fix

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

27.08.2013
27.02.2014
27.02.2014