IBM Support

IT23741: A QUERY OVER NICKNAMES OR COLUMN ORGANIZED TABLES CONTAINING FETCH FIRST N ROWS ONLY CLAUSE MAY RETURN INCORRECT RESULTS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • 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.
    

Local 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.
    

Problem summary

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

Problem conclusion

  • Please, See Error Description and install this fix.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT23741

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-01-15

  • Closed date

    2018-03-16

  • Last modified date

    2018-03-16

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

    IT23267

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSY

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 March 2018