IBM Support What's New?

A clarification on INDEXREC=RESTART behaviour.

Technote (troubleshooting)


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 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 AUTORESTART parameter is set to ON (this is the default setting).
  • The ACTIVATE DATABASE command 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 [ RESTART 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.

Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.5, 9.7, 9.8, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: Advanced Enterprise Server, Advanced Workgroup Server, Enterprise Server, Express, Express-C, Personal, Workgroup Server

Reference #: 1664301

Modified date: 2014-02-18