Research on DB2 transaction logs

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

How many times have you encountered the error message "sql0946c the transaction log for the database is full "?

When trying your best to solve this problem, do you have to stop and think about why transaction logs and transaction logging services exist?

If there is no transaction, when multiple users and applications interact with a database at the same time, data will inevitably be damaged. However, if there is no transaction log record, some database restoration methods in DB2 UDB will not exist.

If you do not fully understand these concepts, do not worry. I will explain what the transaction is and the mechanism behind the transaction log record. Then, I will show you how to use the information stored in the database transaction log file to restore the database to a consistent and available state after a system crash or program failure.

However, you can do more with these important logs. In future columns, I will show you how to use the transaction log file to reproduce operations to restore the database to the status at a given point in time.

Transactions

A transaction (also called a unit of work) refers to one or more SQL statements.
The sequence of operations. These operations are combined into a unit and usually located in an application process. This unit is usually called an "Atomic" because it is inseparable-
All of its work is either executed or not executed. A given transaction can execute any number of SQL operations (from one to thousands, 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; or apply the results of all operations performed in the transaction to the database, and make it permanent (committed), or revoke (roll back), so that the database returns to the status before starting the transaction.

A transaction is the first time that an SQL statement is executed after a connection is established to the database.
Statement or start immediately when an existing transaction is terminated. Once started, you can use the function named atomic commit to implicitly terminate the transaction. If you submit an SQL statement using an atom
Statement as a transaction. If the statement is successfully executed, any modifications it makes will be applied to the database, but if the statement fails, the modifications will be discarded.

You can also executeCOMMIT
OrROLLBACK SQL
Statement to terminate the transaction explicitly.

The basic syntax of these statements is:

Commit <work> And Rollback <work>

 

InCOMMIT
When a transaction is terminated, all modifications made to the database from the transaction start to permanent. UseROLLBACK
All modifications will be revoked.

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

 

Back to Top

Transaction log records

In a base tableINSERT
First, create a record in the buffer pool. The buffer pool is associated with the tablespace where the table data is stored. Each time a record is updated or deleted, the page containing the record is retrieved from the memory and copied to the appropriate buffer pool.UPDATE/DELETE
. Once this modification is made, a log buffer will be written to the log buffer to reflect this action. The log buffer is another designated storage area in the memory. (The actual storage size reserved for the log buffer is controlled by the configuration parameters of the logbufsiz database .) IfINSERT
A record containing the new data value is written. When a row is deleted, a record containing the original value of the row is written. IfUPDATE
Write a record that contains the original and new values of the row. (In most cases, the update value of this row is executed on the original value.EXCLUSIVE OR
To generate a log record for the update operation .) Finally, whenINSERT
,UPDATE
OrDELETE
When the transaction is terminated, the correspondingCOMMIT
OrROLLBACK
Logs are written to the log buffer.

Whenever the buffer pool I/O is activated
Page cleaner, the log buffer itself is full, or when a transaction is committed or rolled back, immediately write all records stored in the log buffer to one or more transaction log files stored on the disk. If the system
Due to system faults, constant refresh of the log buffer minimizes the number of log records that may be lost. All log records associated with a specific transaction (including the correspondingCOMMIT
OrROLLBACK
Record) externalize is one or more log files, and the results of the transaction itself are copied to the appropriate tablespace container for permanent storage. (Modified data pages are retained.
In the memory, you can quickly access it if necessary; they will eventually be rewritten .) This process is called the write-ahead
Logging) to ensure that the changes made to the data are always embodied as log files before being recorded to the database. (See Figure 1)

Because multiple transactions can use one database at any time, a log file may contain logs of several different transactions. To track which log records belong to which transaction
A special transaction ID is assigned to a log record to bind it to the transaction that creates it. By using transactions
Id. logs associated with a specific transaction can be written to one or more log files at any time without affecting data consistency.COMMIT
OrROLLBACK
Logs will also be recorded.

 

Back to Top

Crash recovery

If a problem occurs before a transaction is committed-for example, a power failure occurs or an application terminates abnormally-
What will happen? Any uncommitted or rolled back work done by the firm will be lost. In addition, if the committed transaction whose data is materialized (externalize) to the database is broken
Otherwise, the database will be in an inconsistent and unavailable state. (When a connection is attempted, inconsistent databases generate code and error messages .) You cannot recover the transaction records stored in the memory, but you can
The database is restored to a consistent and available state by performing a crash recovery operation.

The most common method to start crash recovery is to execute the restart command from the DB2 command line processor (CLP. The basic syntax of this command is:

RESTART [DATABASE | DB] [DatabaseName] USER [UserName] < USING [Password] > > < DROP PENDING TABLESPACES ( [TS_Name] , ... ) > < WRITE RESUME >

 

Where:

• Databasename indicates the name allocated to the database to be restored.
• Username indicates the name assigned to the user. The crash recovery will be executed under the user's permissions.
• Password indicates the password corresponding to the user name. The password is executed under the permission of the user after the crash recovery.
• Ts_name indicates the name allocated to one or more tablespaces. If an error occurs when you try to restore the tablespace to a consistent state, these tablespaces are disabled or set to drop pending mode.

Note:
Parameters displayed in angle brackets (<>) are optional. Parameters displayed in square brackets ([]) are required. ellipsis (,...) is followed by commas (,...) the preceding parameters can be repeated multiple times. AboutRESTART
For the complete Syntax of commands, see the IBM DB2 Universal Database, version 8 command reference listed in reference.

If you wantSAMPLE
And then execute the crash recovery operation on the database.RESTART DATABASE SAMPLE
Command.

You can also configure the database so that it will automatically start crash recovery whenever the user or application tries to connect to it in an inconsistent state. You only need to set the valueON
TheAUTORESTART
Configure parameters. (Every time you activate a database or try to establish a connection, DB2 database manager checks the database status. When the autorestart configuration parameter is set to on, if the database is inconsistent, the database manager will automatically executeRESTART
Command .)

During crash recovery, the records stored in the database transaction log files will be analyzed and each record will have a correspondingCOMMIT
The recorded transaction records are re-applied to the database. Reproduce and undoCOMMIT
All records (that is why the information before and after all update operation records is required ). Because log records are frequently materialized, and modifications made by specific transactions are made only when the transaction is successfully terminated, therefore, the ability to restore the database to a consistent state after a fault is always guaranteed.

Crash recovery is only one of the functions provided by transaction logs. When dealing with Rollback Recovery, I will show you how to use the records stored in the transaction log file to restore the database to any status at any specified time point. But first, you need to understand the concepts of backup image and version recovery. I will introduce these two topics in the next column.