Principles of recovering data from transaction logs after a DB2 database crash

This article mainly describes how to recover data with transaction logs after the DB2 database crashes. The following describes how to recover data with transaction logs after the DB2 database crashes, if you are interested, you can click the following articles to view them.

After the system DB2 database 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 the following two questions: 1. Why transaction logs exist; 2. What is the purpose of the transaction logging service?

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 bring the database back to a consistent and available state after the system DB2 database crashes or the program fails. You can also do more with these important logs.

Transactions

A transaction is also called a unit of work. It refers to the sequence of one or more SQL operations. These operations are combined into one unit and usually located in an application process. This unit is often referred to as 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 several 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 cancel rollback) to make the database Return to the status before starting the transaction.

A transaction is started when an SQL statement is executed for the first time after the connection to the database is established or when an existing transaction is terminated. Once started, you can use the function named atomic commit to implicitly terminate the transaction. Through atomic commit, each executable SQL statement is considered 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 explicitly terminate a transaction by executing a COMMIT or rollback SQL statement.

The basic syntax of these statements is:

 
 

  
  
COMMIT <WORK> 
  
  
ROLLBACK <WORK>  
 
 

When COMMIT terminates a transaction, all modifications made to the database from the transaction to 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 use uncommitted read UR. 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.

Transaction log records

When an INSERT operation is performed on a base table, a record is created 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, copied to the appropriate buffer pool, and then updated/deleted.

Once this modification is made, a record will be written to the log buffer to reflect this action, the real storage size reserved for the log buffer in another specified storage area in the memory is controlled by the configuration parameters of the logbufsiz database ).

If INSERT is executed, 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. If UPDATE is executed, a record containing the original value and new value of the row is written. In most cases, the exclusive or operation is performed on the original value with the updated value of the row, generate a log for the update operation ). Finally, when the transaction executing INSERT, UPDATE, or DELETE is terminated, the corresponding COMMIT or ROLLBACK records are written to the log buffer.

When the buffer pool I/O page cleaner is activated, the log buffer itself is full, or the 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 a system failure occurs, the constant refreshing of the log buffer minimizes the number of log records that may be lost.

Once all the log records associated with a specific transaction include the corresponding COMMIT or ROLLBACK records) are embodied as one or more log files, the results of the transaction itself will be copied to the appropriate tablespace container to permanently store the modified data page itself is still in the memory, and can be accessed quickly if necessary; they will eventually be rewritten ). This process is called write-ahead logging) to ensure that the changes made to the data are always embodied as log files before they are recorded in the database.

Because multiple transactions can use one database at any time, a log file may contain logs of several different transactions. To track which transaction a log record belongs to, assign a special transaction ID to each log record and bind it to the transaction that creates it.

By using the transaction ID, you can write the log records associated with a specific transaction to one or more log files at any time without affecting data consistency, the COMMIT or ROLLBACK records for the operations that terminate the transaction will also be logged. The above content is an introduction to the principles of recovering transaction logs after the DB2 database crashes, I hope you will have some gains.