RELEASE bind option

Option descriptions for RELEASE

(COMMIT)

Releases resources at each commit point, unless cursors are held. If the application accesses the object again, it must acquire the lock again.

• All tables and table spaces are unlocked when:

  TSO, Batch, and CAF

  An SQL COMMIT or ROLLBACK statement is issued, or your application process terminates.

  IMS

  A CHKP or SYNC call (for single-mode transactions), a GU call to the I/O PCB, or a ROLL or ROLB call is completed.

  CICS®

  A SYNCPOINT command is issued.

  Exception:

  If the cursor is defined WITH HOLD, table or table space locks necessary to maintain cursor position are held past the commit point.

• Table, partition, or table space locks are released at the next commit point unless the cursor is defined WITH HOLD.
• The least restrictive lock needed to execute each SQL statement is used except when a more restrictive lock remains from a previous statement. In that case, that lock is used without change.

(DEALLOCATE)

Releases resources only when the thread terminates.

However, Db2 can release resources to allow processes to break in when they require access to packages and the catalog:

Break-in for active threads

If the PKGREL_COMMIT subsystem parameter setting is the default value YES, Db2 threads release active packages at COMMIT or ROLLBACK if certain operations are waiting for exclusive access to the package. The following operations can proceed at COMMIT or ROLLBACK while a persistent thread is active on a package:

• BIND PACKAGE with REPLACE and REBIND PACKAGE requests, including automatic rebind for online schema changes (DDL statements) for tables and indexes that are statically referenced by the package
• Online REORG operations that materialize pending definition changes for objects that are statically referenced by the package

Break-in for idle threads

If a package lock is likely to time out, Db2 recycles any idle threads that use local attachment facilities, such as CICS, IMS, or RRSAF. A thread is eligible for recycling if it is has these characteristics:

• Is at a transaction boundary
• Is not running in Db2
• Has not committed or rolled back recently

When you specify RELEASE(DEALLOCATE), some static and dynamic statements that reference declared temporary tables are also kept as prepared across commit points unless the table was defined with the ON COMMIT DROP TABLE option. These statements that are kept as prepared across commit points are INSERT, UPDATE, DELETE, MERGE, and SELECT INTO statements. The statement is not kept across the commit point if one of the following conditions is true:

• The declared global temporary table is defined with the ON COMMIT DROP TABLE option.
• The statement also references a Db2 base object (for example, a table or view), and one of the following statements is true:
  • The base object reference is for a Db2 catalog table.
  • At the commit point, Db2 determines that another Db2 thread is waiting for an X-lock on the base object's database descriptor (DBD).
  • The statement references an XML function or operation, and at the commit point Db2 determines that the base object DBD S-lock for the XML operation must be released.
  • At the commit point, Db2 determines that a base object DBD S-lock that is used by the statement must be released and cannot be maintained across the commit point.
• Db2 determines that another Db2 thread is waiting for an X-lock on the Db2 package that contains the statement.

RELEASE(DEALLOCATE) has no effect on most dynamic SQL statements, which use RELEASE(COMMIT), except in the following cases:

• When you use RELEASE(DEALLOCATE) and KEEPDYNAMIC(YES), and your subsystem is installed with the CACHEDYN subsystem parameter set to YES, the RELEASE(DEALLOCATE) option is honored for dynamic SELECT, INSERT, UPDATE, and DELETE statements.
• When you use RELEASE(DEALLOCATE), prepared INSERT, UPDATE, DELETE, and MERGE dynamic statements that reference declared temporary tables are kept past commit points unless the table was defined with the ON COMMIT DROP TABLE option.

If a lock is to be held past commit and it is an S, SIX, or X lock on a table space or a table in a segmented table space, Db2 sometimes demotes that lock to an intent lock (IX or IS) at commit. Db2 demotes a gross lock if it was acquired for one of the following reasons:

• Db2 acquired the gross lock because of lock escalation.
• The application issued a mass delete (DELETE FROM object without a WHERE clause or TRUNCATE).

Packages that are executed on a Db2 server through a DRDA connection with a client system apply the RELEASE(DEALLOCATE) bind option. However, if the MODIFY DDF PKGREL(COMMIT) command is issued at a Db2 server, the RELEASE(DEALLOCATE) option has no effect on packages that are executed on that server through a DRDA connection with a client system.

Locks that are acquired for dynamic statements are held until one of the following events occurs:

• The application process ends (deallocation).
• The application issues a PREPARE statement with the same statement identifier. (Locks are released at the next commit point.)
• The statement is removed from the cache because it has not been used. (Locks are released at the next commit point.)
• An object that the statement is dependent on is dropped or altered, or a privilege that the statement needs is revoked. (Locks are released at the next commit point.)

RELEASE(DEALLOCATE) can increase the package or plan size, because more items become resident in the package or plan.

INHERITFROMPLAN

Enables a local package to inherit the value of the RELEASE option from the plan, regardless of whether the package was bound remotely or locally.

If you bind a package remotely with the RELEASE(INHERITFROMPLAN) option and the remote server does not understand the INHERITFROMPLAN value, the server might return an error.

The RELEASE(INHERITFROMPLAN) value is not applied in the following situations, because no associated plan exists:

• If you bind the application locally, and then copy the package to a remote server.
• If you bind an application that uses RRSAF.
• For any packages that are created for utilities.

In these cases, RELEASE(COMMIT) is in effect for the package.

Defaults for RELEASE

Catalog records for RELEASE

See the RELEASE column in SYSPACKAGE catalog table and SYSPLAN catalog table.