DB2 FOR LUW / DB2FORLUW / 970 - DB2

A problem in the index compression algorithm, triggered during 
the creation of a compressed index, may result in the creation 
of a corrupted index. The corruption may occur either during an 
index creation or a reorg operation. It will take the form of a 
corrupted index tree node (page). Table data will remain intact. 
 
 
The symptoms may include: 
- Index key/data mismatch or key not found error (SQLI_NOKEY) 
- Bad page error (SQLB_BADPAGE), or 
- Programming error (SQLI_PRG_ERR) 
- Error from function sqliCalcFreeSpaceFromInTotFreeSp, probe 
  4411. 
 
These errors will cause a transaction to fail, and will be 
logged in db2diag.log. 
 
Applying this APAR will prevent future index corruption. Indexes 
which are already corrupted as a result of this problem need to 
be rebuilt as described in the local fix section.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of DB2 index compression.                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* The problem occurred because incorrect index compression     * 
* metadata was used during insertion into compressed index.    * 
* The index compression metadata was made inconsistent during  * 
* index creation. There was a broken dependency between the    * 
* prefix optimization function and the calling function during * 
* index creation. Namely, during index creation the prefix     * 
* optimization function is called to determine which prefix to * 
* use for a given index page. The function determines there is * 
* no value in compressing the page, but the caller does not    * 
* update the metadata to reflect this. As a result, incorrect  * 
* information was used when the insertion into this page       * 
* occurred, which ended up corrupting the page.                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Install DB2 V9.7 Fixpack 1 or follow the workaround          * 
* documented in the Local Fix section.                         * 
****************************************************************

Users may work around this issue either by creating the index 
on an empty table and then populating it using LOAD, or by 
recreating the index without compression. Each of the following 
methods describes a way to work around the problem. 
 
 
Method #1: 
========== 
1. Turn off the index compression feature 
   db2set DB2_INDEX_COMPRESSION=OFF 
 
2. Restart the instance for the registry variable to take 
   effect. 
 
3. Drop and recreate the Index. 
 
Method #2: 
========== 
1. Drop and recreate the index with explicit COMPRESS NO option. 
 
Method #3: 
========== 
1. Alter the index and set COMPRESS NO option: 
 
    ALTER INDEX <indname> COMPRESS NO 
 
2. Mark the index bad using db2dart utility: 
 
    db2dart <dbname> /mi /tsi <tablespaceID> /oi <objectid> 
 
Step 2 has the following requirements: 
- The database must be off-line. 
- Two input values must be supplied: index tablespaceID and 
objectID. For regular tables, these values can be obtained using 
INDEX_OBJECTID and TBSPACEID from SYSCAT.INDEXES. For 
partitioned indexes, these values can be obtained using 
INDPARTITIONOBJECTID and INDPARTITIONTBSPACEID from 
SYSCAT.INDEXPARTITIONS. 
 
Method #4: 
========== 
1. Export table data into a file 
export to t1.del of del messages t1exp.msg select * from t1 
 
2. Drop the table 
drop table t1 
 
3. Create a new empty table 
create table t1(...) 
 
4. Create a compressed index on the empty table 
create index i1 on t1(...) compress yes 
 
5. Use LOAD "insert into" and "indexing mode incremental" 
options to populate the table/index. 
 
load from t1.del of del 
messages t1load.msg 
insert into t1 
indexing mode incremental 
 
 
Notes: 
- Methods #1 and #2 causes the index storage to be reallocated 
which might increase performance impact during recreation. 
 
- Method #3 reuses existing index storage which has a lesser 
performance impact. 
 
- Method #4 also has a greater impact on performance, but it 
enables you to preserve index compression.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
 

not known / see Local fix

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

26.08.2009
17.11.2009
26.11.2009

9.7.FP1