IBM Support

IC98814: ENHANCE QUERY PERFORMANCE THROUGH PUSHDOWN OF RANGE JOINS THROUGH UNION ALL VIEW(S)

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • The DB2 query rewrite phase of optimization might be able to
    improve the performance of an SQL statement compiled using
    optimization class 5 if it includes a join with a view that is
    defined with a union all operation, such as
    
    CREATE VIEW view1
    AS
    SELECT ... FROM tabA ...
    UNION ALL
    SELECT ... FROM tabB ...
    
    and the statement references multiple columns from the view in a
    range join with a base table, such as
    
    SELECT
    ...
    FROM view1, tabC ...
    WHERE tabC.x between view1.y and view1.z ...
    
    You can identify this scenario by capturing an EXPLAIN of the
    statement and verifying in the optimized statement section or
    access plan that the join is performed after the UNION
    operation.
    
    This enhancement is only available under registry variable
    control. To enable it, issue:
    db2set
    DB2_UNION_OPTIMIZATION="[<other-values>,]ENABLE_UA_RANGE_JPPD=ON
    [,<other-values>]"
    and restart the database manager for settings to take effect.
    

Local fix

  • Use optimization class 7 or higher.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to v9.7 FP10 or higher                               *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC98814

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-01-17

  • Closed date

    2014-12-09

  • Last modified date

    2014-12-09

  • 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

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC98814

Modified date: 09 December 2014