IBM Support

IT09390: WRONG RESULTS MAY HAPPEN WHEN ACCESS PLAN CONTAINS INDEX SCAN ON THE OUTER LEG OF THE HASH JOIN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • It is possible to get wrong results when access plan contains
    index scan on the outer leg of the hash join and filtering is
    applied to the scan results.
    Example below will demonstrate the issue.
    SQL using "WITH RR" will return correct results.
    SQL without "WITH RR" will return incorrect results.
    select count(*) from (select rowid rid from test1 where
    tabschema = 'MYSCHEMA') a , (select rowid rid from test1 where
    tabname = 'TEST1') b where a.rid = b.rid
    
    1
    -----------
    38
    
     1 record(s) selected.
    
    select count(*) from (select rowid rid from test1 where
    tabschema = 'MYSCHEMA') a , (select rowid rid from test1 where
    tabname = 'TEST1') b where a.rid = b.rid WITH RR
    
    1
    -----------
    20020
    1 record(s) selected.
    
    Access plans will look the same and will have index scan on the
    outer leg of the hash join.
    In the problem case we will not qualify all rows on the outer of
    hash join due to problems with how we apply filtering.
    

Local fix

  • db2set DB2_TCG_DEFAULT_OPTIONS="set disable_pushdown on".
    restart the instance
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * It is possible to get wrong results when access plan         *
    * contains                                                     *
    * index scan on the outer leg of the hash join and filtering   *
    * is                                                           *
    * applied to the scan results.                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * db2set DB2_TCG_DEFAULT_OPTIONS="set disable_pushdown on".    *
    * restart the instance                                         *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IT09390

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-06-10

  • Closed date

    2017-03-01

  • Last modified date

    2017-03-01

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

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

Fix information

Applicable component levels

  • RA10 PSN

       UP

  • RA10 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
01 March 2017