Principles and techniques for DB2 recovery with transaction logs after a crash

After the system crashes, use the DB2 transaction log to recover the database.

How many times have you encountered the error message "sql0946c The transaction log for the"?

When trying to solve the problem, do you stop to think about the following two questions: 1. Why the transaction log exists; 2. What is the purpose of the Transaction logging service?

Without transactions, multiple users and applications that interact with a database at the same time will inevitably destroy the data. Without transaction logging, some library recovery methods in the DB2 udb do not exist.

If you haven't fully understood these concepts, don't worry. I'll explain what the transaction is and the mechanism behind the transaction log record. Then, I'll show you how to use the information stored in the database transaction log file to bring the database back to a consistent, usable state after a system crash or a program failure.

You can also do more things with these important logs. In future columns, I'll show you how to use the transaction log file to reproduce the operation to restore the database exactly to the state it was in at a given point in time.

Transaction

A transaction (also known as a unit of work) is a sequence of one or more SQL operations, grouped together into a single unit and typically within an application process. The unit is often referred to as "atomic" because it is not divided-all of its work is either performed or not executed. A given transaction can perform any number of SQL operations (from one to thousands of, depending on the definition of "one-step" in the business logic).

The start and end of a transaction define the point of data consistency in the database; either the results of all operations performed in the transaction are applied to the database and made permanent (committed), or both are revoked (rolled back) and the database is returned to the state before the transaction was started.

A transaction is launched the first time an SQL statement is executed after a connection is established to the database, or immediately after an existing transaction terminates. Once started, the transaction can be terminated implicitly by using a feature named Atomic commit. With an atomic commit, each executable SQL statement is treated as a transaction. If the statement succeeds, any modifications it makes will be applied to the database, but if the statement fails, the modification is discarded.

You can also explicitly terminate a transaction by executing a COMMIT or rollback SQL statement.

The basic syntax for these statements is:

COMMIT <WORK>
ROLLBACK <WORK>

When a commit terminates a transaction, all modifications made to the database by the transaction from the beginning are permanent. With rollback, all modifications will be revoked.

Uncommitted modifications made by the firm are inaccessible to other users and applications unless those users and applications are using uncommitted read (UR) isolation. However, once the changes are committed, they are accessible to all other users and applications and can only be deleted by executing a new SQL statement in the new transaction.