Database Recovery with DB2 for OS/2

By Chris Fierros IBM Corporation Kansas City, Missouri

'As databases grow larger and move closer to 7x24 (7 days a week, 24 hours a day) availability, database administrators increasingly face challenges to implement  database recovery strategies within smaller windows of opportunity. To ensure success for these database administrators, DB2 for OS/2 V2.1 provides significant enhancements in  database recovery. This article discusses those enhancements. '

IBM's DB2 for OS/2 V2.1 continues to meet database administrators' expectations for database recovery. This new version includes improvements in fault tolerance, backup and restore processes, and recovery management.

Improved Fault Tolerance
DB2 for OS/2 V2.1 introduces the concept of a database table space. A database table space can be viewed as a logical database partition, much like a logical partition on a disk drive.

A table space can be allocated as either a system-managed space (SMS) or a database-managed space (DMS). An SMS table space is simply a table space managed by the operating system's file system, in this case OS/2. A DMS table space, on the other hand, is managed by DB2 for OS/2 and has some recovery advantages over an SMS table space.

One advantage of a database table space is that it increases the database's fault tolerance to media failures. In previous releases of DB2 for OS/2, a database that failed to restart after a media error would have to be completely restored from a backup copy. In DB2 for OS/2 V2.1, the damage is isolated at the table space level, and the table space is placed in a "pending roll forward" state. This provides two important functions. First, it gives the database administrator the opportunity to recover at the table space level. Second, it allows users to continue to access all other table spaces in the database. In the event of a total disk failure, only those table spaces located on the failed disk are marked as damaged. Once the disk has been replaced, all affected table spaces will have to be restored from a backup copy and will require roll forward recovery.

A DMS table space provides even more fault tolerance by allowing table data, index data, and long field (LONG) data or large object (LOB) data to be separated into different table spaces. Using a DMS table space in this manner provides fault tolerance at the table component level by further isolating the impact of a media error to table components, such as index data.

Improved Backup/Restore
As databases grow larger and move closer to 7x24 availability, the time required to perform critical backup and recovery procedures continues to grow while database  maintenance windows continue to shrink. To support large databases with high availability, DB2 for OS/2 V2.1 introduces the concept of parallel  backup/restore procedures and increases the granularity at which backup/restore  procedures can be performed.

In previous versions of DB2 for OS/2, the backup and restore procedures were executed serially. This meant that only one input/output (I/O) device could be used to back up or restore the database at any given time. It therefore required several hours to back up a very large database, especially if the backup were made to a slow media device such as a tape unit.

A parallel backup/restore procedure will reduce this time significantly, because multiple I/O buffers and multiple I/O devices can be used to back up or restore a single database. To support multiple I/O buffers and multiple I/O devices, a new backup application programming interface (API) (SQLUBKUP) and a new restore API (SQLURSTO) have been introduced.



Improved Database Recovery
In previous versions of DB2/2, the backup procedure supports only two levels of database backup, either the "entire database" or "changes only." The backup procedure is invoked via the database backup API. The backup API can be invoked with the "changes only" option only after an "entire database" backup has been performed. The restore process is invoked via the database restore API. The restore API can be invoked with the option to either replace the existing database or create a new database. This API also supports the option to place the database in a "pending roll forward" state after the restore has  completed.

Both the backup and restore database APIs can be accessed from the Recovery Tool or the Command Line Interface (CLI). They can also be called directly from C, COBOL, or the REXX programming language.

To support a table space-level recovery, the backup API has been modified to accept a list of database table spaces. During recovery, all table spaces in the backup image must be restored and then rolled forward to the end of the database logs. A DMS table space further increases the granularity at which a database can be recovered. This particular type of table space is very significant when considering the impact of storing binary large objects (BLOBs) in databases.

Table Recovery
In addition to database table spaces, DB2 for OS/2 V2.1 also introduces a table-level restore with the LOAD utility.

The LOAD utility provides a high-speed, table-level restore that can load data from multiple I/O devices or files. It is intended for processing initial table loads and large table appends. Unlike database and table space recovery, data restored using the LOAD utility cannot be rolled forward. However, if an error occurs during load processing, the load process itself can be restarted and/or recovered. The LOAD utility supports delimited ASCII, non-delimited ASCII, and PC/IXF file formats.

Improved Recovery Management
As managing database recovery becomes more complex, the database recovery features of DB2 for OS/2 continue to be enhanced. To help database administrators DB2 for OS/2 V2.1 introduces a recovery history file that includes database backup and restore  procedures.

Stored with each database, the recovery history file provides summary information at the database level. It is automatically updated by DB2 for OS/2 when a database backup, restore, or table load is performed. Using the recovery history file, database administrators identify which portions of the database have been backed up, when they were backed up, and where the backups are located.

Significant Enhancements
IBM's DB2 for OS/2 provides database administrators with significant recovery enhancements. Features such as improved fault tolerance through the implementation of system-managed spaces or database-managed spaces, parallel backup and restore  procedures with multiple I/O devices and multiple I/O buffers, and table-level recovery  provide database administrators with the edge needed to stay one step ahead of the next  disaster!