Troubleshooting index to data inconsistencies

Indexes must be accurate to allow quick access to the right data in tables otherwise your database is corrupt.

You can use the INSPECT command to carry out an online check for index to data inconsistency by using the INDEXDATA option in the cross object checking clause. Index data checking is not performed by default when using the INSPECT command; it must be explicitly requested.

When there is an error discovered due to an index data inconsistency while INSPECT performs an INDEXDATA inspection, the error message SQL1141N is returned. At the same time this error message is returned, data diagnostic information is collected and dumped to the db2diag log file. An urgent message is also logged in the administration notification log. Use the db2diag log file analysis tool (db2diag) to filter and format the contents of the db2diag log file.

Locking implications

While checking for index to data inconsistencies by using the INSPECT command with the INDEXDATA option, the inspected tables are only locked in IS mode.

When the INDEXDATA option is specified, by default only the values of explicitly specified level clause options are used. For any level clause options which are not explicitly specified, the default levels (INDEX NORMAL and DATA NORMAL) are overwritten from NORMAL to NONE.