indexrec - Index re-creation time configuration parameter

This parameter indicates when the database manager attempts to rebuild invalid indexes, and whether or not any index build is redone during rollforward or high availability disaster recovery (HADR) log replay on the standby database.

Configuration type

Database and Database Manager

Applies to

Database server with local and remote clients
Database server with local clients
Partitioned database server with local and remote clients

Parameter type

Configurable Online

Propagation class

Immediate

Default [range]

UNIX Database Manager: restart [restart; restart_no_redo; access; access_no_redo]
Windows Database Manager: restart [restart; restart_no_redo; access; access_no_redo]
Database: Use system setting [system; restart; restart_no_redo; access; access_no_redo]

There are five possible settings for this parameter:

SYSTEM: Use system setting specified in the database manager configuration file to decide when invalid indexes are rebuilt, and whether any index build log records are to be redone during rollforward or HADR log replay.
Note: This setting is only valid for database configurations.
ACCESS: Invalid indexes are rebuilt when the underlying table is first accessed. Any fully logged index builds are redone during rollforward or HADR log replay. When HADR is started and an HADR takeover occurs, any invalid indexes are rebuilt after takeover when the underlying table is first accessed.
ACCESS_NO_REDO: Invalid indexes are rebuilt when the underlying table is first accessed. Any fully logged index build is not redone during rollforward or HADR log replay and those indexes are left invalid. When HADR is started and an HADR takeover takes place, any invalid indexes are rebuilt after takeover when the underlying table is first accessed. Access to the underlying tables on the new primary cause index rebuild, which causes log records to be written and then sent to the new standby, which in turn causes the indexes to be invalidated on the standby.
RESTART: The default value for indexrec. Invalid indexes are rebuilt when a RESTART DATABASE command is either explicitly or implicitly issued. Any fully logged index build will be redone during rollforward or HADR log replay. When HADR is started and an HADR takeover takes place, any invalid indexes will be rebuilt at the end of takeover.
Note: In a Db2® pureScale® environment, indexes are rebuilt only during a group crash recovery, not as part of member crash recovery.

Note: When a database terminates abnormally while applications are connected to it, and the autorestart parameter is enabled, a RESTART DATABASE command is implicitly issued when an application connects to a database. If the database terminates normally, then the RESTART DATABASE command must be issued before any connections are made to the database, otherwise index recreation does not take place. If the command is not issued, the invalid indexes are rebuilt the next time the underlying table is accessed.
RESTART_NO_REDO: Invalid indexes are rebuilt when a RESTART DATABASE command is either explicitly or implicitly issued. Any fully logged index build is not redone during rollforward or HADR log replay and instead those indexes are rebuilt when rollforward completes or when HADR takeover takes place. Takeover causes index rebuild on underlying tables on the new primary, which causes log records to be written and then sent to the new standby, which in turn causes the indexes to be invalidated on the standby.
When a database terminates abnormally while applications are connected to it, and the autorestart parameter is enabled, a RESTART DATABASE command is implicitly issued when an application connects to a database. If the database terminates normally, then the RESTART DATABASE command must be issued before any connections are made to the database, otherwise index recreation does not take place. If the command is not issued, the invalid indexes are rebuilt the next time the underlying table is accessed.

Indexes can become invalid when fatal disk problems occur. If this happens to the data itself, the data could be lost. However, if this happens to an index, the index can be recovered by recreating it. If an index is rebuilt while users are connected to the database, two problems could occur:

An unexpected degradation in response time might occur as the index file is re-created. Users accessing the table and using this particular index would wait while the index was being rebuilt.
Unexpected locks might be held after index recreation, especially if the user transaction that caused the index to be re-created never performed a COMMIT or ROLLBACK.

Recommendation: The best choice for this option on a high-user server and if restart time is not a concern, would be to have the index rebuilt at DATABASE RESTART time as part of the process of bringing the database back online after a crash.

Setting this parameter to ACCESS or to ACCESS_NO_REDO result in a degradation of the performance of the database manager while the index is being re-created. Any user accessing that specific index or table would have to wait until the index is re-created.

If this parameter is set to RESTART, the time taken to restart the database is longer due to index re-creation, but normal processing would not be impacted once the database has been brought back online.

The difference between the RESTART and the RESTART_NO_REDO values, or between the ACCESS and the ACCESS_NO_REDO values, is only significant when full logging is activated for index build operations, such as CREATE INDEX and REORG INDEX operations, or for an index rebuild. You can activate logging by enabling the logindexbuild database configuration parameter or by enabling LOG INDEX BUILD when altering a table. By setting indexrec to either RESTART or ACCESS, operations involving a logged index build can be rolled forward without leaving the index object in an invalid state, which would require the index to be rebuilt at a later time.