What is the cause of transaction log recovery after the DB2 database crashes?

This article introduces the main causes and correct operation skills for recovering from transaction logs after the DB2 database crashes, if you are curious about the original and correct operation skills of restoring the DB2 database with transaction logs after the database crash, the following articles will unveil its secrets.

Log, transaction, skill, principle log, transaction, skill, Principle

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 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 of the operations that terminate the transaction will also be logged.

Crash recovery

If a problem occurs before a transaction is committed-for example, a power failure 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 data is materialized externalize) committed transactions to the database are damaged, the database will be in an inconsistent and unavailable State whenever you try to establish a connection, inconsistent databases generate Returned Code and error messages ). You cannot recover the transaction records stored in the memory, but you can restore the inconsistent database to a consistent and available state by performing the database crash recovery operation named DB2.

The most common way to start the crash recovery is to execute the RESTART Command from the DB2 Command Line Processor 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 >  
 
 

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. For the complete syntax of the RESTART Command, see IBM DB2 Universal Database, Version 8 Command Reference (ftp.software.ibm.com/ps/products/db2/info/vr8/#/letter/db2n0e80.pdf ).

If you need to execute the DB2 DATABASE crash recovery operation on the DATABASE named "SAMPLE", run the restart database sample command.

You can also configure the database so that it automatically starts crash recovery whenever the user or application tries to connect in an inconsistent state. You only need to assign the value ON to the AUTORESTART Configuration Parameter of the Database. Every time you activate the 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 in an inconsistent state, Database Manager Automatically executes the RESTART command ).

During crash recovery, the records stored in the database transaction log files are analyzed, and each transaction record with the corresponding COMMIT record is re-applied to the database. Reproduce and then undo all records with no corresponding COMMIT record, which is why information before and after all update operation records should be obtained ).

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. The DB2 database crash recovery is only one of the functions provided by transaction logs.

This topic is classified by hchao