DB2 FOR LUW / DB2FORLUW / 970 - DB2

The error might occur when the following criteria are met: 
 
1) The table is an MDC table with at least one RID index. 
 
2) A deferred cleanup rollout delete is performed on that MDC 
table.  The conditions that need to be met for a delete to be 
classified as a rollout delete are: 
   - The DELETE statement is a searched DELETE, not a positioned 
DELETE (the statement does not use the WHERE CURRENT OF clause). 
   - There is no WHERE clause (all rows are to be deleted), or 
the only conditions in the WHERE clause apply to dimensions. 
   - The table is not defined with the DATA CAPTURE CHANGES 
clause. 
   - The table is not the parent in a referential integrity 
relationship. 
   - The table does not have ON DELETE triggers defined. 
   - The table is not used in any MQTs that are refreshed 
immediately. 
   - A cascaded delete operation might qualify for rollout if 
its foreign key is a subset of the table's dimension columns. 
   - The DELETE statement cannot appear in a SELECT statement 
executing against the temporary table that identifies the set of 
affected rows prior to a triggering SQL operation (specified by 
the OLD TABLE AS clause on the CREATE TRIGGER statement). 
 
   The default behavior, immediate cleanup rollout, is to clean 
up RID indexes during the time of deletion. A deferred cleanup 
rollout delete is used when the user sets the DB2_MDC_ROLLOUT 
registry variable to DEFER, or by specifying DEFERRED on the SET 
CURRENT MDC ROLLOUT MODE statement. 
 
Source: 
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.i 
bm.db2.luw.admin.perf.doc/doc/c0007338.html 
 
3) The asynchronous index cleanup operation yields to a LOAD 
command running on the same table.  The load command must use 
the INSERT option and must not specify INDEXING MODE REBUILD or 
INDEXING MODE DEFERRED. 
 
4) After the LOAD command completes, the asynchronous index 
cleanup operation continues.  However, before it completes 
another deferred cleanup rollout delete is performed on the 
table.  Incorrectly, the DB2 software does not consolidate the 
asynchronous index cleanup operations and runs them both 
together because the life LSN of the table is updated by the 
LOAD command. 
 
5) After the first asynchronous index cleanup operation 
completes, it updates the block map incorrectly marking all 
rolled-out blocks as free. Meanwhile, the index cleanup is not 
complete.  This might result in a variety of different index 
errors.  The problem can be detected by running the INSPECT 
command with the INDEXDATA option.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7.0.9.                              * 
****************************************************************

If the problem occurs, rebuild the indexes.  To avoid the 
problem do not use deferred index cleanup.  Make sure the 
following values are NOT set: 
 
SET CURRENT MDC ROLLOUT MODE DEFERRED 
db2set DB2_MDC_ROLLOUT=DEFER 
 
Use the following instead: 
SET CURRENT MDC ROLLOUT MODE IMMEDIATE 
db2set DB2_MDC_ROLLOUT=IMMEDIATE 
OR 
SET CURRENT MDC ROLLOUT MODE NONE 
db2set DB2_MDC_ROLLOUT=

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

The problem is first fixed in DB2 version 9.7.0.9.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC92990 IC93971 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

10.06.2013
16.12.2013
16.12.2013

9.7.0.9