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

Views: 1006

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

Db2 for z/OS Master class with John Campbell and the SWAT team returns!

Started by Surekha Parekh in What's hot ?. Last reply by carol Goldberg on Thursday. 1 Reply

Db2 for z/OS Master class with John Campbell and the SWAT team returns! June 24-28, 2019 at IBM Hursley near Winchester, UKSeptember 23-27, 2019 at IBM Silicon Valley Lab in San Jose, California…Continue

Tags: Events, MasterClass, JohnCampbell

RBS Shares their Db2 Utilities Experiences

Started by Calene Janacek in What's hot ? Oct 22. 0 Replies

Join Mark Turner, Lead Mainframe Architect and Strategist from RBS and Haakon Roberts, IBM DE as they share Royal Bank of…Continue

Tags: #Db2

© 2019   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service