DB2 Version 10.1 for Linux, UNIX, and Windows

Soft invalidation of database objects

When soft invalidation is active, an object can be dropped even if other running transactions are using it. Transactions that were using the dropped object are permitted to continue, but any new transaction will be denied access to the dropped object.

All cached statements and packages that directly or indirectly refer to the object being dropped or altered are marked as not valid (and are said to be invalidated). Soft invalidation allows DDL affecting the referenced objects to avoid waits that otherwise would result from statements being run holding locks on objects to which they refer, and allows any active access to continue using a cached version of the object, eliminating the possibility of lock timeouts.

By contrast, when hard invalidation is used, exclusive locking is used when referencing an object. This guarantees that all processes are using the same versions of objects and that there are no accesses to an object once it has been dropped.

Soft invalidation is enabled through the DB2_DDL_SOFT_INVAL registry variable; by default, this registry variable is set to ON.

The following list shows the data definition language (DDL) statements for which soft invalidation is supported:

ALTER TABLE...DETACH PARTITION
CREATE OR REPLACE ALIAS
CREATE OR REPLACE FUNCTION
CREATE OR REPLACE TRIGGER
CREATE OR REPLACE VIEW
DROP ALIAS
DROP FUNCTION
DROP TRIGGER
DROP VIEW

Note: In DB2® Version 9.7 Fix Pack 1 and later releases, ALTER TABLE...DETACH PARTITION performs soft invalidation at all isolation levels on cached statements that directly or indirectly refer to the partitioned table. A subsequent asynchronous partition detach task performs hard invalidation on previously soft invalidated cached statements before converting the detached partition into a stand-alone table.

The DB2_DDL_SOFT_INVAL registry variable does not affect the invalidation done by ALTER TABLE...DETACH PARTITION.

Soft invalidation support applies only to dynamic SQL and to scans done under the cursor stability (CS) and uncommitted read (UR) isolation levels. For the ALTER TABLE...DETACH PARTITION statement, the soft invalidation applies to scans under all isolation levels.

Example

Assume a view called VIEW1 exists. You open a cursor, and run the statement SELECT * from VIEW1. Shortly afterward, the database administrator issues the command DROP VIEW VIEW1 to drop VIEW1 from the database. With hard invalidation, the DROP VIEW statement will be forced to wait for an exclusive lock on VIEW1 until the SELECT transaction has finished. With soft invalidation, the DROP VIEW statement is not given an exclusive lock on the view. The view is dropped, however, the SELECT statement will continue to run using the most recent definition of the view. Once the SELECT statement has completed, any subsequent attempts to use to VIEW1 (even by the same user or process that just used it) will result in an error (SQL0204N).