IBM Support

IT00933: 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 program error.

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:                                              *
    * Database Partitioning Feature                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.5.0.4.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 10.5.0.4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT00933

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-04-09

  • Closed date

    2014-04-09

  • Last modified date

    2015-04-07

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

    IC98114

  • 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

  • RA50 PSN

       UP

[{"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.5","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
13 January 2022