DB2 FOR LUW / DB2FORLUW / A10 - DB2

When trying to reference a column returned from a associative 
array of records 
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 has to 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 
 
This APAR avoids the error by allowing the name of the column to 
be used in SQL statements.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v10.1 Fixpack 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.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in DB2 v10.1 Fixpack 3

not known / see Local fix

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

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

11.01.2013
07.10.2013
07.10.2013