IBM Support

IC83652: GIVE HIGHER PRIORITY TO GROUP-BY MQT AND STATISTICAL VIEW IN OPTIMIZER PLAN COSTING DECISION

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When compiling a query, optimizer can generate multiple access
    plans that use different compilation of MQTs and use statistical
    view to adjust the cardinality of matched query join operators.
    Replicated MQT is commonly used in DPF and it can be enforced by
    either registry control
    "DB2_EXTENDED_OPTIMIZATION=FORCE_REPLICATED_MQT_MERGEA" or
    optimizer guideline "<MQTENFORCE TYPE='REPLICATED'/>" . A
    replicated MQT must have no aggregation. MQT having GROUP BY
    operation is called group-by MQT. When a base table has both
    replicated MQT and group-by MQT dependent on it, if replicated
    MQT is enforced and group-by MQT is not enforced, optimizer used
    to prune group-by MQT plan since it doesn't use the replicated
    MQT which is enforced. However the enforcement of replicated MQT
    should be in effect only at deciding among plans of base object
    access including base table and non-group-by MQTs and should not
    be applied to group-by MQT plan. This fix allows group-by MQT
    plan to be used if its plan costing is not more expensive than
    the base object access plan.
    
    This fix also increases of chance of using statistical view in
    optimizer planing. Query compiler attempts to substitute some
    subquery block with group-by MQT and adjust the cardinality of
    some other subquery block by using the matched statistical view.
    The group-by MQT used to reduce optimizer's use of statistical
    view. That is fixed by this APAR.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7 Fix Pack 7                               *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 V9.7 Fix Pack 7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC83652

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-05-22

  • Closed date

    2012-12-17

  • Last modified date

    2012-12-17

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

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

    IC89174

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC83652

Modified date: 17 December 2012