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.

db2set DB2_DDL_SOFT_INVAL=ON db2stop db2start 

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:

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.