The recover and backup utilities supplied by DB2 takes the burden of database recovery form the system administrator and places it with the DBMS. The DB2 recovery and backup utilities were discussed in the previous chapter. The standard tools of DB2 recovery are the image copy backups, the DB2 log tapes and internal DB2 tables and data sets.

The RECOVER utility is invoked to restore the tablespace data. DB2 uses all the information it stores in active and archive logs, the DB2 catalog, the DB2 directory and the BSDS to recover tablespace data with minimum of user input. The only input the RECOVER utility requires is the name of the tablespace to be recovered. DB2 does the rest. The reduction of user input in a recovery situation minimizes the possibility of errors during a potentially hectic and confusing time.


COMMIT and ROLLBACK are not really database operations at all, in the sense that SELECT, UPDATE, etc., are database operations. The COMMIT and ROLLBACK statements are not instruction to the DBMS. They are instructions to the transaction manager and the transaction manager is not a part of the DBMS. In fact the DBMS is subordinate to the transaction manager and DBMS is just one of the several 'resource managers' that provide services to transactions or applications that are running under the transaction manager. In the case of DB2, there are several transaction managers corresponding to the several different environments in which the DB2 applications operate. For example, a transaction running under IMS can use the services of three resource managers: IMS/DB, IMS/DC and DB2. IMS acts as the transaction manager in this case. A transaction running under CICS can also use the services of the above mentioned resource managers. Here CICS is the transaction manager. A transaction running under TSO or CAF can use the services of DB2 only and in this case DB2 itself acts as the transaction manager.

If there is a transaction that updates both an IMS database and a DB2 database. If that transaction is successful then it should update or COMMIT data in both the data bases, similarly if the transaction abends then all its updates must be rolled back. It should not be possible to commit the data in one database and roll back in the other. To avoid this the transaction issues a single, system-wide COMMIT or ROLLBACK to the appropriate transaction manager, and the transaction manager in turn guarantees that all resources are either committed or rolled back. That is why the DBMSs are subordinate to the transaction manager. COMMIT and ROLLBACK must be global operations and the transaction manager acts as the necessary central control point to ensure that this is so.

Thus COMMIT and ROLLBACK operations are requested in different ways in different environments. In TSO batch environment, where DB2 is the transaction manager, commit and roll back are requested via SQL statements COMMIT and ROLLBACK.

Before getting into the details of commit and rollback, there is the concept of synchronization point or 'syncpoint' that should be understood. A syncpoint represents a boundary point between two transactions. It corresponds to the end of a logical unit of work and therefore a point in which the database is in a state of consistency. Program initiation, COMMIT, and ROLLBACK cam establish syncpoints.

The syntax for COMMIT is COMMIT [WORK]; When this command is issued a successful end-of-transaction is signaled and a syncpoint is established. All updates made by the programs at the last syncpoint are committed. All open cursors except the cursors with 'with hold' option, are closed. All page locks are released. The optional word 'WORK' has no meaning and has no effect on the execution but is required by SQL standard. The syntax for ROLLBACK is ROLLBACK [WORK];

An un-successful end-of-transaction is signaled and a syncpoint is established. All updates made since the previous syncpoint are undone. All open cursors are closed. All page locks are released. Here also the 'WORK' has no meaning.

Recovery Process

Probably almost every reader of this book has lost some data and work at one time or other by losing a file or table or database. If you had neglected to take a backup, then you know the frustrations and rework that you had to do to work that you have lost. Accidents and problems are inevitable. According to Murphy, disaster will strike at the least expected time. So you must always be prepared against it. So the system administrator needs to prepare to be able to recover the data losses. There are various processes by which a DBMS tries to protect the data.

The flight log or flight recorder commonly known as the black box in an aircraft record what goes on in the cockpit during a flight. A almost indestructible box houses the flight recorder to protect the log if the plane crashes. After the crash the investigators can use the flight log to reconstructs the events or incidents that caused the crash and discover what went wrong.

DB2 also keeps a similar log of changes that occur in a database. Each time a user uses an SQL statement that makes a change to the database or tables, it is recorded in the log. When the log becomes full it is archived and a new log is started. DB2 uses this log to recover form different failures that might occur. You can use the RECOVER utility to do the recovery process.

Backup and Recovery is an exercise performed to meet any contingency that may arise in future. Planning for disaster recovery, however, is a complex task in the best of the situations. DB2 provides a means of recovering data to its current state in the event of a system failure. The units of recovery are tablespaces and datasets. It is fairly easy to co-ordinate recovery when DB2 distributed feature is not used, as there is only one location involved. If DB2 distributed data facility is used, recovery at all locations to which the subsystem has access to should be coordinated to ensure integrity of data.

The principal tools for recovery are the DB2 provided utilities—QUIESCE, REPORT, COPY, RECOVER and MERGECOPY.

To ensure that a tablespace can be recovered to a specific point, a copy should be existing at some earlier state. This is called a backup copy. DB2 records all changes made to the data in the tablespace in its recovery log. If DB2 fails, it can recover data to current state by restoring the tablespace using the backup copy and applying all changes from the log. Image copies and archive logs are the essential elements of any recovery process

Recovery Log

The DB2 log registers data changes and significant events as they occur. DB2 writes each log record to a DASD dataset called the active log. When active log is full, DB2 copies its contents to a DASD or tape dataset called the archive log. This process is called off-loading.

DB2 log records everything it needs in order to backout for recovery. For INSERT, it records entire row. For an UPDATE, it logs entire row before and after update. Following are the steps involved in creating log records.

DB2 registers changes to data and significant events in recovery log records. DB2 processes recovery log records and breaks them into segments, if necessary. Log records are placed sequentially in output log buffers, which are formatted as VSAM control intervals. Each log record is identified by a continuously increasing RBA. The Control Intervals are written to a set of pre-defined DASD active log datasets, which are used sequentially and recycled. The log buffers are written to an active log dataset when they become full, when the write threshold is reached (an installation parameter), or, more often, when the DB2 subsystem forces the log buffer to be written (such as, at commit time). In the last case, the same control interval can be written several times to the same location. As each active log dataset becomes full, its contents are automatically loaded to a new archive log dataset.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics