DB2 FOR LUW / DB2FORLUW / B10 - DB2

A query over nicknames containing FETCH FIRST n ROWS ONLY clause 
may return incorrect results when the following conditions are 
met: 
 
1. The query contains a FETCH FIRST n ROWS ONLY clause. 
2. The query contains a join that can be processed in the 
remote data source. 
3. The query join predicate can only be processed locally or 
the query contains other predicates that can only be processed 
locally and in both cases cannot be pushed down into the remote 
data source. 
 
A query containing column organized tables with similar criteria 
may also return incorrect results when the query also contains 
an ORDER BY clause. 
 
Example: 
 
*connect to remote database* 
 
CREATE TABLE remote.t1 (c1 int); 
CREATE TABLE remote.t2 (c1 int, c2 varchar(255)); 
 
insert into remote.t1 values (1); 
insert into remote.t2 values (1, 'bar'),(1, 'foo_bar'); 
 
*connect to local database* 
CREATE NICKNAME local.t1 FOR myserver.remote.t1; 
CREATE NICKNAME local.t2 FOR myserver.remote.t2; 
 
SET SCHEMA LOCAL; 
 
SELECT t2.c1 FROM t1 JOIN t2 ON t1.c1 = t2.c1 where INSTR(t2.c2, 
'foo_', 1) > 0 FETCH FIRST 1 ROW ONLY; 
 
Expected Results: 
 
C1 
----------- 
          1 
 
  1 record(s) selected. 
 
Actual Results: 
 
C1 
----------- 
 
  0 record(s) selected. 
 
 
An indication of this problem can be found within the DB2 access 
plan 
details. 
 
For example, the access plan for the query above has a FILTER 
operation 
above a SHIP operation, and the SHIP operation is estimated to 
return 1 
row. This may indicate that the remote data source is processing 
the 
FETCH FIRST n ROWS ONLY clause and the local database is 
processing 
the predicate that can only be processed locally: 
 
                Rows 
               RETURN 
               (   1) 
                Cost 
                 I/O 
                 | 
              0.333333 
               FILTER 
               (   2) 
               596.263 
                 57 
                 | 
                  1 
               SHIP 
               (   3) 
               596.228 
                 57 
          /------+------\ 
       1000              1000 
 NICKNM: LOCAL    NICKNM: LOCAL 
        T2                T1 
        Q1                Q2 
 
For column organized tables, the access plan above will 
use a CTQ operator instead of a SHIP operator.

**************************************************************** 
* USERS AFFECTED:                                              * 
* NICKNAMES OR COLUMN ORGANIZED TABLES                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please, See Error Description and install this fix.          * 
****************************************************************

Either of these workarounds will avoid the incorrect results for 
nicknames: 
 
1. Disable the FETCH FIRST n ROWS ONLY clause from being 
processed by the remote server: 
alter server myserver options(add db2_fetch_n_rows 'N') 
 
2. If the predicate contains a function, create a function 
mapping object so that the predicate can be processed by the 
remote data source. In the example above, a function mapping 
object for the SYSIBM.INSTR function will allow the predicate to 
be processed by the remote data source.

Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

Please, See Error Description and install this fix.

not known / see Local fix

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

15.01.2018
16.03.2018
16.03.2018