Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Overview of this book

With lots of new features, DB2 9.7 delivers one the best relational database systems in the market. DB2 pureXML optimizes Web 2.0 and SOA applications. DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms. This DB2 9.7 Advanced Application Developer Cookbook will provide an in-depth quick reference during any application's design and development. This practical cookbook focuses on advanced application development areas that include performance tips and the most useful DB2 features that help in designing high quality applications. This book dives deep into tips and tricks for optimized application performance. With this book you will learn how to use various DB2 features in database applications in an interactive way.
Table of Contents (15 chapters)
IBM DB2 9.7 Advanced Application Developer Cookbook
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface

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.