IBM Support

IC72250: TUNING OF STATEMENTS FROM OPM EXTENDED INSIGHT ANALYSIS DASHBOARD DOES NOT USE THE ACCESS PLAN FROM THE LAST EXECUTION

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Starting with OQT (Optim Query Tuner) 2.2 FP2, when connected to
    a DB2 V97 server, OPM (Optim Performance Manager) users can tune
    a SQL statement based on the access plan from the package cache,
    which is the access plan of the last execution.  This is done
    via the OPM Extended Insight Analysis Dashboard.
    
    A problem exists where tuning is not based on the access plan in
    the package cache, but is instead based on a new access plan
    obtained from submitting the query to the DB2 query compiler.
    This new access plan may, or may not, be the access plan of the
    last execution.
    
    This problem can be detected by examining the details of the
    RETURN node of the Access Plan Graph obtained when Collect
    Actual Execution Values is not selected:
    - when the problem is not present and tuning is based on the
    access plan in the package cache, the RETURN node will show
    EXECUTID in the Attributes section
    - when the problem is present and tuning is not based on the
    access plan in the package cache, the RETURN node will not show
    EXECUTID in the Attributes section
    
    The problem was first introduced in DB2 Version 9.7 Fix Pack 3.
    

Local fix

  • No local fix is available to tune the query based on the access
    plan of the last execution in the package cache.  A work around
    is to tune from the access plan obtained from re-submitting the
    query to the DB2 query compiler.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Starting with OQT (Optim Query Tuner) 2.2 FP2, when          *
    * connected to                                                 *
    * a DB2 V97 server, OPM (Optim Performance Manager) users can  *
    * tune                                                         *
    * a SQL statement based on the access plan from the package    *
    * cache,                                                       *
    * which is the access plan of the last execution.  This is     *
    * done                                                         *
    * via the OPM Extended Insight Analysis Dashboard.             *
    *                                                              *
    *                                                              *
    *                                                              *
    * A problem exists where tuning is not based on the access     *
    * plan in                                                      *
    * the package cache, but is instead based on a new access plan *
    *                                                              *
    * obtained from submitting the query to the DB2 query          *
    * compiler.                                                    *
    * This new access plan may, or may not, be the access plan of  *
    * the last execution.                                          *
    *                                                              *
    *                                                              *
    *                                                              *
    * This problem can be detected by examining the details of the *
    *                                                              *
    * RETURN node of the Access Plan Graph obtained when Collect   *
    *                                                              *
    * Actual Execution Values is not selected:                     *
    *                                                              *
    * - when the problem is not present and tuning is based on the *
    *                                                              *
    * access plan in the package cache, the RETURN node will show  *
    *                                                              *
    * EXECUTID in the Attributes section                           *
    *                                                              *
    * - when the problem is present and tuning is not based on the *
    *                                                              *
    * access plan in the package cache, the RETURN node will not   *
    * show                                                         *
    * EXECUTID in the Attributes section                           *
    *                                                              *
    *                                                              *
    *                                                              *
    * The problem was first introduced in DB2 Version 9.7 Fix Pack *
    * 3.                                                           *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 4.                       *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 Version 9.7 Fix Pack 4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72250

  • 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

    2010-10-27

  • Closed date

    2011-04-28

  • Last modified date

    2011-04-28

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

  • 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

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC72250

Modified date: 28 April 2011