Introduction:

AUTOBIND is a very useful automation instrument inside the Db2 engine.

Suppose you change the definition of the table or index and these changes have impact on the dependent package, the static SQL package is marked as invalid, because a REBIND PACKAGE is required. Such a REBIND requires user intervention, because you must determine that a package need such a REBIND. Of course, this is doable by examining the VALID column in SYSIBM.SYSPACKAGE.

AUTOBIND has the beauty that the required “rebind” action can be deferred until the package is requested by application. Then the required “rebind” takes place automatically.

So, no user intervention is required, theoretically.

The following should be considered:

1) AUTOBIND must be enabled by the DSNZPARM setting ABIND=YES. If AUTOBIND is disabled, then the following SQLCODE is returned at runtime to the application program:

DSNT408I SQLCODE = -908, ERROR:  AUTO-REBIND ERROR USING SYSADM

AUTHORITY.  BIND, REBIND, OR AUTO-REBIND OPERATION IS NOT ALLOWED

2) AUTOBIND does currently not honour PLANMGMT and APREUSE settings.

3) AUTOBIND happens only once. Either it is successful, or it fails, then the package is set also to inoperative, which requires a REBIND.

4) Due to practical experience AUTOBIND can fail (e.g. Db2 code error or as in my example a problem with the PLAN_TABLE): See the following enhancement.

PH13350 enhancement:

If an AUTOBIND fails, the application gets the following SQLCODE:

DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E30305, TYPE OF RESOURCE                

          00000801, AND RESOURCE NAME DB.APPGM.1AD2BE670ABFE5F4

The package is set now not only to VALID = ‘N’ but also to OPERATIVE = ‘N’, which means an explicit REBIND is required.

With PH13550 a DSNT500I message with a new type is issued to the syslog if AUTOBIND fails:

DSNT500I  -DB2A DSNTBAB2 RESOURCE UNAVAILABLE                       

           REASON 00E30305                                             

           TYPE 00000804                                                

           NAME "DB"."APPGM".("VERSION-0001")                    

This message can be used to automate a required REBIND:

REBIND PACKAGE (DB.APPGM.(VERSION-0001))       EXPLAIN(NO)

And you might encounter the following message, dependent on your PLANMGMT usage. Beginning with V12 invalid packages are not saved to ORIGINAL and/or PREVIOUS.

DSNT219I -DB2A DSNTBRB2 REBIND FOR                             

           PACKAGE = STLEC1.DB.APPGM.(VERSION-0001) SUCCEEDED BUT

           A PREVIOUS AND/OR ORIGINAL PACKAGE COPY WAS NOT      

           CREATED/REPLACED BECAUSE THE CURRENT PACKAGE COPY WAS

           INVALID.  

Recreation scenario for testing the automation:

1) Create a table TB1 with 3 columns: COL1, COL2, COL3

2) Create 2 indexes (non-unique), one IX1 on COL1 and one IX2 on COL2

3) Have a program which does via static SQL a SELECT COL1, COL2, COL3 FROM TB1 WHERE COL1=? AND COL2=?. This should result in a multi index access for this statement.

4) Bind the plan and package with EXPLAIN(YES) and VALIDATE(BIND).

5) Run the program, which is successful.

6) Drop the index IX1. The package will be invalidated.

7) Drop the PLAN_TABLE and create the PLAN_TABLE with missing columns.

Consider that dropping the PLAN_TABLE alone does not generate an AUTOBIND failure, because AUTOBIND ignores the missing PLAN_TABLE and goes on.

That this works, DSNZPARM setting ABEXP must be YES, which is the default.

8) Run the program again, which is not successful: DSNT500I is issued with this new type.

If you want to have the complete recreation scenario, please send an email to peterhar@de.ibm.com