PM25679: ACCESS PATH ENHANCEMENT

Obtain the fix for this APAR.

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 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.