Introduction:

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.

PH15896 enhancement:

AUTOBIND now does its operation by enforcing APREUSE(WARN) and APREUSESOURCE(CURRENT).

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.

or DSNT219I if you are using PLANMGMT.

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.

Views: 188

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

Introducing IBM Db2 for z/OS Developer Extension for Microsoft Visual Studio Code

Started by Calene Janacek in Application Development and DB2 Jul 30. 0 Replies

We are excited to announce that the first iteration of IBM Db2 for z/OS Developer Extension is available now as a free downloadable extension in the…Continue

QMF Governor

Started by Maitena Gallastegi Ginea in Application Development and DB2. Last reply by Maitena Gallastegi Ginea Jul 30. 4 Replies

Hi,We are using QMF Governor to limit the QMF queries of users.We have configured correctly and it is working OK. We want to get statistics of those queries canceled by QMF Governor but we are not able to discover where that information is stored.…Continue

© 2020   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service