RELEASE bind option
The RELEASE option determines when to release resources that a program uses, either at each commit point or when the program terminates.
The RELEASE option has no effect on many dynamic SQL statements, which generally use RELEASE(COMMIT) behavior with certain exceptions. For more information, see Dynamic SQL statements and the RELEASE(DEALLOCATE) option.
| Command option | Option values | Used with |
|---|---|---|
| RELEASE |
|
- The
RELEASE(INHERITFROMPLAN)option is not valid for REBIND of packages for native REST services. - The RELEASE bind option is not valid for REBIND of packages for native SQL procedures or advanced triggers.
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 partition locks use RELEASE(DEALLOCATE).
The RELEASE option does not affect page, row, LOB, or XML locks.
Option descriptions for RELEASE
- RELEASE(COMMIT)
-
Db2 releases resources at each commit point, unless cursors are held. If the application accesses the object again, it must acquire the lock again.
Table, partition, or table space locks are released at the next commit point unless the cursor is defined WITH HOLD. Cursors that are defined under the WITH HOLD option are an exception, and locks that are necessary to maintain the cursor position are held past the commit point.
When the RELEASE(COMMIT) option is in effect, locks are released at different times depending on the attachment facility:
Environment Release condition 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. Locks that are originally acquired with the RELEASE(COMMIT) option in effect might be promoted to deallocate duration if an application issues subsequent statements from a package with the RELEASE(DEALLOCATE) option in effect. For more information, see Applications with multiple packages and mixed RELEASE options.
- RELEASE(DEALLOCATE)
-
With the RELEASE(DEALLOCATE) option in effect, Db2 generally releases locks and other resources only when a thread terminates.
However, certain situations and settings are exceptions, where the RELEASE(DEALLOCATE) option does not apply, or the release behavior is modified. For more information, see the following sections:
- Dynamic SQL statements and the RELEASE(DEALLOCATE) option
- Distributed packages and the RELEASE(DEALLOCATE) option
- Break-in for the RELEASE(DEALLOCATE) option
- Declared temporary tables and the RELEASE(DEALLOCATE) option
- Lock demotion and the RELEASE(DEALLOCATE) option
The RELEASE(DEALLOCATE) option can increase the package or plan size, because more items become resident in the package or plan.
- RELEASE(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) option 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
| Process | Default value |
|---|---|
| BIND SERVICE | COMMIT |
| BIND PLAN | COMMIT |
| BIND PACKAGE |
|
| REBIND PLAN | Existing value |
| REBIND PACKAGE | Existing value |
| REBIND TRIGGER PACKAGE | Existing value |
Applications with multiple packages and mixed RELEASE options
If an application issues statements from multiple packages with different RELEASE bind options, the RELEASE option in effect for the application process can change. That is, locks might be first acquired with RELEASE(COMMIT) duration but later promoted to RELEASE(DEALLOCATE) when the application runs a subsequent statement from a different package with the RELEASE(DEALLOCATE) option.
When statements from more than one package acquire partition locks for the same table space, all partition locks are held for the same duration. If the first statement to access the table space is from a package that uses the RELEASE(COMMIT) bind option, all partition locks follow the RELEASE(COMMIT) rules. However, if a subsequent statement from another package that uses the RELEASE(DEALLOCATE) bind option accesses a partition in the same table space, all partition locks are then promoted to follow the RELEASE(DEALLOCATE) rules.
Dynamic SQL statements and the RELEASE(DEALLOCATE) option
- 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. For more information, see CACHE DYNAMIC SQL field (CACHEDYN subsystem parameter) and KEEPDYNAMIC bind option.
- 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. For more information, see Declared temporary tables and the RELEASE(DEALLOCATE) option.
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.)
Distributed packages and the RELEASE(DEALLOCATE) option
Packages that are executed on a Db2 server through a DRDA connection with a client system can apply the RELEASE(DEALLOCATE) option in most situations. 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 connection with a client system.
For more information, see Controlling deallocation for high-performance DBATs and .
Break-in for the RELEASE(DEALLOCATE) option
- 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.
For packages that are bound with the RELEASE(DEALLOCATE) option, the following operations can proceed at COMMIT or ROLLBACK while the package is active and allocated for a persistent Db2 thread:
- BIND REPLACE PACKAGE 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
YES is the default value.
For more information, see PACKAGE RELEASE COMMIT field (PKGREL_COMMIT subsystem parameter).
- 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
Declared temporary tables and the RELEASE(DEALLOCATE) option
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. The 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.
Lock demotion and the RELEASE(DEALLOCATE) 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).
Catalog records for RELEASE
See the RELEASE column in SYSPACKAGE catalog table and SYSPLAN catalog table.