IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
AUTOBIND is a very useful automation instrument inside the Db2 engine.
Recently there are 2 enhancements in this area. For the first enhancement refer to my blog post.
The second enhancement is described in this blog post and was introduced by PH15896.
This ensures that the old access path is reused, if the old access path can be re-used.
It is of course not possible if e.g. an index is missing for the old access path due to a drop of the index.
Please consider the following 2 topics:
1) If later a REBIND runs without specifying APREUSE, then the default of APREUSE(NO) is used. This means that e.g. AUTOBIND with APREUSE(WARN) has preserved the access path, but the REBIND establishes again the "best available" access path at this time: Perhaps you have created an usable index after AUTOBIND operation.
2) The difference between "letting AUTOBIND do the work" and an "explicit REBIND" with the mentioned parameters is that AUTOBIND does not issue
DSNT286I -DB2A DSNTBBP2 REBIND FOR PACKAGE = STLEC1.DB.APPGM1, USE OF APREUSE RESULTS IN:
0 STATEMENTS WHERE APREUSE IS SUCCESSFUL
1 STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL OR PARTIALLY SUCCESSFUL
0 STATEMENTS WHERE APREUSE COULD NOT BE PERFORMED
0 STATEMENTS WHERE APREUSE WAS SUPPRESSED BY OTHER HINTS.
To detect whether APREUSE(WARN) was successful or not, you have to look into the PLAN_TABLE under the REMARKS column and detect the reason why APREUSE(WARN) has failed, e.g.
APREUSE FAILURE (REASON: 13) APCOMPARE FAILURE (COLUMN: ACCESSTYPE).
For more details of these reason codes, refer to SQLCODE +395.
Refer also to the knowledge center about the latest enhancements of Db2 12 for z/OS.
Add a Comment