IBM Books

Administration Guide

Factors Affecting Recovery

To decide which database recovery method to use, you must consider the following key factors:

In general, a database maintenance and recovery strategy should ensure that all information is available when it is required for database recovery. The strategy should include a regular schedule for taking database backups, as well as scheduled backups when a database is created, or in the case of a partitioned database system, when the system is scaled by adding or dropping database partition servers (nodes). In addition to these basic requirements, a good strategy will include elements that reduce the likelihood and impact of database failure.

The following topics provide additional information:

While the general focus of this chapter is on the database, your overall recovery planning should also include recovering:

Recoverable and Non-Recoverable Databases

If you can re-create data easily, the database holding that data is a candidate to be a non-recoverable database. For example:

If you cannot re-create data easily, then the database holding that data is a candidate to be a recoverable database. The following are examples of data that should be part of a recoverable database:

Database Logs

All databases have logs associated with them. These logs keep records of database changes.

Active logs are used by crash recovery to prevent a failure (system power, application error) from leaving a database in an inconsistent state. The RESTART DATABASE command uses the active logs, if needed, to move the database to a consistent and usable state by means of crash recovery. During crash recovery, changes recorded in these logs that were made to the data but not committed because of the failure are rolled back. Changes that were committed but were not physically written from memory (buffer pool) to disk (database containers) are redone. These actions ensure the integrity of the database. The ROLLFORWARD command may also use the active logs, if needed, during a point-in-time recovery or a recovery to the end of the logs. Active logs are located in the database log path directory.

Archived logs are used specifically for roll-forward recovery. They can be:

Online archived logs
When all changes in the active log are no longer needed for normal processing, the log is closed, and becomes an archived log. An archived log is said to be online when it is stored in the database log path directory.

Offline archived logs
You also have the ability to store archived logs in a location other than the database log path directory, by using a user exit program. (See Appendix L. "User Exit for Database Recovery" for additional information.) An archived log is said to be offline when it is no longer found in the database log path directory.

Roll-forward recovery can use both archived logs and active logs to rebuild a database either to the end of the logs, or to a specific point in time. The roll-forward function achieves this by reapplying committed changes that are found in the archived and active logs to the restored database.

Roll-forward recovery can also use logs to rebuild a table space by re-applying committed updates in both archived and active logs. You can recover a table space to the end of the logs, or to a specific point in time.

Figure 28. Active and Archived Database Logs in Roll-forward Recovery


Two database configuration parameters allow you to change where archived logs are stored: the newlogpath parameter and the userexit parameter. Changing the newlogpath parameter also affects where active logs are stored. For more information, see "Change the Database Log Path (newlogpath)" and "User Exit Enable (userexit)".

To determine which log extents in the database log path directory are archived logs, check the value of the database configuration parameter loghead. This parameter indicates the lowest numbered log that is active. Those logs with sequence numbers less than loghead are archived logs and can be moved. For more information, see "Log Head Identification (loghead)".


  1. If you erase an active log, the database becomes unusable and must be restored before it can be used again. Also, you will be able to roll forward the changes from the logs only up to the first log that was erased.

  2. If you are concerned that your active logs may be damaged (due to a disk crash), you should consider mirroring the volumes on which the logs are stored. By having multiple copies of the logs, you will not lose any transactions, which may happen when active logs are damaged.

Reducing Logging on Work Tables

If your application creates and populates work tables from master tables, and you are not concerned about the recoverability of these work tables because they can be easily re-created from the master tables, you may want to create the work tables with the NOT LOGGED INITIALLY parameter of the CREATE TABLE statement. The advantage of using the NOT LOGGED INITIALLY parameter is that any changes made on the table (including Insert, Delete, Update, or Create Index operations) in the same unit of work that creates the table will not be logged. This not only reduces the logging that is done, but also obtains better performance for your application. You can also obtain the same behavior for existing tables by using the ALTER TABLE statement with the NOT LOGGED INITIALLY parameter.


  1. You can create more than one table with the NOT LOGGED INITIALLY parameter in the same unit of work.

  2. Changes to the catalog tables and other user tables are still logged.

Because changes to the table are not logged, you should consider the following when deciding to use the NOT LOGGED INITIALLY parameter:

See the SQL Reference for more information about creating tables.

Point of Recovery

The version and roll-forward recovery methods provide different points of recovery. The version method involves making an offline, full database backup copy of the database at scheduled times. With this method, the backup copy of the database is only as current as the time that the last backup was made. For instance, if you make a backup copy at the end of each day and you lose the database midway through the next day, you will lose a half-day's worth of changes.

In the roll-forward recovery method, changes made to the database are retained in logs. With this method, you first restore the database or table space(s) using a backup copy; then you use the logs to reapply changes that were made to the database since the backup copy was created.

With roll-forward recovery enabled, you can take advantage of online backup and table space level backup. For full database and table space roll-forward recovery, you can choose to recover to the end of the logs or to a specified point-in-time. For instance, if an application corrupted the database, you could start with a restored copy of the database, and roll-forward changes up until just before that application started. All units of work in the logs after the time specified will not be reapplied.

You can also roll forward table spaces to the end of the logs, or to a specific point in time. For more information about rolling forward table spaces, see "Rolling Forward Changes in a Table Space".

Frequency of Backups and Time Required

Your recovery plan should allow for regularly scheduled backups, since backing up a database requires time and system resource.

You should take full database backups regularly, even if you archive the logs (which allows for roll-forward recovery). If your recovery strategy includes roll-forward recovery, a recent full database backup will mean that there are fewer archived logs to apply to the database, which reduces the amount of time required by the ROLLFORWARD utility to recover the database.

You should also consider not overwriting backups and logs, saving more than one full database backup and its associated logs as an extra precaution.

You can do a backup while the database is either online or offline. If it is online, other applications or processes can continue to connect to the database as well as read and modify data while the backup task is running. If the backup is performed offline, only the backup task can be connected to the database. The implication of offline backup is that the rest of your organization cannot connect to the database while the backup task is running.

To reduce the time when the database is not available, consider using online backups. Online backups are supported only if roll-forward recovery is enabled. If roll-forward recovery is enabled and you have a complete set of logs, you can rebuild the database should the need arise.


  1. You can only use an online backup if you have the database log (or logs) that span the time that backup operation both started and completed.

  2. Offline backups are faster than online backups.

If a database contains large amounts of long field and LOB data, backing up the database could be very time-consuming. The BACKUP command provides the capability to back up selected table spaces. If you use DMS table spaces, you can store different types of data in their own table spaces to reduce the time required for backups. You can keep table data in one table space, the LONG and LOB data in another table space, and the INDEX data in another table space. By storing long field and LOB data in separate table spaces, the time required to complete the back up of the data can be reduced by choosing not to back up the table spaces containing the long field and LOB data. If the long field and LOB data is critical to your business, backing up these table spaces should be considered against the time required to complete the restore task for these table spaces. If the LOB data can be reproduced from a separate source then, when creating or altering a table to include LOB columns, choose the NOT LOGGED option.

If you reorganize a table, you should back up the affected table spaces after the operation completes. If you have to restore the table spaces, you will not have to roll forward through the data reorganization.
Note:If you back up a table space that contains table data without the table spaces containing the associated the LONG or LOB fields, you cannot perform point-in-time roll-forward recovery on that table space. All the table spaces that contain any type of data for a table must be rolled forward simultaneously to the same point in time.

Recovery Time Required

The time required to recover a database is made up of two parts: the time required to complete the restore of the backup; and, if the database is enabled for forward recovery, the time required to apply the logs during the roll-forward operation. When formulating a recovery plan, you should determine what is a reasonable amount of time for your business operations to be impacted while the database is being recovered.
Note:The setting of the enable intra-partition parallelism (intra_parallel) database manager configuration parameter does not affect the performance of either backup or restore operations. Multiple processes will be used for both of these operations, regardless of the setting of the intra_parallel parameter.

Testing your overall recovery plan will assist you in determining whether the time required to recover the database is reasonable given your business requirements. Following each test, you may want to increase the frequency with which you take a backup. If roll-forward recovery is part of your strategy, this will reduce the number of logs that are archived between backups and, as a result, reduce the time required to roll forward the database after a restore.

Storage Considerations

When deciding which recovery method to use, consider the storage space required.

The version recovery method requires space to hold the backup copy of the database and the restored database. The roll-forward recovery method requires space to hold the backup copy of the database or table spaces, the restored database, and the archived database logs.

If a table contains long field or large object (LOB) columns, you should consider placing this data into a separate table space. This will affect your storage space considerations as well as affect your plan for recovery. With a separate table space for long field and LOB data, and knowing the time required to back up long field and LOB data, you may decide to use a recovery plan that only infrequently saves a backup of this long field/LOB table space. You may also choose, when creating or altering a table to include LOB columns, not to log changes to that column. This will reduce the size of the log space required and the corresponding log archive space.

The backup of an SMS table space which contains LOBs can be bigger than the size of the original table space. The backup can be as much as 40 per cent larger depending on the LOB data size in the table space. For example, if you take a backup of a 1GB SMS table space (with LOBs), you will need more than 1GB of disk space when you restore it. This situation only occurs on file systems that support sparse allocation (for example, UNIX operating systems).

To prevent a media failure from destroying a database and your ability to rebuild it, you should keep the database backup, the database logs, and the database itself on different devices. For this reason, it is highly recommended that you use the newlogpath configuration parameter to put database logs on a separate device once the database is created. (This and other configuration parameters related to logging are discussed in "Rolling Forward Changes in a Database".)

Because the database logs can take a large amount of storage, if you plan on using the roll-forward recovery method you must decide how to manage the archived logs. Your choices are the following:

  1. Dedicate enough space in the database log path directory to retain the logs.
  2. Manually copy the logs to a storage device or directory other than the database log path directory after they are no longer in the active set of logs.
  3. Use a user exit program to copy these logs to another storage device in your environment. (See Appendix L. "User Exit for Database Recovery" for more information.)
Note:Under OS/2, the database manager supports a user exit program to handle the storage of both backup copies of databases and database logs on standard and non-standard devices. See Appendix L. "User Exit for Database Recovery" for more information.

Keeping Related Data Together

As part of your database design, you will know the relationships that exist between tables. These relationships can be at the application level, where transactions update more than one table, or at the database level, where referential integrity exists between tables, or where triggers on one table affect another table. You should consider these relationships when developing a recovery plan. You will want to back up related sets of data together. The sets of data can be established at either the table space or the database level. By keeping related sets of data together, you can recover to a point where all of the data is consistent. This is especially important if you want to be able to perform point-in-time roll-forward recovery on table spaces.

Recovery Performance Considerations

The following items should be considered when thinking about recovery performance:

It is also recommended that you monitor and measure within your own system environment. The recommendations are only a starting point: each business and each environment is unique.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]