Using the soft invalidation and automatic revalidation support

In the earlier versions of DB2, whenever an object was altered or dropped, an exclusive lock was applied to ensure that no user accessed the object. This locking resulted in lock-waits or the rolling back of the transaction because of the deadlocks.

Getting ready

We need the SYSADM authority to modify the values for DB2 registry variables.

How to do it...

To enable or disable soft invalidation at the instance level, use the DB2 registry variable, DB2_DDL_SOFT_INVAL.

• To enable soft invalidation at the instance level, set the value of the DB2_DDL_SOFT_INVAL registry variable to ON.

db2set DB2_DDL_SOFT_INVAL=ON
db2stop
db2start

• To disable soft invalidation at the instance level, set the value of the DB2_DDL_SOFT_INVAL registry variable to OFF.

db2set DB2_DDL_SOFT_INVAL=OFF
db2stop
db2start

How it works...

In DB2 9.7, we have the soft invalidation feature to avoid these lock-waits or deadlocks. Upon activating soft invalidation using the registry variable DB2_DDL_SOFT_INVAL=ON in any transaction, the DDL operations, such as DROP TABLE, ALTER TABLE, and DETACH partitions on database objects will not be stuck because of a lock-wait (SQL0911N Reason Code 68) or a deadlock (SQL0911N Reason Code 2) while the modifying objects are being accessed by other transactions. This is because the current transaction will continue to access the original object definition while the new transaction will make use of the changed object definition of ALTER, DROP, or DETACH if the object being accessed is altered. During the DROP statement, the current transaction would still see the object until the completion of the execution of the transaction and all new transactions would fail to find the dropped object. This way, DB2 9.7 improves the application concurrency for DDL statements.

The following is the list of DDL statements for which soft invalidation is supported in DB2 9.7:

• CREATE OR REPLACE ALIAS

• CREATE OR REPLACE FUNCTION

• CREATE OR REPLACE TRIGGER

• CREATE OR REPLACE VIEW

• DROP ALIAS

• DROP FUNCTION

• DROP TRIGGER

• DROP VIEW

There's more...

As discussed in the earlier recipe, DB2 9.7 supports automatic object revalidation, based on the database configuration parameter's AUTO_REVAL setting.

Normally, the object would get revalidated whenever the application or the user accesses the invalid object, if AUTO_REVAL is set to DEFERRED. If we set AUTO_REVAL to IMMEDIATE, the objects get revalidated immediately after they become invalid.