DB2 FOR LUW / DB2FORLUW / A10 - DB2

In an Oracle database, 4000 is the maximum length allowed for 
VARCHAR2, so varchar2 comparison predicate with a variable 
whose length is larger than 4000, should not be pushed down to 
Oracle data source. 
 
But from v97fp6,  varchar2 comparison predicate may be 
incorrectly pushed down to Oracle data source ignoring the 
length of the variable that bound in the predicate, in turn 
causes SQL1822N error. 
 
The problem could be reproduced by following steps: 
 
1. Database is created and running with Oracle mode enabled. 
db2set  DB2_COMPATIBILITY_VECTOR=ORA 
 
2. Create nickname 
set passthru NET8_SERV; 
create table fedtest (c1 varchar2(20) primary key,c2 
varchar(20)); 
insert into fedtest values('A','A'); 
set passthru reset; 
 
create nickname TESTNICKNAME for NET8_SERV.TIGGER.fedtest; 
 
3. Create procedure, declaring a variable with length larger 
than 4001 
 
CREATE OR REPLACE PROCEDURE PROC2( 
 C1P in varchar2(4001)) -- length larger than 4000 
 IS 
 c2p varchar(10);    -- 
 BEGIN 
  SELECT C2 into c2p FROM TESTNICKNAME WHERE C1= C1P;     -- 
  DBMS_OUTPUT.PUT_LINE(c2p);       -- 
 END; 
 
 
create table t(c1 varchar(4000)); 
insert into t values(repeat('A',3999)); 
 
db2 => call PROC2(concat(( select c1 from t fetch first 1 rows 
only),'AA')); 
SQL1822N  Unexpected error code "1460" received from data source 
"NET8_SERV" 
Associated text and tokens are "ORA-01460: unimplemented or 
unreasonable conversion requeste".  SQLSTATE=560BD 
 
4. From explain output, we can see the predicate is pushed down: 
 
(    2) Server: NET8_SERV  (ORACLE 11g) 
        SQL Statement: 
 
             SELECT A0."C2" 
             FROM "TIGGER" ."FEDTEST" A0 
             WHERE (A0."C1" =:H0 )    ==> PUSHDOWN

**************************************************************** 
* USERS AFFECTED:                                              * 
* The user is running oracle wrapper.                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Federation Server v10.1 fp3.                      * 
****************************************************************

Declare the variable with length not larger than 4000.

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
 

The problem was firstly fixed on v10.1 fp3.

keiner bekannt / siehe Local-Fix

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

22.04.2013
03.10.2013
03.10.2013