Troubleshooting
Problem
The DB or DBM configuration parameter INDEXREC controls when the DB2® product checks for invalid indexes in a database. If this parameter is set to RESTART, there are cases when this check does not occur if the database is activated.
Cause
The reason why the check might not occur because of the distinct difference between a database RESTART and a database ACTIVATION .
A database restart takes place explicitly when the RESTART DATABASE command is executed.
An implicit restart also takes place when the following conditions are met:
- The db cfg setting the
AUTORESTARTparameter is set toON(this is the default setting). - The
ACTIVATE DATABASEcommand is executed or the first connection to the database is established. - The database is in a an inconsistent state.
In particular this latter condition is important. When a database is cleanly shut down (for example, through a DB2 deactivate database), when the last connection terminates, or through a
db2stopRESTART DATABASE.
The fact that database activation does not always trigger a database restart means that the INDEXREC=RESTART logic will not always executed when the database is brought online. The risk of this action leading to the the indexes being left invalid is minimal.
Indexes are not routinely marked as invalid. An index might be marked as invalid by operations like an index reorg or a load command on a table. However if the commands fail, then the database will most likely abort and be left in an inconsistent state which leads to the implicit restart.
Resolving The Problem
The only scenario where a database could have invalid indexes after database activation, is when they were explicitly marked as invalid using the db2dart <db> /MI command.
The db2dart <db> /MI command is occasionally used, typically instructed by support, in order to avoid a problem related to the existing indexes.
When the database is consistent, and the db2dart <db> /MI command is used to mark indexes as invalid, then a database activation will not trigger the implicit restart, so that running the INDEXREC=RESTART command does not check these indexes.
The alternative is that the first application accessing the table will start rebuilding the index in the same way as the INDEXREC=ACCESS configuration setting.
The recommend approach when db2dart <db> /MI was used against a table is hence :
- Explicitly restart the database via the
db2 RESTART DATABASE <db name>command - or run
db2 "select count(*) from <table>"in order to ensure indexes are recreated.
The progress of the index rebuilding can be monitored in the db2diag.log file where a message is written after each index of the object has been recreated.
Note that the scope of the RESTART DATABASE command is partition specific. In a DPF environment you might have to run the db2_all "db2 RESTART DATABASE <db>" command in order to apply this on all database partitions.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21664301