IBM Support

IC98114: IN DB2 DPF ENVIRONMENTS ONLY, A SPECIFIC TYPE OF QUERY AND RESULTING ACCESS PLAN MIGHT RETURN WRONG RESULTS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • In DB2 DPF environments only, a specific type of query and
    resulting access might return wrong results.
    
    The following conditions will cause this issue to occur:
    - The query must have joins with join predicates using an
    explicit expression or an implicit data type conversion.  An
    example of an explicit expression is "coalesce(col1,col2) =
    coljoin".
    - The access plan must have a UNION with a Directed TQ  (DTQ)
    above it.  This is most likely to happen in a query that does a
    full outer join.
    - The access plan must also have a Hash Join (HSJOIN) chosen
    with the expression above the UNION.
    
    You can review access plans through either the explain utility
    and the db2exfmt command or using Optim Visual Explain.
    
    The problem scenario can only happen if you have all of these in
    the plan:
    - UNION
    - HSJN above UNION with the JOIN predicate using the result of
    expression (for example: COALESCE(Q9.$C3, Q9.$C1) =
    Q10.column_name))
    - CMPEXP used in HSJN is above the UNION
    - DTQ (Directed TQ) is above the same UNION and above CMPEXP is
    used to direct results.
    
    The following is a simplified example of this scenario.  There
    is a Compute Expression (CMPEXP) in the plan below the HSJN but
    above the UNION:
    
                          HSJOIN
                        /---+----\
                     DTQ          TBSCAN
                      |             |
                     PIPE    TABLE: TAB_NAME
                      |
                    CMPEXP
                      |
                    UNION
         /------------+------------\
      HSJN                          TA
    

Local fix

  • To avoid hitting this APAR, execute the following and then
    recycle the instance:
    db2set DB2_TCG_DEFAULT_OPTIONS="set multi_stream_pushdown off"
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2LUW v10.1                                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * In DB2 DPF environments only, a specific type of query and   *
    * resulting access might return wrong results.                 *
    *                                                              *
    * The following conditions will cause this issue to occur:     *
    * - The query must have joins with join predicates using an    *
    * explicit expression or an implicit data type conversion.  An *
    * example of an explicit expression is "coalesce(col1,col2) =  *
    * coljoin".                                                    *
    * - The access plan must have a UNION with a Directed TQ       *
    * (DTQ)                                                        *
    * above it.  This is most likely to happen in a query that     *
    * does a                                                       *
    * full outer join.                                             *
    * - The access plan must also have a Hash Join (HSJOIN) chosen *
    * with the expression above the UNION.                         *
    *                                                              *
    * You can review access plans through either the explain       *
    * utility                                                      *
    * and the db2exfmt command or using Optim Visual Explain.      *
    *                                                              *
    * The problem scenario can only happen if you have all of      *
    * these in                                                     *
    * the plan:                                                    *
    * - UNION                                                      *
    * - HSJN above UNION with the JOIN predicate using the result  *
    * of                                                           *
    * expression (for example: COALESCE(Q9.$C3, Q9.$C1) =          *
    * Q10.column_name))                                            *
    * - CMPEXP used in HSJN is above the UNION                     *
    * - DTQ (Directed TQ) is above the same UNION and above CMPEXP *
    * is                                                           *
    * used to direct results.                                      *
    *                                                              *
    * The following is a simplified example of this scenario.      *
    * There                                                        *
    * is a Compute Expression (CMPEXP) in the plan below the HSJN  *
    * but                                                          *
    * above the UNION:                                             *
    *                                                              *
    *                       HSJOIN                                 *
    *                     /---+----\                               *
    *                  DTQ          TBSCAN                         *
    *                   |             |                            *
    *                  PIPE    TABLE: TAB_NAME                     *
    *                   |                                          *
    *                 CMPEXP                                       *
    *                   |                                          *
    *                 UNION                                        *
    *      /------------+------------\                             *
    *   HSJN                          TA                           *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v10.1 FP4                                     *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC98114

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-12-04

  • Closed date

    2014-03-21

  • Last modified date

    2014-05-26

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

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

    IT00933

Fix information

Applicable component levels

  • RA10 PSY

       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:
23 September 2021