IBM Support

IT04520: INCORRECT RESULTS MIGHT BE PRODUCED IF HSJOIN HAS 2 NLJNs ON THE PROBE SIDE AND HSJN PROBE PUSH DOWN INTO THE SECOND NLJN DONE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • An SQL might produce incorrect results if all the following
    conditions are met:
    
    (a) The statement leverages a hash join (HSJOIN).
    (b) The plan has 2 nested loop joins (NLJOIN); one on the inner
    of another, and the other on the probe side of the HSJOIN.
    (c) An internal optimization on the probe side of the HSJN
    called a "push down" is implemented. The DB2 trace example below
    shows sqlrihsjnProbePD, which identifies this optimization
    taking place.
    
    We can use the EXPLAIN statement to see if the given SQL
    satisfies the conditions (a) and (b).
    
    The plan would have a graph like as below:
    
                      HSJOIN
                     /           \
              NLJOIN (x)
              /         \
                         NLJOIN  (y)
    
    Please note that this issue is likely to occur if NLJN(x) outer
    is not a real table, but a TABFNC: SYSIBM.GENROW
    
    Also, we can take db2trc to see if the HSJOIN is running into
    the condition (c), like as below:
    
    1. Turn on the trace.
    db2trc on -f trc.dmp
    
    2. Run the query.
    
    3. Turn off the trace, then format it.
    db2trc off
    db2trc flw trc.dmp trc.flw
    
    4. Search one sqlrihsjnProbePD that immediately returns with
    rc=1 inside sqlriFetch.
    grep -e sqlrihsjnProbePD -e sqlriFetch trc.flw
    59255   | | | sqlriFetch entry
    59417   | | | | | | | | | | | sqlrihsjnProbePD entry
    59420   | | | | | | | | | | | sqlrihsjnProbePD exit [rc = 1]
    50526   | | | sqlriFetch exit
    
    Note that the result might vary depending on the order and
    volume of the data.
    

Local fix

  • Users can disable all the push down by using the following
    setting.
    
    db2set DB2_TCG_DEFAULT_OPTIONS="set disable_pushdown on"
    
    Note that disabling push down can impact on query performance.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.1.0.5.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 10.1.0.5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT04520

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-09-21

  • Closed date

    2015-07-10

  • Last modified date

    2015-07-10

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

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

    IT05904

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

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

Document Information

Modified date:
25 September 2021