DB2 FOR LUW / DB2FORLUW / A50 - DB2

DB2 may return wrong results when all the following conditions 
are met: 
-The database has oracle compatibility 
(DB2_COMPATIBILITY_VECTOR=ORA) mode enabled. 
-The SELECT query being issued contains a relational operator 
predicate with SUBSTR. 
-The string argument to SUBSTR is from a table that has an index 
on that column. 
-The other operand of the relational operator is a literal. 
 
This forces the optimizer to perform an index scan using only 
the 
start/stop predicates. The index scan LOLEPOP does not contain 
the SUBSTR predicate, only the start/stop preds. 
 
Example: 
 
create table t (v VARCHAR(128)); 
create index i on t(v); 
insert into t values 'PT1', 'PT10'; 
 
select v from t where substr(v, 1, 20) = cast('PT1' as 
VARCHAR(20)); 
 
The above query returns two rows ('PT1', 'PT10') when we should 
only 
be returning one ('PT1'). 
 
The expected result (one row) is returned by the following: 
 
select v from t where v = cast('PT1' as VARCHAR(20));

**************************************************************** 
* USERS AFFECTED:                                              * 
* Compatibility ORA users                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* See Error Description. Please, install fix.                  * 
****************************************************************

Workaround: 
 
Replace the literal with a table column or variable. 
Continuing with the example above: 
 
create variable myvar char(3) default 'PT1'; 
select * from test where substr(v, 1, 20) = myvar;

See Error Description. Please, install fix.

not known / see Local fix

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

03.08.2017
27.09.2017
12.10.2017