During a database upgrade to DB2 Version 9.7, index corruption
may be introduced. This is caused by the DB2 code that upgrades
the index root page to Version 9.7 if following conditions are
true:
- the index was created with both the UNIQUE and INCLUDE
keywords
- at least one of the columns specified in the INCLUDE clause of
the CREATE INDEX statement is a variable length column
- the index contains only 1 node (i.e. a root-leaf node)
- there is reserved space on the page
Once the index has been corrupted, it can lead to further
corruption, which results in a number of different index manager
errors and eventually the database/instance is stopping
abnormally. Due to index corruption, subsequent crash recovery
will fail as well.
During IndexError processing of one of these errors, the DB2
database may run db2dart to dump the index page in error.
Db2dart may trap when attempting to format this page, but if it
does not, then the following message will likely appear in the
db2dart report:
Error: Total Record Reserved Space != Page Reserved Space
If db2dart traps during execution, the stack will look like
following:
sqloCrashOnCriticalMemoryValidationFailure
sqlofmblkEx
DartCheckKeys
DartCheckIndex
DartMGRInspectTable
DartMGRMain
main
If db2dart is run with the /DB option immediately after the
upgrade, an error message like this will appear in the report:
Index inspection phase start. Index obj: 4 In pool: 2
Page 2: Error: Incorrect Total Reserved Space
The following query can be used to identify indexes that might
be affected:
select distinct t2.TBSPACEID, t2.TABLEID, t1.IID,
t1.TABSCHEMA, t1.TABNAME, t1.INDSCHEMA, t1.INDNAME
from syscat.indexes as t1,
syscat.tables as t2,
syscat.indexColUse as t3,
syscat.columns as t4
where t1.TABSCHEMA = t2.TABSCHEMA
and t1.TABNAME = t2.TABNAME
and t1.INDSCHEMA = t3.INDSCHEMA
and t1.INDNAME = t3.INDNAME
and t1.TABSCHEMA = t4.TABSCHEMA
and t1.TABNAME = t4.TABNAME
and t4.COLNAME = t3.COLNAME
and t1.UNIQUE_COLCOUNT >= 1
and t1.UNIQUE_COLCOUNT < t1.COLCOUNT
and t3.COLSEQ > t1.UNIQUE_COLCOUNT
and t4.TYPENAME = 'VARCHAR' and t4.TYPESCHEMA = 'SYSIBM'
with ur; |
