You should consider setting the database configuration
parameters logindexbuild and indexrec for
high availability disaster recovery (HADR) databases.
Using the logindexbuild database
configuration parameter
Recommendation: For HADR
databases, set the logindexbuild database configuration
parameter to ON to ensure that complete information is logged for
index creation, re-creation, and reorganization. Although this means
that index builds might take longer on the primary system and that
more log space is required, the indexes will be rebuilt on the standby
system during HADR log replay and will be available when a failover
takes place. Otherwise,
when replaying an index build or rebuild event, the standby marks
the index invalid, because the log records do not contain enough information
to populate the new index. If index builds on the primary system
are not logged and a failover occurs, any invalid indexes that remain
after the failover is complete have to be rebuilt before they can
be accessed. While the indexes are being re-created, they cannot be
accessed by any applications.
Note: If the LOG INDEX BUILD table
attribute is set to its default value of NULL, DB2® uses the value specified for the logindexbuild
database configuration parameter. If the LOG INDEX BUILD table attribute
is set to ON or OFF, the value specified for the logindexbuild database
configuration parameter is ignored.
You might choose to
set the LOG INDEX BUILD table attribute to OFF on one or more tables
for either of the following reasons:
- You do not have enough active log space to support logging of
the index builds.
- The index data is very large and the table is not accessed often;
therefore, it is acceptable for the indexes to be re-created at the
end of the takeover operation. In this case, set the indexrec configuration
parameter to RESTART. Because the table is not frequently accessed,
this setting causes the system to re-create the indexes at the end
of the takeover operation instead of waiting for the first time the
table is accessed after the takeover operation.
If the LOG INDEX BUILD table attribute
is set to OFF on one or more tables, any index build operation on
those tables might cause the indexes to be re-created any time a
takeover operation occurs. Similarly, if the LOG INDEX BUILD table
attribute is set to its default value of NULL, and the
logindexbuild database
configuration parameter is set to OFF, any index build operation
on a table might cause the indexes on that table to be re-created
any time a takeover operation occurs. You can prevent the indexes
from being re-created by taking one of the following actions:
- After all invalid indexes are re-created on the new primary database,
take a backup of the database and apply it to the standby database.
As a result of doing this, the standby database does not have to
apply the logs used for re-creating invalid indexes on the primary
database, which would mark those indexes as rebuild required on the
standby database.
- Set the LOG INDEX BUILD table attribute to ON, or set the LOG
INDEX BUILD table attribute to NULL and the logindexbuild configuration
parameter to ON on the standby database to ensure that the index
re-creation will be logged.
Using the indexrec database configuration
parameter
Recommendation: Set the indexrec database
configuration parameter to RESTART (the default) on both the primary
and standby databases. This causes invalid indexes to be rebuilt
after a takeover operation is complete. If any index builds have not
been logged, this setting allows DB2 to
check for invalid indexes and to rebuild them. This process takes
place in the background, and the database is accessible after the
takeover operation has completed successfully.
If a transaction
accesses a table that has invalid indexes before the indexes have
been rebuilt by the background re-create index process, the invalid
indexes are rebuilt by the first transaction that accesses it.
DB2 Version 10.1 for Linux, UNIX, and Windows