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.