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 log 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.
Affairs
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.
transaction log Records
When you insert into a base table, you first create a record in the buffer pool that is associated with the table space where the data for the table is stored. Each time a record is updated or deleted, the page containing the record is retrieved from the storage and copied to the appropriate buffer pool, which is then modified by Update/delete. Once this modification is made, a record is written to the log buffer that reflects the action, and the log buffer is another specified store in memory (the true storage size reserved for the log buffer is controlled by the Logbufsiz database configuration parameter). If an insert is executed, a record is written that contains the new row data value. When a deletion occurs, a record is written that contains the original value of the row. If you execute update, you write a record that contains the original and new values of the row (in most cases, you generate logging for the update operation by executing exclusive OR on the original value with the updated value of the row). Eventually, when the transaction that executes the INSERT, UPDATE, or delete terminates, the corresponding commit or rollback record is written to the log buffer.
Whenever the buffer pool I/O page cleaner is activated, the log buffer itself is full, or when a transaction is committed or rolled back, all records stored in the log buffer are immediately written to one or more transaction log files stored on the disk. If a system failure occurs, constant flushing of the log buffers minimizes the number of log records that may be lost. Once all the log records associated with a particular transaction (including the corresponding commit or rollback record) are successfully materialized (externalize) to one or more log files, the results of the transaction itself are copied to the appropriate tablespace container Permanent storage (the modified data pages themselves remain in memory and can be accessed quickly when necessary; they will eventually be overwritten). This process is called pre-write logging (Write-ahead logging), ensuring that changes made to the data are always materialized as log files before they are recorded in the database. See figure below:
Because multiple transactions can use a database at any time, a log file may contain logging that belongs to 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 created it. By using the transaction ID, the log records associated with a particular transaction can be written to one or more log files at any time without affecting data consistency-eventually, the commit or rollback record of the operation that terminated the transaction is also logged.
Crash Recovery
What happens if a problem occurs-for example, when a power outage or an application terminates unexpectedly-before a transaction is submitted for modification? Any uncommitted or rolled back work done by the transaction will be lost. In addition, if a committed transaction whose data is being materialized (externalize) to the database is compromised, the database will be in an inconsistent, unusable state (the inconsistent database will generate return codes and error messages whenever an attempt is made to establish a connection). You cannot recover a transaction record that is stored in memory, but you can restore inconsistent databases to a consistent, usable state by performing an operation called crash recovery.
The most common way to start crash recovery is to execute restart commands from the DB2 command-line processor (DB2 command line PROCESSOR,CLP). The basic syntax for this command is:
Where: DatabaseName indicates the name of the database that is assigned to the attempt to recover.
UserName indicates the name assigned to the user, and crash recovery executes under that user's permissions.
Password indicates the password that corresponds to the user name, and crash recovery executes under that user's permissions.
Ts_name indicates the name assigned to one or more tablespaces, and if an error is encountered while attempting to restore the tablespace to a consistent state, the tablespace will be disabled or set to drop pending mode.
Note: The arguments displayed in angle brackets (< >) are optional, the parameters displayed in brackets ([]) are required, and the comma is followed by an ellipsis (, ...). Indicates that the preceding argument can be repeated multiple times. For complete syntax on the restart command, see IBM DB2 Universal Database, Version 8 command Reference (ftp.software.ibm.com/ps/products/db2/info /vr8/pdf/letter/db2n0e80.pdf).
If you need to perform a crash recovery operation on a database named sample, execute the Restart Database sample command.
You can also configure the database so that it automatically initiates crash recovery whenever a user or application attempts to connect in an inconsistent state. You simply need to assign the value on to the AutoRestart configuration parameters of the database (whenever the database is activated or when a connection is attempted, DB2 database manager checks the state of the databases.) When the AutoRestart configuration parameter is set to on, database Manager automatically executes the restart command if the databases are in an inconsistent state.
In the case of crash recovery, records stored in the database transaction log files are parsed and each transaction record with the corresponding commit record is applied back to the database. reproduce and then undo all records that do not have a corresponding commit record (this is why the information before and after all update operations are logged). The ability to restore a database to a consistent state after a failure is always guaranteed because logging is frequently materialized and modifications made by a particular transaction are materialized only if the transactions themselves are terminated successfully. Crash recovery is just one of the features provided by the transaction log.
