Enabling autobind phase-in for packages invalidated at the statement level
You can enable applications to execute specific invalidated packages without waiting for the automatic bind to complete by enabling the packages to use statement-level dependencies and invalidation.
Before you begin
Db2 uses automatic binds only when the ABIND subsystem parameter is set to YES or COEXIST (Db2 13 uses the same behavior is used for both settings.). If ABIND is set to NO when an invalid package runs, Db2 returns an error. For details, see AUTO BIND field (ABIND subsystem parameter).
About this task
FL 504 With statement-level dependencies and statement-level invalidation, applications can execute a package that is invalidated at a statement level without waiting for the completion of the automatic rebind (autobind). The application thread can execute the valid statements immediately. Any invalid statements go through incremental bind in the application thread before execution. Concurrently, Db2 initiates a special autobind, which is called autobind phase-in. When the autobind phase-in finishes successfully, the subsequent executing threads can use the new valid copy of the package, and they no longer use incremental bind for any statements.
For more information, see Autobind phase-in for packages with statement-level invalidation.
Procedure
To identify and enable packages that are likely to benefit from statement-level dependencies, complete the following steps:
What to do next
- Evaluate the MAX_CONCURRENT_PKG_OPS subsystem parameter values. For example, if you prefer the total maximum number of service tasks for regular autobind and autobind phase-in to be 10, set MAX_CONCURRENT_PKG_OPS to 5. There are at most 5 service tasks for regular autobind and another 5 service tasks for autobind phase-in.
- Consider changing the maximum number of locks to avoid lock escalation. With statement-level dependencies, BIND, REBIND, autobind, and DDL processes access more catalog records, so the number of lock requests increases. If there are more row locks requested on a catalog table or by a process than the maximum number of locks allowed, Db2 escalates the table space lock with S or X mode. Lock escalation can impact the concurrent access of the catalog table by other threads and is reported in message DSNI031I. The initial default value of the LOCKMAX clause for catalog tables is determined by the NUMLKTS subsystem parameter value. You can change it by using the ALTER TABLESPACE statement with the LOCKMAX clause.
- Consider whether to monitor the new accounting and statistics trace information for evaluating the cost of incremental binds. If waiting occurs for the service tasks for the autobind phase-in queue, it might imply that more threads need to incremental bind the invalid statements. Increasing the MAX_CONCURRENT_PKG_OPS might help to expedite the autobind phase-in process, which can make the new current copy available sooner, and thus eliminates incremental bind for new threads.
- Plan to remove unneeded dependency records for invalidated statements in the SYSPACKDEP and SYSPACKSTMTDEP tables. When the invalid current copy of a package becomes a phased-out copy due to a successful autobind phase-in, the dependency records of the invalidated statements remain. You can free the phased-out package copies by using the FREE PACKAGE command with the PLANMGMTSCOPE(PHASEOUT) bind option.