IBM Support

IC68629: OPTIMIZE UNION ALL JOIN PUSHDOWN AND BRANCH ELIMINATION

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as suggestion for future release.

Error description

  • DB2 Rewrite will optimize the performance of union all views by
    performing pushdown of local predicates of a base table which
    joins the union all view if the join is considered to be non
    expanding.
    
    For the rewrite to take place, all the following conditions must
    be true:
    
    1. If we have join predicates from base table that cover all the
    columns in a unique index of Base Table, and,
    2. those columns, via equality classes in various levels, can be
    mapped to all the branches of the UA that also ranges over the
    same base table (BT)
    3. There must be no recursion or correlation in the joins
    related to the rewrite.
    4. The union all must not have a Update, Delete, Insert
    consumer, fetch n rows or be part of a subquery.
    5. The joins with the base table must not be correlated
    6. There must be no side-effects, external action or scratchpad
    below the UNION ALL
    
    Result:
    Local predicates on base table are pushed down to all the base
    tables under Union All view.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * DB2 Rewrite will optimize the performance of union all views *
    * by                                                           *
    * performing pushdown of local predicates of a base table      *
    * which                                                        *
    * joins the union all view if the join is considered to be non *
    *                                                              *
    * expanding.                                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v970 fixpack 4                                *
    ****************************************************************
    

Problem conclusion

  • The enhancement is available in DB2 v970 fixpack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC68629

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED SUG

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-05-13

  • Closed date

    2011-04-29

  • Last modified date

    2011-04-29

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

    IC68362

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

Fix information

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU029","label":"Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7"}]

Document Information

Modified date:
10 September 2020