PH15896: AUTOBIND DOES NOT TRY TO REUSE THE ACCESS PATH FROM CURRENT PACKAGE COPY SO ACCESS PATH COULD CHANGE, AFFECTING PERFORMANCE

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Today autobind does not try to reuse the access path from the
  current package copy.  As a consequence, the new access path
  obtained during autobind could be different, which might
  affect the performance of subsequent execution.
  
  This APAR will enforce APREUSE(WARN) and APREUSESOURCE(CURRENT)
  during autobind.
  This will be the new behavior of autobind. It starts in
  Function Level 100, does not require any system parameter
  setting, and is independent from the plan management and
  APREUSE settings that currently exist in the package.
  SYSPACKAGE.APREUSE column will be updated to reflect this new
  behavior.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users who execute a                      *
  * package that causes Db2 to automatically                     *
  * rebind the package prior to the execution                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * During package execution, Db2 may                            *
  * automatically rebind the package                             *
  * prior to execution. There are                                *
  * two concerns about automatic rebind                          *
  * (autobind):                                                  *
  *  1. Db2 does not try to reuse the                            *
  *     access path from the current                             *
  *     package. As a result, the access                         *
  *     path obtained during autobind may                        *
  *     be different and could affect the                        *
  *     performance of the subsequent                            *
  *     executions.                                              *
  *  2. SYSPACKAGE.APREUSE column isn't                          *
  *     updated to reflect the APREUSE                           *
  *     bind option value that is used                           *
  *     during autobind.                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  During package execution, Db2 may need to automatically
  rebind (autobind) the package first. However, during autobind:
   1. Db2 does not try to reuse the access path from the current
      package, that is, APREUSE(NO) is in effect. As a result,
      the access path obtained during autobind may be different
      and could cause a performance problem to subsequent
      executions.
   2. SYSPACKAGE.APREUSE column isn't updated to reflect the
      APREUSE bind option value that is used during autobind.
  The following are two examples where Db2 needs to automatically
  rebind the package before the execution:
   1. The package was created in an unsupported release,
      that is, V9 or prior.
      For non-SQL PL packages, SYSPACKAGE.RELBOUND tells
      which release a package is bound.
      For SQL PL packages, SYSROUTINES.RELCREATED tells which
      release the routine is created. If SYSROUTINES.RELCREATED
      shows V9, its package might be from an unsupported release,
      even though SYSPACKAGE.RELBOUND shows a supported release.
   2. The package is operative but in an invalid state, that
      is, SYSPACKAGE.OPERATIVE = 'Y' and SYSPACKAGE.VALID = 'N'.
      The schema change via an ALTER statement is the most
      common example where the package that references the
      altered object is placed in the invalid state.
  Additional Keywords: FL100 SQLACCESSPATH SQLPERFORMANCE
                       SQLAUTOBIND SQLAPREUSE
  

Problem conclusion

Temporary fix

Comments

• The new functionality delivered in this APAR supports
  V10 packages or higher.
  For a non-SQL PL package, SYSPACKAGE.RELBOUND tells
  which release a package is bound.
  For an SQL PL package, SYSROUTINES.RELCREATED tells which
  release the routine is created. If SYSROUTINES.RELCREATED
  shows V9, its package might be from the unsupported release,
  even though SYSPACKAGE.RELBOUND shows a supported release.
  The ALTER statement with the REGENERATE keyword can be used
  to regenerate the package to the supported release.
  With this APAR, if the current package copy is in a
  supported release, that is, V10 or higher, during the
  automatic rebind (autobind), Db2 will try to reuse the
  access path from the current package by following the
  APREUSE(WARN) and APREUSESOURCE(CURRENT) functionality.
  With this APAR, SYSPACKAGE.APREUSE column will always be
  updated to reflect the APREUSE bind option value that is
  used during autobind. That is:
    1. 'W' if the package is in V10 or higher
    2. 'N' if the package is in V9 or prior
  The new support starts in Function Level 100 and is not
  controlled by the APPLCOMPAT option.
  The APREUSE related messages such as DSNT286I and DSNT285I
  will be suppressed.
  This APAR does not support an advanced trigger package
  because today APREUSE is not supported in the REBIND command
  for an advanced trigger.
  

APAR Information

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

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

  UI67906

Modules/Macros

• DSNXEAAL DSNTBAB2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI67906

     UP20/02/22 P F002

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.