indexrec - Index re-creation time configuration parameter
- 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
- 8 restart [ restart;
8 restart_no_redo;
8 access;
8 access_no_redo ]
- Windows Database Manager
- 8 restart [
8 restart;
8 restart_no_redo;
8 access;
8 access_no_redo ]
- Database
- 8 Use system setting
8 [ system; restart; restart_no_redo; access; access_no_redo ]
8 This parameter indicates when the database manager will attempt to rebuild invalid
8 indexes, and whether or not any index build will be redone during DB2 rollforward
8 or HADR log replay on the secondary database.
8 There are five possible settings for this parameter:
8
8
8 - SYSTEM
8 - 8 use system setting specified in the database manager
8 configuration file
8 to decide when invalid indexes will be rebuilt, and whether any index build log records are to be redone
8 during DB2 rollforward or HADR log replay.
8 (Note: This setting is only valid for database configurations.)8
8
8 - ACCESS
8 - 8 Invalid indexes are rebuilt when the index is first accessed.
8 Any fully logged index builds are redone during DB2 rollforward or HADR log replay.
8 When HADR is started and an HADR takeover occurs, any invalid indexes are rebuilt after
8 takeover when the underlying table is first accessed.8
8
8
8 - ACCESS_NO_REDO
8 - Invalid indexes will be rebuilt when the underlying table is first accessed.
8 Any fully logged index build will not be redone during DB2 rollforward or
8 HADR log replay and those indexes will be left invalid.
8 When HADR is started and an HADR takeover takes place, any invalid indexes will be rebuilt after
8 takeover when the underlying table is first accessed.
8
8 8
8 - RESTART
8 - The default value for indexrec.
8 Invalid indexes will be rebuilt when a RESTART DATABASE command is either explicitly or implicitly issued.
8 Any fully logged index build will be redone during DB2 rollforward or HADR log replay.
8 When HADR is started and an HADR takeover takes place, any invalid indexes will be rebuilt at the end
8 of takeover.
8
8
8
8
Note that a RESTART DATABASE command is implicitly
8 issued if the autorestart parameter is enabled.
8 8 8
8 - RESTART_NO_REDO
8 - Invalid indexes will be rebuilt when a RESTART DATABASE command is either explicitly or implicitly issued.
8 (A RESTART DATABASE command is implicitly issued if the
8 autorestart parameter is enabled.)
8
8 Any fully logged index build will not be redone during DB2 rollforward or HADR log replay and instead
8 those indexes will be rebuilt when rollforward completes or when HADR takeover takes place.
8
8
8
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 re-creating 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 re-creation, 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.
8 Setting this parameter to "ACCESS" or to "ACCESS_NO_REDO"
8 will result in a degradation of
8 the performance of the database manager while the index is being re-created.
8 Any user accessing that specific index or table would have to wait
8 until the index is recreated.
If this parameter is set to "RESTART", the time taken to restart the
database will be longer due to index re-creation, but normal processing would
not be impacted once the database has been brought back online.
Note:
7
7 At database recovery time, all SQL procedure executables on
7 the file system that belong to the database being recovered are
7 removed.
7 If indexrec is set to RESTART, all SQL procedure executables
7 are extracted from the database catalog and put back on the file
7 system at the next connection to the database.
7 If indexrec is not set to RESTART, an SQL executable is
7 extracted to the file system only on first execution of that SQL
7 procedure.
8 The difference between the RESTART and the RESTART_NO_REDO
8 values, or between the ACCESS and the ACCESS_NO_REDO values, is only significant when
8 full logging is activated for index build operations, such
8 as CREATE INDEX and REORG INDEX operations, or for an index rebuild.
8 You can activate logging by enabling the logindexbuild
8 database configuration parameter or
8 by enabling LOG INDEX BUILD when altering a table.
8 By setting indexrec to either RESTART or ACCESS, operations involving a
8 logged index build can be rolled
8 forward without leaving the index object in an invalid state, which would
8 require the index to be rebuilt at a later time.
This topic can be found in: Administration Guide: Performance.