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 option applies only to static SQL statements, which are bound before your program runs. Dynamic SQL statements acquire locks only when they access objects and release the locks at the next commit point. However, locks for dynamic statements might be held past commit. When the KEEPDYNAMIC(YES) bind option is specified and the value of the CACHEDYN subsystem parameter is YES, the RELEASE(DEALLOCATE) bind option is honored for dynamic SELECT, INSERT, UPDATE, and DELETE statements.

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

Procedure

Choose a value for the RELEASE bind option that is appropriate for the characteristics of the particular application:

RELEASE(DEALLOCATE)

This option results in the most efficient use of processing time in most cases. When RELEASE(DEALLOCATE) is used, Db2 generally frees resources for packages when the threads that are associated with the packages are deallocated. This behavior provides an opportunity for better performance for many applications.

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

RELEASE(COMMIT)

This option provides the greatest concurrency. However, if the application commits frequently, RELEASE(COMMIT) requires more processing time. Under this option, locks are released at different times, depending on the attachment facility:

TSO, Batch, and CAF: An SQL COMMIT or ROLLBACK statement is issued, or your application process ends.
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.

Cursors that are defined under the WITH HOLD option are an exception. Locks that are necessary to maintain the cursor position are held past the commit point.

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.