DB2 Universal Data Base V8.1 Certification: Backup and Recovery
This chapter is from the book
This chapter is from the book
This chapter is from the book
Fourteen percent (14%) of the DB2 UDB V8.1 for Linux, UNIX, and Windows Database Administration certification exam (Exam 701) and seventeen percent (17%) of the DB2 UDB V8.1 for Linux, UNIX, and Windows Database Administration certification upgrade exam (Exam 706) exam is designed to evaluate your knowledge of transactions and transaction logging, and to test your ability to backup and restore a database using the various methods of backup and recovery available. The questions that make up this portion of the exam are intended to evaluate the following:
-
Your knowledge of the various transaction logging features available.
-
Your knowledge of the types of database recovery available (crash, version, and roll-forward) and your ability to demonstrate when and how each are used.
-
Your ability to create and use database-level and tablespace-level backup images.
-
Your ability to create and use full, incremental, and delta backup images.
-
Your ability to return a damaged or corrupt database to the state it was in at any given point in time.
-
Your knowledge of how and when invalid indexes are recreated.
-
Your ability to suspend and resume database I/O and your ability to initialize a split mirror copy of a database.
This chapter is designed to introduce you to the backup and recovery tools that are available with DB2 Universal Database, and to show you how to both backup a database on a regular basis and restore a database if it becomes damaged or corrupted.
Note
Although basic syntax is presented for most of the DB2 UDB commands covered in this chapter, the actual syntax supported may be much more complex. To view the complete syntax for a specific command or to obtain more information about a particular command, refer to the IBM DB2 Universal Database, Version 8 Command Reference product documentation.
Terms you will learn:
-
Transaction
-
Unit of work
-
COMMIT
-
ROLLBACK
-
Transaction logging
-
Log buffer
-
Circular logging
-
Archival logging
-
Infinite logging
-
Log mirroring
-
Crash recovery
-
Version recovery
-
Roll-forward recovery
-
Transaction failure
-
Recoverable database
-
Non-recoverable database
-
Online backup and recovery
-
Offline backup and recovery
-
RESTART
-
Soft checkpoint
-
BACKUP
-
RESTORE
-
ROLLFORWARD
-
Recovery History file
-
LIST HISTORY
-
PRUNE HISTORY
-
Redirected restore
-
SET TABLESPACE CONTAINERS
-
Invalid index
-
Split Mirror
-
Mirroring
-
Splitting
-
SET WRITE
-
db2inidb
Techniques you will master:
-
Recognizing the types of transaction logging available and understanding when each is to be used.
-
Understanding how crash recovery, version recovery, and roll-forward recovery operations are initiated.
-
Understanding how database-level and tablespace-level backup images are made.
-
Understanding how full, incremental, and delta backup images are created and used.
-
Understanding how invalid indexes are recreated.
-
Understanding how split mirroring is used to backup a database.
-
Understanding how I/O is suspended and resumed so mirrors can be split, as well as knowing how split mirrors are initialized.
Transactions
A transaction (also known as a unit of work) is a sequence of one or more SQL operations grouped together as a single unit, usually within an application process. Such a unit is called "atomic" because, like atoms (before fission and fusion were discovered), it is indivisible—either all of its work is carried out or none of its work is carried out. A given transaction can perform any number of SQL operations—from a single operation to many hundreds or even thousands, depending on what is considered a "single step" within your business logic. (It is important to note that the longer a transaction is, the more database concurrency decreases and the more resource locks are acquired; this is usually considered the sign of a poorly written application.)
The initiation and termination of a single transaction defines points of data consistency within a database; either the effects of all operations performed within a transaction are applied to the database and made permanent (committed), or the effects of all operations performed are backed out (rolled back) and the database is returned to the state it was in before the transaction was initiated.
In most cases, transactions are initiated the first time an executable SQL statement is executed after a connection to a database has been made or immediately after a pre-existing transaction has been terminated. Once initiated, transactions can be implicitly terminated, using a feature known as "automatic commit" (in which case, each executable SQL statement is treated as a single transaction, and any changes made by that statement are applied to the database if the statement executes successfully or discarded if the statement fails) or they can be explicitly terminated by executing the COMMIT or the ROLLBACK SQL statement. The basic syntax for these two statements is:
COMMIT <WORK>
and
ROLLBACK <WORK>
When the COMMIT statement is used to terminate a transaction, all changes made to the database since the transaction began are made permanent. On the other hand, when the ROLLBACK statement is used, all changes made are backed out and the database is returned to the state it was in just before the transaction began. Figure 7-1 shows the effects of a transaction that was terminated with a COMMIT statement; Figure 7-2 shows the effects of a transaction that was terminated with a ROLLBACK statement.
)
Figure 7-1. Terminating a transaction with the COMMIT SQL statement.
)
Figure 7-2. Terminating a transaction with the ROLLBACK SQL statement.
It is important to remember that commit and rollback operations only have an effect on changes that have been made within the transaction they terminate. So in order to evaluate the effects of a series of transactions, you must be able to identify where each transaction begins, as well as when and how each transaction is terminated. Figure 7-3 shows how the effects of a series of transactions can be evaluated.
)
Figure 7-3. Evaluating the effects of a series of transactions.
Changes made by a transaction that have not been committed are usually inaccessible to other users and applications (unless another user or application is using the Uncommitted Read isolation level), and can be backed out with a rollback operation. However, once changes made by a transaction have been committed, they become accessible to all other users and/or applications and can only be removed by executing new SQL statements (within a new transaction). So what happens if a problem occurs (for example, a power failure occurs or an application abends) before a transaction's changes can be committed? In order to answer that question, we must first look at how data changes are made and at how transaction activity is logged.