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.
The reason why the check might not occur because of the distinct difference between a database
RESTART and a database
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 to
ON(this is the default setting).
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
db2stop<] force, the database is brought to a consistent point with open transactions committed or rolled back. When there is an abnormal termination of the database, the database might be in an inconsistent state triggering the implicit [
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.
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.