Symptoms:
There are several possible symptoms.
a) The following message could be seen when attempting to access
the table for the first time after the database has been
deactivated:
SQL1655C The operation could not be completed due to an error
accessing data on disk. SQLSTATE=58030
The diag.log will contain the following and an FODC_BadPage
directory will be created.
FUNCTION: DB2 UDB, buffer pool services, sqlb_verify_page,
probe:2
MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad"
DIA8400C A bad page was encountered.
DATA #1 : String, 64 bytes
Error encountered trying to read a page - information follows :
DATA #2 : String, 23 bytes
Page verification error
Upon formatting the page dumped in the FODC_BadPage
directory the page dumped may be all zeroes.
b) Dropping the index on the table could fail with the
following written to the diag.log:
FUNCTION: DB2 UDB, data management, sqldmdpi, probe:195
MESSAGE : Unable to find index token.
DATA #1 : Hexdump, 2 bytes
0x00002AAAC73F6288 : 0100
..
c) If the index object has been marked bad, either manually
or by rolling forward through an operation that marked it bad,
it's possible that one of the following could happen when an
attempt is made to recreate the index object.
An endless loop could occur with the following being
written to the diag.log repeatedly:
FUNCTION: DB2 UDB, data management, sqldLoadTCB, probe:10717
MESSAGE : ADM5572I One or more indexes on table "KIRTON .T1"
are marked
invalid and require rebuilding.
or a -901 will be returned with the message "numIndexes not
set for index recreation!".
Conditions Required:
Have a table with a not enforced key defined, create one or more
physical indexes on the table and drop all the indexes. The
index object will now be in an inconsistent state. Creating a
new index will appear to be successful but corruption may have
occurred. The corruption will ultimately lead to one of the
symptoms as above. Note that the corrupt object may not be
detected immediately as in some cases use of the table and
inserts into it will succeed, but the index will not be updated. |
If an index was created successfully, then the index object can
be marked bad and will be recreated successfully. After the
indexes are recreated the table must then be altered to drop the
NOT ENFORCED constraint to avoid corrupting the object again.
If the index object has been marked bad and cannot be recreated
successfully then an attempt can be made to drop the table. If
this fails then service will have to assist to mark the table
bad and then drop it.
Alternatively, the database can be restored from a backup and
rolled forward to a point in time before the last index on the
NOT ENFORCED table was dropped. At the point the table could be
altered to drop the NOT ENFORCED constraint and avoid the
corruption.
To Avoid:
Do not use a mix of not enforced keys and indexes on the same
row-organized table. If you have a NOT ENFORCED constraint as
well as one or more indexes defined on a table then alter the
table to drop the NOT ENFORCED key before attempting to drop any
indexes. |
