RELEASE bind option

Partition locks follow the same rules as table space locks, and all partitions are held for the same duration. Thus, if one package is using RELEASE(COMMIT) and another is using RELEASE(DEALLOCATE), all partitions use RELEASE(DEALLOCATE).

The RELEASE option does not affect page, row, LOB or XML locks.

( 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.

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).

RELEASE(DEALLOCATE) has no effect on dynamic SQL statements, which always use RELEASE(COMMIT), with one exception: 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.

Packages that are executed on a DB2 server through a DRDA connection with a client system honor the RELEASE(DEALLOCATE) bind option. However, if the MODIFY DDF PKGREL(COMMIT) command has been 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 additional 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:

Catalog record: Column RELEASE of tables SYSPACKAGE and SYSPLAN.