This chapter is from the book
This chapter is from the book
This chapter is from the book
Backup and Recovery
Although crash recovery can be used to resolve inconsistency problems that result from power interruptions and/or application failures, it cannot be used to handle problems that arise when the storage media being used to hold a database's files becomes corrupted or fails. In order to handle these types of problems, some kind of backup (and recovery) program must be put in place.
A database recovery strategy should include a regular schedule for making database backup images and, in the case of partitioned database systems, include making backup images whenever the system is scaled (i.e., whenever database partition servers are added or dropped). In addition, the strategy used should ensure that all information needed is available when database recovery is necessary and it should include procedures for recovering command scripts, applications, user-defined functions (UDFs), stored procedure code in operating system libraries, and load copies as well as database data. To help with such a strategy, DB2 UDB provides three utilities that are used to facilitate backing up and restoring a database. The utilities are:
-
The BACKUP utility
-
The RESTORE utility
-
The ROLLFORWARD utility
The DB2 UDB BACKUP Utility
The single most important item you can possess that will prevent catastrophic data losses in the event storage media becomes corrupted or fails is a database backup image. A database backup image is essentially a copy of an entire database that includes both its objects and its data. Once created, a backup image can be used at any time to return a database to the exact state it was in at the time the backup image was made (version recovery). A good database recovery strategy should ensure that backup images are created on a regular basis, and that backup copies of critical data are retained in a secure location and on different storage media from that used to store the database itself. Depending on the logging method used (circular or archival), database backup images can be made when a database is offline or while other users and applications are connected to it (online). (In order to backup a database while it is online, archival logging must be used.)
A backup image of a DB2 UDB database (or of a tablespace within a DB2 UDB database) can be created by executing the BACKUP command. The basic syntax for this command is:
BACKUP [DATABASE | DB] [DatabaseName] <USER [UserName] <USING [Password]>> <TABLESPACE ( [TS_Name],...) <ONLINE> <INCREMENTAL <DELTA>> <TO [Location]> <WITH [NumBuffers] BUFFERS> <BUFFER [BufferSize]> <PARALLELISM [ParallelNum]> <WITHOUT PROMPTING>
where:
|
DatabaseName |
Identifies the name assigned to the database that a backup image is to be created for. |
|
UserName |
Identifies the name assigned to a specific user whose authority the backup operation is to be performed under. |
|
Password |
Identifies the password that corresponds to the name of the user that the backup operation is to be performed under. |
|
TS_Name |
Identifies the name assigned to one or more specific tablespaces that are to be backed up. |
|
Location |
Identifies the directory or device where the backup image created is to be stored. (If no location is specified, the current location is used as the default.) |
|
NumBuffers |
Identifies the number of buffers that are to be used to perform the backup operation. (By default, two buffers are used if this option is not specified.) |
|
BufferSize |
Identifies the size, in pages, that each buffer used to perform the backup operation will be. (By default, the size of each buffer used by the BACKUP utility is determined by the value of the backbufsz DB2 Database Manager configuration parameter.) |
|
ParallelNum |
Identifies the number of tablespaces that can be read in parallel during a backup operation. |
If the INCREMENTAL option is specified, an incremental backup image will be produced. An incremental backup image is a copy of all data that has changed since the last successful, full backup image was produced. Likewise, if the DELTA option is specified, a delta backup image will be produced. A delta backup image is a copy of all data that has changed since the last successful backup image of any type (full, incremental, or delta) was produced.
Thus, if you wanted to create a backup image of a database named SAMPLE and store the image created in a directory named BACKUPS on logical disk drive E:, you could do so by executing a BACKUP command that looks something like this:
BACKUP DATABASE SAMPLE USER DB2ADMIN USING IBMDB2 TO E:\BACKUPS
On the other hand, if you wanted to create an incremental backup image of a tablespace named TBSP1 and store the image created in a directory named BACKUPS on logical disk drive E: while the database it is associated with (named SAMPLE) remains online, you could do so by executing a BACKUP command that looks something like this:
BACKUP DATABASE SAMPLE USER DB2ADMIN USING IBMDB2 TABLESPACE (TBSP1) ONLINE INCREMENTAL TO E:\BACKUPS
Keep in mind that tablespace backup images can only be created if archival logging is being used; if circular logging is used instead, tablespace backups are not supported.
You can also create a backup image of a database or one or more tablespaces using the Backup Wizard, which can be activated by selecting the Backup action from the Databases menu found in the Control Center. Figure 7-8 shows the Control Center menu items that must be selected to activate the Backup Wizard; Figure 7-9 shows how the first page of the Backup Wizard might look immediately after it is activated.
)
Figure 7-8. Invoking the Backup Wizard from the Control Center.
)
Figure 7-9. The first page of the Backup Wizard.
Note
Only users with System Administrator (SYSADM) authority, System Control (SYSCTRL) authority, or System Maintenance (SYSMAINT) authority are allowed to backup a database or any of its tablespaces.
The Recovery History File
In Chapter 3—Data Placement, we saw that a special file, known as the recovery history file, is created as part of the database creation process. This file is used to log historical information about specific actions that are performed against the database it is associated with. Specifically, records are written to the recovery history file whenever any of the following are performed:
-
A backup image of any type is created.
-
A version recovery operation is performed either on the database or on one of its tablespaces.
-
A table is loaded using the LOAD utility.
-
A roll-forward recovery operation is performed either on the database or on one of its tablespaces.
-
A tablespace is altered.
-
A tablespace is quiesced.
-
Data in a table is reorganized using the REORG utility.
-
Statistics for a table are updated using the RUNSTATS utility.
-
A table is created, renamed, or deleted (dropped).
In addition to identifying the event that was performed, each entry in the recovery history file identifies the date and time the event took place, how the event took place, the tablespaces and tables that were affected, and the location of the backup image created (if the action was a backup operation), along with information on how to access this image. (In a moment, we'll look at how this information is used when a version recovery operation is performed.)
Because the recovery history file sits quietly in the background and the DB2 Database Manager is responsible for managing its contents, a database administrator rarely has to interact with it. However, two commands are available that provide a way to both view the contents of a database's recovery history file and to remove one or more entries stored in it. You can view the contents of a database's recovery history file by executing the LIST HISTORY command from the DB2 Command Line Processor (CLP). The basic syntax for this command is:
LIST HISTORY <BACKUP| ROLLFORWARD| DROPPED TABLE | LOAD | CREATE TABLESPACE | ALTER TABLESPACE | RENAME TABLESPACE | REORG> [ALL | SINCE [Timestamp] | CONTAINING <SchemaName.>ObjectName] FOR [DATABASE | DB] [DatabaseName]
where:
|
Timestamp |
Identifies a timestamp that is to be used as search criteria when retrieving entries from the recovery history file; only entries with timestamps that are greater than or equal to the timestamp provided are retrieved and displayed. |
|
SchemaName |
Identifies the name assigned to the schema that is to be used as search criteria when retrieving entries from the recovery history file; only entries that are associated with the schema name specified are retrieved and displayed. |
|
ObjectName |
Identifies the name assigned to an object that is to be used as search criteria when retrieving entries from the recovery history file; only entries that are associated with the object specified are retrieved and displayed. |
|
DatabaseName |
Identifies the name assigned to the database that recovery history file information is to be retrieved and displayed for. |
So, if you wanted to display all entries found in the recovery history file for a database named SAMPLE, you could do so by executing a LIST HISTORY command that looks something like this:
LIST HISTORY ALL FOR DATABASE SAMPLE
And when such a LIST HISTORY command is executed, output that looks something like the following might be produced (assuming the SAMPLE database has been backed up):
List History File for SAMPLE
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ ------------
B D 20030817204927001 F D S0000000.LOG S0000000.LOG
-------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
-------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20030817204927
End Time: 20030817204957
-------------------------------------------------------------------------
00001 Location: C:\Backup\SAMPLE.0\DB2\NODE0000\CATN0000\20030817
You can delete a recovery history file entry by executing the PRUNE HISTORY command. The basic syntax for this command is:
PRUNE HISTORY [Timestamp] <WITH FORCE OPTION>
where:
|
Timestamp |
Identifies a timestamp that is to be used as search criterion when removing entries from the recovery history file; only entries with timestamps that are less than or equal to the timestamp provided are deleted, provided they are not part of the most recent restore set. |
If the WITH FORCE OPTION option is specified, entries with timestamps that are less than or equal to the timestamp specified are deleted regardless of whether or not they are part of the most recent restore set.
Thus, if you wanted to remove all recovery history log file entries that were made prior to and including January 1, 2002, regardless of whether or not they are part of the most recent restore set, you could do so by executing a PRUNE HISTORY command that looks something like this:
PRUNE HISTORY 20020101 WITH FORCE OPTION
It is important to note that where the LIST HISTORY command requires you to provide the name of the database whose recovery history file is to be queried, the PRUNE HISTORY command requires that you establish a connection to the appropriate database before attempting to remove one or more of its recovery history file entries.
The DB2 UDB RESTORE Utility
Earlier, we saw that version recovery is the process that returns a database to the state it was in at the time a backup image was made. This means that in order for version recovery to be available, at least one backup image must exist and be available. And since the recovery history file contains image location information for each backup image available, it acts as a tracking and verification mechanism during version recovery operations; each backup image contains special information in its header and this information is compared to the records stored in a database's recovery history file to determine whether or not a particular backup image is associated with the database that is to be recovered.
So just how is a recovery operation initiated? The most common way is by executing the RESTORE command. The basic syntax for this command is:
RESTORE [DATABASE | DB] [DatabaseName] <USER [UserName] <USING [Password]>> <TABLESPACE <ONLINE> | TABLESPACE ( [TS_Name] ,... ) <ONLINE> | HISTORY FILE <ONLINE>> <INCREMENTAL <AUTO | AUTOMATIC | ABORT>> <FROM [SourceLocation]> <TAKEN AT [Timestamp]> <TO [TargetLocation]> <INTO [TargetAlias]> <NEWLOGPATH [LogsLocation]> <WITH [NumBuffers] BUFFERS> <BUFFER [BufferSize]> <REPLACE EXISTING> <REDIRECT> <PARALLELISM [ParallelNum]> <WITHOUT ROLLING FORWARD> <WITHOUT PROMPTING>
or
RESTORE [DATABASE | DB] [DatabaseName] [CONTINUE | ABORT]
where:
|
DatabaseName |
Identifies the name assigned to the database that is associated with the backup image that is to be used to perform a recovery operation. |
|
UserName |
Identifies the name assigned to a specific user that the recovery operation is to be performed under. |
|
Password |
Identifies the password that corresponds to the name of the user that the recovery operation is to be performed under. |
|
TS_Name |
Identifies the name assigned to one or more specific tablespaces that are to be restored from a backup image. |
|
SourceLocation |
Identifies the directory or device where the backup image to be used is stored. |
|
Timestamp |
Identifies a timestamp that is to be used as search criterion when looking for a particular backup image to use for recovery. (If no timestamp is specified there must be only one backup image at the source location specified.) |
|
TargetLocation |
Identifies the directory where the database that will be created is to be stored, if the backup image is to be used to create a new database. |
|
TargetAlias |
Identifies the alias to be assigned to the new database to be created. |
|
LogsLocation |
Identifies the directory or device where log files for the new database are to be stored. |
|
NumBuffers |
Identifies the number of buffers that are to be used to perform the recovery operation. (By default, two buffers are used if this option is not specified.) |
|
BufferSize |
Identifies the size, in pages, that each buffer used to perform the backup operation will be. (By default, the size of each buffer used by the RESTORE utility is determined by the value of the restbufsz DB2 Database Manager configuration parameter.) |
|
ParallelNum |
Identifies the number of tablespaces that can be read in parallel during a backup operation. |
Thus, if you wanted to restore a database named SAMPLE (which already exists and uses circular logging), using a backup image stored in a directory named BACKUPS on logical disk drive E:, you could do so by executing a RESTORE command that looks something like this:
RESTORE DATABASE SAMPLE USER DB2ADMIN USING IBMDB2 FROM E:\BACKUPS REPLACE EXISTING WITHOUT PROMPTING
On the other hand, if you wanted to restore just a tablespace named TBSP1 in a database named SAMPLE from an incremental backup image stored in a directory named BACKUPS on logical disk drive E: while the database is online, you could do so by executing a RESTORE command that looks something like this:
RESTORE DATABASE SAMPLE USER DB2ADMIN USING IBMDB2 TABLESPACE (TBSP1) ONLINE INCREMENTAL FROM E:\BACKUPS
Each full database backup image contains, among other things, a copy of the database's recovery history file. However, when an existing database is restored from a full database backup image, the existing recovery history file is not overwritten. But what if the recovery history file for the database happens to be corrupted? Can the recovery history file be restored as well since a copy exists in the database backup image? The answer is yes. A special form of the RESTORE command can be used to restore just the recovery history file from a database backup image. Such a RESTORE command might look something like this:
RESTORE DATABASE SAMPLE USER DB2ADMIN USING IBMDB2 HISTORY FILE FROM E:\BACKUPS
It is also possible to create an entirely new database from a full database backup image, effectively cloning an existing database. Thus, you could create a new database named SAMPLE_2 that is an exact duplicate of a database named SAMPLE, using a backup image stored in a directory named BACKUPS on logical disk drive E: by executing a RESTORE command that looks something like this:
RESTORE DATABASE SAMPLE USER DB2ADMIN USING IBMDB2 FROM E:\BACKUPS INTO SAMPLE_2
It is important to note that if a backup image is used to create a new database, the recovery history file stored in the backup image will become the recovery history file for the new database.
You can also perform any of the restore/recovery operations just described (along with many others) using the Restore Data Wizard, which can be activated by selecting the Restore action from the Databases menu found in the Control Center. Figure 7-10 shows the Control Center menu items that must be selected to activate the Restore Data Wizard; Figure 7-11 shows how the first page of the Restore Data Wizard might look immediately after it is activated.
)
Figure 7-10. Invoking the Restore Data Wizard from the Control Center.
)
Figure 7-11. The first page of the Restore Data Wizard.
Note
Only users with System Administrator (SYSADM) authority, System Control (SYSCTRL) authority, or System Maintenance (SYSMAINT) authority are allowed to restore a database or any of its tablespaces from a backup image; only users with SYSADM authority or SYSCTRL authority are allowed to create a new database from a backup image.
Redirected Restore
A full backup image of a database contains, among other things, information about all tablespaces that have been defined for the database, including specific information about each tablespace container being used at the time the backup image was made. During a recovery operation, a check is performed to verify that all tablespace containers referenced by the backup image exist and are accessible. If this check determines that one or more of the tablespace containers needed is no longer available or is no longer accessible, the recovery operation will fail and the database will not be restored. When this happens, any invalid tablespace containers encountered can be redefined at the beginning of the recovery process by performing what is referred to as a redirected restore operation.
Redirected restore operations are performed by executing the RESTORE command with the REDIRECT option specified, followed by one or more SET TABLESPACE CONTAINERS commands, followed by the RESTORE command with the CONTINUE option specified. The basic syntax for the SET TABLESPACE CONTAINERS command is:
SET TABLESPACE CONTAINERS FOR [TS_ID] USING [( PATH '[Container]' ,... ) | ( [FILE | DEVICE] '[Container]' [ContainerSize] ,... )]
where:
|
TS_ID |
Identifies the identification number assigned to the tablespace that new storage containers are to be provided for. |
|
Container |
Identifies one or more containers that are to be used to store the data associated with the tablespace specified. |
|
ContainerSize |
Identifies the number of pages to be stored in the tablespace container specified. |
The steps used to perform a redirected restore operation are as follows:
-
Start the redirected restore operation by executing the RESTORE command with the REDIRECT option specified. (When this option is specified, each invalid tablespace container encountered is flagged, and all tablespaces that reference invalid tablespace containers are placed in the "Restore Pending" state. A list of all tablespaces affected can be obtained by executing the LIST TABLESPACES command.) At some point, you should see a message that looks something like this:
SQL1277N Restore has detected that one or more table space containers are inaccessible, or has set their state to 'storage must be defined'. DB20000I The RESTORE DATABASE command completed successfully.
-
Specify new tablespace containers for each tablespace placed in "Restore Pending" state by executing a SET TABLESPACE CONTAINERS for each appropriate tablespace. (Keep in mind that SMS tablespaces can only use PATH containers, while DMS tablespaces can only use FILE or DEVICE containers.)
-
Complete the redirected restore operation by executing the RESTORE command with the CONTINUE option specified.
To simplify things, all of these steps can be coded in a UNIX shell script or a Windows batch file, which can then be used to perform the redirected restore operation. Such a file might look something like this:
db2 "RESTORE DATABASE SAMPLE FROM C:\BACKUPS TO D:\DB_DIR INTO SAMPLE_2 REDIRECT" db2 "SET TABLESPACE CONTAINERS FOR 0 USING (PATH 'D:\DB_DIR\SYSTEM')" db2 "SET TABLESPACE CONTAINERS FOR 1 USING (PATH 'D:\DB_DIR\TEMP')" db2 "SET TABLESPACE CONTAINERS FOR 2 USING (PATH 'D:\DB_DIR\USER')" db2 "RESTORE DATABASE SAMPLE CONTINUE"
You can also perform a redirected restore by assigning new tablespace containers to existing tablespaces on the Containers page of the Restore Data Wizard. Figure 7-12 shows how this is used to assist in a redirected restore operation.
)
Figure 7-12. Performing a redirected restore using the Containers page of the Restore Data Wizard.
Note
In addition to providing new storage containers for tablespaces when older tablespace containers are inaccessible or are no longer present, a redirected restore can also be used to add new containers to existing SMS tablespaces. (The ALTER TABLESPACE command does not allow you to add new storage containers to existing SMS tablespaces; a redirected restore provides a workaround to this limitation.)
The DB2 UDB ROLLFORWARD Utility
When a backup image is used to restore a damaged or corrupted database, the database can only be returned to the state it was in at the time the backup image was made. Therefore, all changes that were made to the database after the backup image was created will be lost when a recovery operation is performed. To return a database to the state it was in at any given point in time, roll-forward recovery must be used instead. And in order to perform a roll-forward recovery operation, the database must be recoverable (that is, archival logging must be the logging strategy used), you must have a full backup image of the database available, and you must have access to all archived log files that will be needed to perform the roll-forward recovery operation.
Roll-forward recovery starts out as a recovery operation. However, where a recovery operation will leave a non-recoverable database in a "Normal" state, the same operation will leave a recoverable database in "Roll-forward pending" state. (When a recoverable database is restored from a backup image, it is automatically placed in "Roll-forward pending" state unless the WITHOUT ROLLING FORWARD option is used with the RESTORE command; while a database is in "Roll-forward pending state, it cannot be accessed by users and applications.) At that point, the database can either be taken out of "Roll-forward pending" state (in which case all changes made to the database since the backup image used for recovery was made will be lost), or information stored in the database's transaction log files can be replayed to return the database to the state it was in at any given point in time.
Note
Once a database is taken out of "Roll-forward pending" state, it cannot be manually returned to that state again. Therefore, if you discover that a database was taken out of "Roll-forward pending" state prematurely and you need to return it to that state, you must restore the database again using an appropriate backup image.
The process of replaying transactions stored in archived log files is known as "rolling the database forward" and one way to roll a database forward is by executing the ROLLFORWARD command. The basic syntax for this command is:
ROLLFORWARD [DATABASE | DB] [DatabaseName] <USER [UserName] <USING [Password]>> <TO [PointInTime] <USING LOCAL TIME> <AND COMPLETE | AND STOP> | TO END OF LOGS <AND COMPLETE | AND STOP> | COMPLETE | STOP | CANCEL | QUERY STATUS <USING LOCAL TIME>> <TABLESPACE ONLINE | TABLESPACE <( [TS_Name] ,... )> <ONLINE>> <OVERFLOW LOG PATH ( [LogDirectory] ,... )> <RECOVER DROPPED TABLE [TableID] TO [Location]>
where:
|
DatabaseName |
Identifies the name assigned to the database that is to be rolled forward. |
|
UserName |
Identifies the name assigned to a specific user that the roll-forward operation is to be performed under. |
|
Password |
Identifies the password that corresponds to the name of the user that the roll-forward operation is to be performed under. |
|
PointInTime |
Identifies a specific point in time, identified by a timestamp value in the form yyyy-mm-dd-hh.mm.ss.nnnnnn (year, month, day, hour, minutes, seconds, microseconds) that the database is to be rolled forward to. (Only transactions that took place before and up to the time specified will be reapplied to the database.) |
|
TS_Name |
Identifies the name assigned to one or more specific tablespaces that are to be rolled forward. |
|
LogDirectory |
Identifies the directory that contains offline archived log files that are to be used to perform the roll-forward operation. |
|
TableID |
Identifies a specific table (by ID) that was dropped earlier that is to be recovered as part of the roll-forward operation. (The table ID can be obtained by examining the database's recovery history file.) |
|
Location |
Identifies the directory where files containing data that was stored in the table that was dropped are to be written to when the table is recovered as part of the roll-forward operation. |
If the AND COMPLETE, AND STOP, COMPLETE, or STOP option is specified, the database will be returned to "Normal" state when the roll-forward operation has completed. Otherwise, the database will remain in "Roll-forward pending state". (When a recoverable database is restored from a backup image, it is automatically placed in "Roll-forward pending" state unless the WITHOUT ROLLING FORWARD option is used with the RESTORE command; while a database is in "Roll-forward pending state, it cannot be accessed by users and applications.)
Thus, if you wanted to perform a roll-forward recovery operation on a database named SAMPLE and take it out of "Roll-forward pending" state, you could do so by executing a ROLLFORWARD command that looks something like this:
ROLLFORWARD DATABASE SAMPLE TO END OF LOGS AND STOP
On the other hand, if you wanted to perform a roll-forward recovery operation on a database named SAMPLE by reapplying all transactions that were committed at or before 01/01/2003, you could do so by executing a ROLLFORWARD command that looks something like this:
ROLLFORWARD DATABASE SAMPLE TO 2003-01-01-00.00.00.0000 AND STOP
It is important to note that the time value specified is interpreted as a Coordinated Universal Time (UTC), otherwise known as Greenwich Mean Time (GMT), value. If a ROLLFORWARD command that looks something like this had been executed instead:
ROLLFORWARD DATABASE SAMPLE TO 2003-01-01-00.00.00.0000 USING LOCAL TIME AND STOP
The time value specified would have been interpreted as a local time value.
You can also initiate a roll-forward recovery operation using the Rollforward Wizard, which can be activated by selecting the Roll-forward action from the Databases menu found in the Control Center. Figure 7-13 shows the Control Center menu items that must be selected to activate the Rollforward Wizard; Figure 7-14 shows how the first page of the Rollforward Wizard might look immediately after it is activated.
)
Figure 7-13. Invoking the Rollforward Wizard from the Control Center.
)
Figure 7-14. The first page of the Rollforward Wizard.
Because a roll-forward recovery operation is typically performed immediately after a database is restored from a backup image, a roll-forward recovery operation can also be initiated by providing the appropriate information on the Roll forward page of the Restore Data Wizard. Figure 7-15 shows how the Roll forward page of the Restore Data Wizard might look after its input fields have been populated.
)
Figure 7-15. The Roll forward page of the Restore Data Wizard.
Note
Only users with System Administrator (SYSADM) authority, System Control (SYSCTRL) authority, or System Maintenance (SYSMAINT) authority are allowed to perform a roll-forward recovery operation.
Rebuilding Invalid Indexes
So far we have looked at ways to recover data in the event the storage media being used to hold a database's files becomes corrupted or fails. But what if only indexes are damaged and a database's data is unaffected (which could be the case if data and indexes are stored in separate DMS tablespaces and only the tablespace container where index data is stored fails)? In this case, the affected indexes are invalidated and can be recovered by being recreated once the faulty media has been replaced.
Whenever the DB2 Database Manager detects that an index is no longer valid, it automatically attempts to rebuild it. However, the point in time at which the DB2 Database Manager attempts to rebuild an invalid index is controlled by the indexrec parameter of the database or the DB2 Database Manager configuration file. There are three possible settings for this parameter:
-
SYSTEM. Invalid indexes are to be rebuilt at the time specified in the indexrec parameter of the DB2 Database Manager configuration file. (This setting is only valid for database configuration files.)
-
RESTART. Invalid indexes are to be rebuilt when crash recovery is performed on the database (i.e., when the database is restarted).
-
ACCESS. Invalid indexes are to be rebuilt the first time they are accessed after they have been marked as being invalid.
So when is the best time to rebuild invalid indexes? If the time it takes to perform a crash recovery operation on a database is not a concern, it is better to let the DB2 Database Manager rebuild invalid indexes while it is returning a database to a consistent state; the time needed to restart a database will be longer due to the index recreation process, but normal processing will not be affected. On the other hand, if indexes are rebuilt as they are accessed, crash recovery can be performed faster, but users may experience an initial decrease in performance; references made to tables that contain associated invalid indexes will have to wait for the invalid index(es) to be rebuilt. Furthermore, unexpected locks may be acquired and held long after an invalid index has been recreated, especially if the transaction that caused the index recreation to occur is not committed (or rolled back) for quite some time.