Index Re-creation Time (indexrec)

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
• Satellite database server with local clients

Parameter Type

Configurable

Default [Range]

UNIX Database Manager

restart [ restart; access ]

OS/2 and Windows NT Database Manager

access [ restart; access ]

Database

Use system setting [ system; restart; access ]

Related Parameters

Auto Restart Enable (autorestart)

This parameter indicates when the database manager will attempt to rebuild invalid indexes. There are three possible settings for this parameter:

SYSTEM

use system setting which will cause invalid indexes to be rebuilt at the time specified in the database manager configuration file. (Note: This setting is only valid for database configurations.)

ACCESS

during index access which will cause invalid indexes to be rebuilt when the index is first accessed.

RESTART

during database restart which will cause invalid indexes to be rebuilt when a RESTART DATABASE command is either explicitly or implicitly issued. Note that a RESTART DATABASE command is implicitly issued if the autorestart parameter is enabled.

For the numeric equivalents and API constants for these values, refer to the Administrative API Reference.

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 may 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 may 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.

Setting this parameter to "ACCESS" will 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 re-creating is complete.

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.