JR45575: INCORRECT VARCHAR2 COMPARISON PREDICATE IS PUSHED DOWN TO ORACLE CAUSES -1822 ERROR

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • 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
    

Local fix

  • Declare the variable with length not larger than 4000.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * The user is running Oracle wrapper.                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Federation Server V97FP9.                         *
    ****************************************************************
    

Problem conclusion

  • The problem is firstly fixed Federation Server V97FP9.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR45575

  • Reported component name

    ORACLE WRAPPER

  • Reported component ID

    5724N9708

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-01-29

  • Closed date

    2013-12-17

  • Last modified date

    2013-12-17

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC91775

Fix information

  • Fixed component name

    ORACLE WRAPPER

  • Fixed component ID

    5724N9708

Applicable component levels

  • R970 PSY

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Federation Server
Data Sources and Wrappers - Oracle

Software version:

970

Reference #:

JR45575

Modified date:

2013-12-17

Translate my page

Machine Translation

Content navigation