Controlling allocation and deallocation processing for database access threads

You can use package release options to reduce the use of CPU resources for package allocation and deallocation processing and retain the ability to conduct routine and emergency maintenance tasks.

About this task

When database access threads run in INACTIVE mode according to the value of the CMTSTAT subsystem parameter, Db2 honors the RELEASE bind option of the package. When packages use the RELEASE(DEALLOCATE) option, the amount of allocation and deallocation processing is reduced, because the copy of the package remains allocated until the database access thread is terminated.

However, database access threads that run according the rules of the RELEASE(DEALLOCATE) bind option can hold package locks and table space intent locks that can effectively block your ability to modify objects, invoke utilities, or bind or rebind packages. The MODIFY DDF command enables you to change the processing rules that Db2 uses for database access threads.

Procedure

To minimize the use of CPU resources for package allocation and deallocation processing, use the following practices:

  • During normal production operating hours, enable Db2 to allocate and deallocate packages according to the rules of the RELEASE(DEALLOCATE) bind option.
    • You can issue either of the following commands to specify that Db2 uses the rules of the RELEASE bind option that is specified for the package:
      Specify that terminated DBATs are deallocated
      MODIFY DDF PKGREL(BNDOPT)
      Specify that terminated DBATs are pooled
      MODIFY DDF PKGREL(BNDPOOL)
  • For nightly utilities and emergency maintenance, specify that Db2 uses the rules of the RELEASE(COMMIT) bind option for all packages.
    You can issue the following command to specify that behavior for all packages, regardless of the RELEASE option specified for each package:
    MODIFY DDF PKGREL(COMMIT)
    The effects of issuing this command are not immediate. However, any database access thread is terminated and made inactive when the next commit point is reached. Any RELEASE(DEALLOCATE) database access threads that remain active waiting for a new unit-of-work request from a client are terminated by a DDF service task, which runs every two minutes. Subsequently, when any new unit-of-work creates a database access thread, the packages are allocated according to the rules of the RELEASE(COMMIT) bind option.