DB2 FOR LUW / DB2FORLUW / A50 - 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.

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

****************************************************************
* USERS AFFECTED:                                              *
* Federated Environment                                        *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Please, see error  Description and install this fix.         *
****************************************************************

Either of these workarounds will avoid the incorrect results:

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.

not known / see Local fix

forerunner  : 
follow-up : IT23741 

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

24.11.2017
16.07.2018
16.07.2018