IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
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
March 16, 2021 from 11am to 11:30pm – Webcast
0 Comments 3 LikesBringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.
Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply 0 Likes
I would need to know who modified table data on a certain day.I do have my backups and database logs available.DB2audit was however not activated at this point.How would i be able to access this information?Continue
Tags: #DataAccess
Started by Kip in What's hot ? Nov 4, 2020. 0 Replies 0 Likes
This digital event examines how enterprises can become smarter with a data management system that is developer-inclusive. During this session, participants will get to learn how Db2 integrates with a broad array of programming languages, employs…Continue
© 2021 Created by Surekha Parekh.
Powered by
You need to be a member of The World of DB2 to add comments!
Join The World of DB2