PM25679: ACCESS PATH ENHANCEMENT

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • Access path enhancement.                                (DM1490)
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 users of BIND and REBIND PACKAGE         *
    *                 commands who intend to utilize new function  *
    *                 to detect access path changes, reuse access  *
    *                 paths, or perform simulated REBINDs.         *
    ****************************************************************
    * PROBLEM DESCRIPTION: With new options, users of the BIND and *
    *                      REBIND PACKAGE commands will be able to *
    *                      detect access path changes (APCOMPARE)  *
    *                      and reuse access paths (APREUSE). In    *
    *                      addition, users of the REBIND PACKAGE   *
    *                      command can use the new EXPLAIN(ONLY)   *
    *                      option to populate EXPLAIN records      *
    *                      without performing an actual REBIND.    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This PTF supports the following new function:
    
    1. New option, APCOMPARE, on the BIND and REBIND PACKAGE
    commands
    
    With APCOMPARE, DB2 compares the incoming access paths with
    the ones that existed prior to the BIND/REBIND. Aggregated
    package-level statistics are reported via DSNT285I messages
    issued by the command. In addition, if the EXPLAIN (YES/ONLY)
    option is used, any differences are reported in the
    PLAN_TABLE.REMARKS column. Legal values of APCOMPARE are (NO)
    or (NONE), (WARN) and (ERROR).
    
      -With (ERROR), if DB2 detects a difference between the
       incoming and old access paths, the package is not
       bound/rebound.
    
      -With (WARN), package processing continues even if a
       difference in access paths is seen.
    
      -With (NO) or (NONE), APCOMPARE is not performed. This is
       the default.
    
    2. New option, APREUSE, on the BIND and REBIND PACKAGE commands
    
    With APREUSE, DB2 attempts to keep the same access paths that
    existed prior to the BIND/REBIND. So, while the package
    structures are created afresh, if the reuse is successful, the
    new and old access paths are structurally identical in terms
    of the EXPLAIN information exposed in PLAN_TABLE. Aggregated
    package-level statistics are reported via DSNT286I messages
    issued by the command. In addition, if the EXPLAIN(YES/ONLY)
    option is used, details on the reuse successes/failures are
    reported via PLAN_TABLE.HINT_USED and PLAN_TABLE.REMARKS
    columns.
    
    Please note that access path reuse is NOT guaranteed in all
    cases, and there may be statements whose access paths cannot
    be reused. Some common reasons for reuse failures include
    changes in schema (e.g. missing indexes), certain
    incompatiblities across versions (e.g., reusing DB2 9 access
    paths on DB2 10 may not work in all cases), insufficient
    information in the old packages for DB2 to reuse the access
    paths, etc.
    
    Legal values of APREUSE are (NO) or (NONE), and (ERROR).
    
      -With (ERROR), if DB2 is unable to reuse an old access path,
       the package is not bound/rebound.
    
      -With (NO) or (NONE), APREUSE is not performed.
    
    APREUSE(WARN) is not supported at this time.
    
    3. New option, EXPLAIN(ONLY), on the REBIND PACKAGE command
    
    With EXPLAIN(ONLY), DB2 simulates the REBIND PACKAGE command
    such that only EXPLAIN tables (PLAN_TABLE, etc.) are
    populated, but the actual REBIND is not performed. For such
    rebinds, PLAN_TABLE.BIND_EXPLAIN_ONLY is set to 'Y'.
    
    4. New options on DB2 Interactive (DB2I) panels
    
    The following DB2I panels were updated with additional
    options.
    
     DSNEBP10 DEFAULTS FOR BIND PLAN AND BIND PACKAGE
     DSNEBP11 DEFAULTS FOR REBIND PLAN AND REBIND PACKAGE
     DSNEBP18 FREE PACKAGE
     DSNEBP19 REBIND TRIGGER PACKAGE
    
    In order to accommodate the new options, DSNEBP10 and DSNEBP11
    have been converted to scrollable panels.  When viewing these
    panels, use the ISPF UP and DOWN keys to view the full range
    of options.
    
    For additional details, refer to DB2 product documentation.
    
    Additional Keywords:  SQLEXPLAIN
    

Problem conclusion

Temporary fix

  • Not applicable
    

Comments

APAR Information

  • APAR number

    PM25679

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function

  • Submitted date

    2010-11-01

  • Closed date

    2011-07-27

  • Last modified date

    2011-09-01

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

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

    UK70233 UK70234 UK70235

Modules/Macros

  •    DSNEBC02 DSNEBC03 DSNEBC04 DSNEBC11 DSNEBC19
    DSNEBP10 DSNEBP11 DSNEBP18 DSNEBP19 DSNLXRCS DSNTBAP  DSNTBMSG
    DSNXOD3  DSNXOGBM DSNXOGCM DSNXOLCM DSNXOLDE DSNXOLDG DSNXOLET
    DSNXOLM1 DSNXOMRG DSNXOPCO DSNXOPTH DSNXOSSC DSNXOTS1 DSN0BP10
    DSN0BP11 DSN0BP18 DSN0BP19
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK70233

       UP11/08/13 P F108

  • RA11 PSY UK70234

       UP11/08/13 P F108

  • RA14 PSY UK70235

       UP11/08/13 P F108

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM25679

Modified date:

2011-09-01

Translate my page

Machine Translation

Content navigation