By Tammie Dang and Paul McWilliams
With the release of function level 504 in Db2 13 for z/OS, you can now enable autobind phase-in for packages invalidated at statement level granularity. This new capability uses the statement-level dependency and invalidation infrastructure that was introduced in function level 502, including the new DEPLEVEL(STATEMENT) package bind option. With statement-level dependencies and statement-level invalidation, applications can continue execute a package that is invalidated at a statement level, without waiting for the full automatic rebind (autobind) of the package to complete.
Before the availability of this capability, customers have often reported SQL performance and application availability problems due to the lack of concurrency between execution and autobind of invalid packages. The execution of a DDL statement to make database object schema changes can invalidate packages that have static SQL statements that reference the changed object. This invalidation process can also take place when executing a utility job, such as REORG of a table materializing the pending DDL statements. Once invalidated, on the next execution of such packages, Db2 triggers an autobind process for the package. The execution of the package must wait until the autobind completes successfully. The autobind is done by a separate Db2 service task, and multiple threads executing the same or different invalid packages must request a bind lock to obtain a service task. The multiple service tasks must also compete for the locks (on the collection and package) to bind the package, and transaction timeouts are possible. An autobind failure can make things worse by marking the package inoperative. Inoperative packages cannot be automatically rebound or executed until the user explicitly rebinds the package successfully.
However, if a package is enabled for the new statement-level invalidation capability, an online schema change process can mark specific statements that reference the schema-changed object as invalid, leaving other statements valid as is. Then when an application thread subsequently executes the partially invalidated package, Db2 can continue to execute valid statements immediately. If an invalid statement in the application package needs to be executed, it goes through an incremental bind process in the application thread before execution. Concurrently, Db2 also initiates a special autobind operation called “autobind phase-in” for the package. When the autobind phase-in finishes successfully, the subsequent executing threads can use the new valid copy of the package, and they no longer need to perform incremental bind for any statements. Note that the application logic may not execute any invalid statement in the package, in which case, the performance overhead is not observed at all.
The autobind phase-in process runs concurrently with executing application threads, and the old partially invalid copy becomes a phased-out copy, which is stored in the SYSIBM.SYSPACKCOPY catalog table. The new copy of the package becomes the current copy. Subsequent executions of the package use the new valid copy. Threads that existed prior to autobind phase-in completing can also use the new current copy when they release the phased-out copy (based on the RELEASE(COMMIT) bind option). In this respect, autobind phase-in is the same as the rebind phase-in capability. However, unlike other phase-in rebinds, autobind phase-in does not require the PLANMGMT bind option of the package to be set to EXTENDED. As usual, Db2 uses automatic binds, phase-in or not, only when the ABIND subsystem parameter is set to YES or COEXIST. If ABIND is set to NO when an invalid package runs, Db2 returns an error.
Autobind phase-in can be a useful feature to minimize the impacts of the online schema change process. Customers can meet several challenges when changing a database object schema. Quiescing applications and invalidating application packages are two issues that can both prevent a successful schema change operations and affect application availability. With Db2 13 function level 504, the pain-point of package invalidation might be reduced with the autobind phase-in enhancement. This feature can incur some overhead of temporary incremental bind and additional storage for tracking package dependencies and validity more granularly, but enabling it for packages with a small subset of statements that reference a target database object can prevent the transaction timeouts and improve the object schema change experience.
#Db2forz/OS#Db2Znews#db2z13