DB2 V9.7 for Linux/Unix/Windows supports automatic revalidation of objects which is controlled by the database configuration value AUTO_REVAL. If requested by database administrator, this feature for example revalidates a views which was temporary invalid due to a DROP and re-CREATE of a base table or view. But administrators should play attention: Autoamtic revalidation may lead to lock timeouts (SQL0913N) under certain circumstances!

Example: You’ve DROP’ped and re-CREATE’d a table which is part of a view. Without automatic revalidation that view would be not usable and marked as INVALID. Usually you would have to re-CREATE the view. But if you’ve activated automatic revalidation (AUTO_EVAL [IMMEDIATE, , DEFERRED, DEFERRED_FORCE]) DB2 checks whether the invalidated object may be used IMMEDIATE, or when the first process/user try to access the object, so revalidation takes place in mode DEFERRED.

As long as no parallel processing requires an access to that object, auto revalidation works fine. But concurrent processing fails with SQL0913N because exclusive X-locks are hold until the end of the unit-of-work which has initiated the revalidation.

Example:

Typical DB2 9.7 lock report:

LOCK TIMEOUT REPORT 

Date:               02/06/2012 
Time:               00:46:25 
Instance:           db2ixp 
Database:           DBPXXXX 
Database Partition: 0 

Lock Information: 

  Lock Name:      0000006900003701736A884043 
  Lock Type:      Internal - Catalog Cache 
  Lock Specifics: Tablespace ID=0, Table ID=105, Row ID=00003701, Additional Info=736A8840 

Lock Requestor: 
  System Auth ID:          MYUSER   
  Application Handle:      [0-21584] 
  Application ID:          *LOCAL.db2ixp.120601224529 
  Application Name:        myprog 
  Requesting Agent ID:     29477 
  Coordinator Agent ID:    29477 
  Coordinator Partition:   0 
  Lock timeout Value:      60000 milliseconds 
  Lock mode requested:     ..S 
  Application Status:      (SQLM_UOWEXEC) 
  Current Operation:       (SQLM_PREPARE) 
  Lock Escalation:         No 

Context of Lock Request:  Not available. 

Lock Owner (Representative):   
  System Auth ID:          MYUSER
  Application Handle:      [0-21573] 
  Application ID:          *LOCAL.db2ixp.120601224414 
  Application Name:        myprog2 
  Requesting Agent ID:     13687 
  Coordinator Agent ID:    13687 
  Coordinator Partition:   0 
  Lock mode held:          ..X 

  List of Active SQL Statements:   

     Entry:                  #1 
     Identification:         UOW ID (1); Activity ID (1) 
     Package Schema:         (NULLID  ) 
     Package Name:           (SYSSH200NULLID  ) 
     Package Version:        () 
     Section Entry Number:   4 
     SQL Type:               Dynamic 
     Statement Type:         DML, Select (blockable) 
     Effective Isolation:    Cursor Stability 
     Statement Unicode Flag: No 
     Statement:              SELECT ... FROM MYVIEW1, MYVIEW2 WHERE ... ORDER BY ...

  List of Inactive SQL Statements from current UOW: Not available

Solution:

In case of concurrent processing you have to control and initiate the revalidation yourself:

1. determine invalidates objects, e.g. views:

select * from syscat.invalidobjects where objecttype = 'V';

1. initiate revalidation yourself

call sysproc.admin_revalidate_db_objects('VIEW', NULL, NULL);