DB2 FOR LUW / DB2FORLUW / A10 - DB2

A predicate <> with empty string and varchar/ vargraphic column 
can result in empty result of the query, e.g. 
 
select * from n1 where varchar_col <> '' 
 
when predicate is pushed down, the remote statement will be 
like: 
 
select * from <remote_schema>.<remote_tab_name> where 
varchar_col <> RPAD('', <length of varchar_col>) 
 
If the remote data source is Oracle or UDB in 
ora-varchar2-compat mode, the '' will be treated as NULL in 
remote database, and no record will match the predicate, 
resulting in empty result set. 
 
As requirement from one customer, we block pushdown of a <> 
predicate, given that the following condition are met: 
       1. one of the argument of <> is of lenght 0, and constant 
       2. federation db is in non ora string compat mode 
       3. remote DB is Oracle or UDB in ora mode (varchar2_mode 
server option is Y) 
       4. the other argument of <> is of varchar/vargraphic type

**************************************************************** 
* USERS AFFECTED:                                              * 
* all platforms                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to IFS V10.1                                         * 
****************************************************************

change the predicate to varchar_col is not null

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 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
 

v10.1

See Error Description

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

28.11.2012
19.12.2012
19.12.2012