Understanding DB2: Learning Visually with Examples (2nd Edition)

13.1. Database Recovery Concepts

Database recovery is how DB2 makes your database consistent in the event of a failure. A database is "consistent" when all committed transactions have been applied to the database and any uncommitted transactions that might have been performed have been rolled back.

13.1.1. Recovery Scenarios

To minimize the loss of your data, you need to have a recovery strategy, ensure that it works, and constantly practice it. The following are some recovery scenarios you should consider.

• System outage: A power failure, hardware failure, or software failure can cause your database to be in an inconsistent state.

• Transaction failure: Users may inadvertently corrupt your database by modifying it with incorrect data or delete useful data.

• Media failure: If your disk drive becomes unusable, you may lose all or part of your data.

• Disaster: The facility where your system is located may be damaged by fire, flooding, or other catastrophe.

13.1.2. Recovery Strategies

To plan your recovery strategy, ask yourself:

• Can the data be loaded from another source?

• How much data can we afford to lose?

• How much time can we spend recovering the database?

• What storage resources are available for storing backups and log files?

13.1.3. Unit of Work (Transactions)

A unit of work (UOW), also known as a transaction, consists of one or more SQL statements that end with a COMMIT or ROLLBACK statement. All of the statements inside this UOW are treated as a unit, which ensures data consistency. A typical example to explain this concept is that of a customer trying to transfer $100 from his savings account to his checking account. The UOW in this case would include all three of the following:

DELETE 100 dollars from SAVINGS account INSERT 100 dollars to CHECKING account COMMIT

If these statements are not treated as a unit and a hardware failure occurs after the DELETE and before the INSERT, then this person loses $100! Since the statements are treated as a unit, this will never happen because DB2 knows that the unit did not complete as a COMMIT was not issued. When the system is restarted after the failure, DB2 will ROLLBACK the statements, meaning it will bring the database back to the state prior to beginning of the transaction.

NOTE

An analogy for understanding the COMMIT statement is to compare it to the Save button in word processing software. When you click this button, you expect your text document to be saved. Changes made after you save the document are lost if your server crashes, but what was saved will remain on disk. Similarly, when you issue a COMMIT statement, changes made to the database are saved. If your server crashes, anything that was committed can be recovered, and anything that was not will be lost.

13.1.4. Types of Recovery

There are three types of recovery in DB2:

• Crash recovery

• Version recovery

• Roll forward recovery

Each of these types of recovery is discussed in detail in the next sections.

13.1.4.1 Crash Recovery

Crash recovery protects a database from being left in an inconsistent state following an abnormal termination. An example of an abnormal termination is a power failure. Using the banking example, if a power failure occurs prior to the COMMIT statement, the next time DB2 is restarted and the database accessed, DB2 will ROLLBACK the INSERT statement, followed by the DELETE statement. Note that statements are rolled back in reverse order, not in the order they were originally executed. This ensures that the data is consistent, and that the person still has the $100 in his savings account.

By default, DB2 automatically initiates crash recovery when a database is accessed for the first time following an abnormal termination. You can disable the automatic crash recovery by setting the database configuration parameter AUTOSTART to OFF. If you do that, you will need to perform crash recovery manually using the RESTART DATABASE command. If you do not restart the database manually in the event of a system crash, you will receive the following error when you try to connect to the database:

[View full width]

13.1.4.2 Version Recovery

Version recovery allows for the restoration of a previous version of a database using a backup image created with the BACKUP command. The restored database will be in exactly the same state it was in when the BACKUP command was executed. If further activity was performed against the database after this backup was taken, those updates are lost. For example, assume you back up a database and then create two tables, table1 and table2. If you restore the database using the backup image, your restored database will not contain the two new tables.

13.1.4.3 Roll Forward Recovery

Roll forward recovery extends version recovery by using full database backups in conjunction with log files. A backup must be restored first as a baseline, and then logs are applied on top of this backup image. Therefore, changes you made after you backed up the database can be applied to the restored database. Using the previous example, with roll forward recovery you have three choices to restore your database:

• You can restore the database using only the backup image. This is identical to version recovery. In this case, the restored database will not contain table1 and table2.

• You can restore the database using the backup image, and then roll forward the logs to the point when table1 was created. In this case, the restored database will contain table1 but not table2.

• You can restore the database using the backup image, and then roll forward the logs all the way to the end of the logs. In this case, the restored database will contain both table1 and table2.

By default, crash recovery and version recovery are enabled. You will learn how to enable roll forward recovery in Section 13.2.4, Logging Methods.