DB2 Version 9.7 for Linux, UNIX, and Windows

Soft invalidation and automatic revalidation support added

Soft invalidation is a process whereby access to an object being invalidated is allowed to continue.

Previously, when an object was being altered or dropped, exclusive locking was used to ensure that all users of the object were no longer using it. This locking could result in applications waiting or rolling back because of deadlocks. Soft invalidation, however, avoids these waits, and allows any active access to continue, using the old version of the object. If the object has been recreated, any new access to the object will see the new version of the object; if the object has been dropped, new access to the object is not possible.

Soft invalidation is enabled through a new registry variable named DB2_DDL_SOFT_INVAL; by default, this registry variable is set to on. For more information, see "Miscellaneous variables".

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

CREATE OR REPLACE ALIAS
CREATE OR REPLACE FUNCTION
CREATE OR REPLACE TRIGGER
CREATE OR REPLACE VIEW
DROP ALIAS
DROP FUNCTION
DROP TRIGGER
DROP VIEW

Soft invalidation support applies only to dynamic SQL and to scans done under the cursor stability (CS) and uncommitted read (UR) isolation levels.

In general, the database manager attempts to revalidate invalid objects the next time that those objects are used. However, if the auto_reval database configuration parameter is set to IMMEDIATE, invalid objects are revalidated immediately after they become invalid. For information about the dependent objects that are impacted when an object is dropped, and when those dependent objects are revalidated, see "DROP statement".

The following list shows the data definition language (DDL) statements for which automatic revalidation is currently supported:

ALTER MODULE DROP FUNCTION
ALTER MODULE DROP PROCEDURE
ALTER MODULE DROP TYPE
ALTER MODULE DROP VARIABLE
ALTER NICKNAME (altering the local name or the local type)
ALTER TABLE ALTER COLUMN
ALTER TABLE DROP COLUMN
ALTER TABLE RENAME COLUMN
CREATE OR REPLACE ALIAS
CREATE OR REPLACE FUNCTION
CREATE OR REPLACE NICKNAME
CREATE OR REPLACE PROCEDURE
CREATE OR REPLACE SEQUENCE
CREATE OR REPLACE TRIGGER
CREATE OR REPLACE VARIABLE
CREATE OR REPLACE VIEW
DROP FUNCTION
DROP NICKNAME
DROP PROCEDURE
DROP SEQUENCE
DROP TABLE
DROP TRIGGER
DROP TYPE
DROP VARIABLE
DROP VIEW
RENAME TABLE

Example

Disable soft invalidation support.

db2set DB2_DDL_SOFT_INVAL=off