Autobind failures now issue console messages in Db2 12
Paul_McWilliams 110000JT36 Visits (1170)
One of the current Db2 for z/OS Ideas with the most votes (RFEs are now Ideas) is to provide a message when an autobind fails and a package goes inoperative. We are happy to announce that we have now satisfied that requirement. After you apply APAR PH13550, if an autobind fails resulting in an inoperative package, Db2 issues console message DSNT500I.
An autobind occurs when a package that is invalid is executed. There are many ways that packages become invalid but the most common are related to ALTER or DROP on an object that the package depends on. Dependencies for static packages are recorded in the SYSIBM.SYSPACKDEP catalog table. If a package is dependent on an object, and that object is dropped or altered, the package will, in most cases, be invalidated. Once a package is invalidated, the next execution of that package results in an autobind attempt.
Most of the time, the autobind succeeds and the package is re-enabled for execution. However, autobind can sometimes fail and the package becomes inoperative. The problem with an inoperative (as opposed to invalid) package is that an inoperative package does not attempt to autobind when it is executed Thus, when a package becomes inoperative, manual intervention is required to REBIND the package so that application can use it.
The problem is: “When is that manual intervention required?” Before APAR PH13550, Db2 doesn’t externalize a failed autobind to the console. Only the application (the thread that executed the failed autobind) receives SQLCODE -904 After the autobind failure, subsequent attempts to use the package most likely fail with an SQLCODE –805, but other SQLCODEs are possible, depending on reason for invalidation and the BIND options for the package.
With APAR PH13550 applied, Db2 now issues console message DSNT500I when autobind fails and the package becomes inoperative (SYS
Resource type: '804'x
Resource name: coll
DSNT500I is issued only once per package regardless of the number of threads or members attempting to access and autobind the package. The message is issued at the location where the autobind fails (the server where the autobind was attempted). If Db2 11 and Db2 12 are in co-existence, the message is issued only if autobind fails on a Db2 12 member. If an autobind fails, the current SQLCODE -904 is returned, with resource type ‘801’x to the thread that attempted the failed autobind (this is the same behavior as before PH13550).
If you are using NetView for automation1, you can use the following examples to capture the message and run a job to rebind the package:
/* CLIST NAME : DSNT500I */
'GETMSIZE NUMLINES' /* If so get # of lines and */
DO I=1 TO NUMLINES /* put each line into a */
'GETMLINE LINE'||I I ; LINE.I = VALUE(LINE||I)
END /* variable for processing. */
PARSE VAR LINE.2 'REASON' reas /* reason always x00E30305 */
PARSE VAR LINE.3 'TYPE' restype /* resource type always x804 */
PARSE VAR LINE.4 'NAME' package /* package name to rebind */
If reas = '00E30305' & restype = '00000804' Then
bldsubjob: Parse Upper Arg package
jcl.0 = 0
call nx"//"REBIND1 JOB 'SAM
call nx"//JOBLIBDD DISP
call nx"//REBINDEXEC PGM=IKJEFT01"
call nx"//SYSPRINT DD SYSOUT=*"
call nx"//SYSTSPRT DD SYSOUT=*"
call nx"//SYSTSINDD *"
call nx" DSN SYSTEM(DSN)"
call nx" REBINDPACKAGE ("package")"
call nx" END"
Parse ARG intext
jcl.0 = jcl.0 + 1
nn = jcl.0
jcl.nn = intext
1 Thanks to Caprice Settles for providing the NetView Automation example.
You might ask, "Why would automation work to REBIND a package when autobind did not?" There may be various reasons a package is invalidated. For example, let’s say you have package A that is dependent on table T1, and is bound VALIDATE(BIND). When you DROP T1 and COMMIT the change, there may be an immediate autobind attempt for package A – but T1 does not exist, so the autobind fails. While that autobind is being attempted, another job is re-creating T1, but the autobind fails before the job has committed. Package A is now inoperative. A message is issued, automation captures the message, and attempts a REBIND. In this case, the REBIND works, and transactions can now continue to use package A.
With PH13550, we are providing a much-needed message that, when coupled with automation, can reduce the need for manual REBINDs that may be required today. Once the automation is in place, there will be an extra layer of capability in place that may prevent failed transactions and DBA callouts.