Choosing a RELEASE option

The RELEASE bind option controls when an application releases locks that it acquires on objects such as partitions, tables, or table spaces that it accesses. It applies only to partition, table, and table space locks.

About this task

Begin program-specific programming interface information. An application program acquires table, partition, or table space locks only when it accesses the specific objects. In most cases, the least restrictive lock mode that is required to process each SQL statement is used. However, a statement sometimes reuses a more restrictive lock than required, if a suitable lock remains from a previous statement that accessed the same objects.

The RELEASE bind option has no effect on some dynamic SQL statements, which use RELEASE(COMMIT) behavior except in the following situations:

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.

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

Procedure

To select a RELEASE bind option that is appropriate for the characteristics of a particular application, use any of the following approaches:

Specify the RELEASE(DEALLOCATE) bind option to prioritize processing efficiency. With the RELEASE(DEALLOCATE) option in effect, Db2 generally releases locks and other resources only when a thread terminates. However, many situations and settings prevent the RELEASE(DEALLOCATE) option from taking effect or modify the release behavior. For more information about these situations, see RELEASE bind option.

Specify the RELEASE(COMMIT) option to prioritize concurrency for many applications. However, if the application commits frequently, the RELEASE(COMMIT) option requires more processing time. When the RELEASE(COMMIT) option is in effect, locks are released at different times, depending on the attachment facility:

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.

FL 500 You can change the RELEASE option in effect for packages from DEALLOCATE to COMMIT behavior at package load time and at end of transaction boundary by creating a system profile with the PACKAGE_RELEASE keyword. For more information, see Overriding the RELEASE(DEALLOCATE) option for packages by using profile tables.

Example

Consider an application that selects employee names and telephone numbers from a table. Assume that employees can update their own telephone numbers, and they can run several searches in succession. The application is bound with the RELEASE(DEALLOCATE) bind option because most uses of this application do not update and do not commit. For those uses, little difference exists between RELEASE(COMMIT) and RELEASE(DEALLOCATE).

However, administrators might update several phone numbers in one session with the application, and the application commits after each update. In that case, RELEASE(COMMIT) releases a lock that Db2 must acquire again immediately. RELEASE(DEALLOCATE) holds the lock until the application ends, avoiding the processing that is required to release and acquire the lock several times. End program-specific programming interface information.