DB2 FOR LUW / DB2FORLUW / 970 - DB2

When executing a LOAD INSERT command into an MDC + RP 
(range-partitioned) table, the LOAD operation may insert new 
data into "gaps/holes of unused pages", instead of adding new 
pages beyond the end of existing data. For each partition, if 
all the data entered fits into the "gaps" such that the 
partition is not extended, the LOAD operation may fail to mark 
the partition with the "requires set integrity check" message. 
Subsequently, performing the Set Integrity operation after 
executing LOAD will fail to enforce integrity on the partition's 
data. (Set Integrity will report successful completion, but the 
table partition will still contains rows that violate the Check 
Constraint/Referential Integrity standards). 
 
This problem affects the following constraints managed by Set 
Integrity: (Table) Check constraints, Foreign key (referential) 
constraints and loading into MQTs. 
 
The following constraints are not affected by this issue: NOT 
NULL constraints, Unique constraints, Primary key constraints, 
Informational constraints. 
 
This problem only affects DB2 Version 9.7 fixpack 2, fixpack 3, 
and fixpack 3a.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL users running LOAD INSERT on MDC+RP tables               * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When executing a LOAD INSERT command into an MDC + RP        * 
* (range-partitioned) table, the LOAD operation may insert new * 
* data into "gaps/holes of unused pages", instead of adding    * 
* new pages beyond the end of existing data. For each          * 
* partition, if all the data entered fits into the "gaps" such * 
* that the partition is not extended, the LOAD operation may   * 
* fail to mark the partition with the "requires set integrity  * 
* check" message. Subsequently, performing the Set Integrity   * 
* operation after executing LOAD will fail to enforce          * 
* integrity on the partition's data. (Set Integrity will       * 
* report successful completion, but the table partition will   * 
* still contains rows that violate the Check                   * 
* Constraint/Referential Integrity standards).                 * 
*                                                              * 
* This problem affects the following constraints managed by    * 
* Set Integrity: (Table) Check constraints, Foreign key        * 
* (referential) constraints and loading into MQTs.             * 
*                                                              * 
* The following constraints are not affected by this issue:    * 
* NOT NULL constraints, Unique constraints, Primary key        * 
* constraints, Informational constraints.                      * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 4                        * 
****************************************************************

To check for constraints and referential integrity, you must 
re-validate the entire table: 
 set integrity for <tableName> off 
 set integrity for <tableName> immediate checked not incremental 
for exception in <tableName> use <exceptionTableName> 
 
Any violating rows will be moved to the exception table where 
they can be analyzed and reinserted into the base table, if 
required. 
 
The MQT (both refresh immediate and refresh deferred with 
staging table) will no longer be in sync with the base tables 
and will need to be fully refreshed. To do a full refresh of the 
MQT, you must do one of the following: 
 
a) Perform LOAD REPLACE directly into the MQT via a cursor that 
corresponds to the definition of MQT. This is the recommended 
option, but will not work with a replicated MQT. 
 
b) Perform the following steps to change the integrity and 
refresh the table: 
 set integrity for <mqtTableName> off 
 refresh table <mqtTableName> not incremental 
Note: This will perform a fully-logged delete of the MQT, and 
can be slow for large tables. 
 
c) Drop the MQT, recreate it and then do an initial refresh 
(Note that if MQT has a staging table, it will need to be 
recreated as well.)

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 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
 

Problem was first fixed in Version 9.7 Fix Pack 4

see Local Fix

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

15.03.2011
20.04.2011
20.04.2011

9.7.FP4