Circular and archive logs of the DB2 database

Circular and archive logs of the DB2 database
DB2 databases support two different log modes: Circular and Archival ). When a new database is created, the default log mode is loop. If your business requires more advanced functions, you can change the log mode from cyclic to archive. DB2 will always try to write log entries to the main log file set, that is, the log files automatically allocated by the database activity time. If a transaction consumes all major log files (all major log files are marked as unavailable), the database administrator allocates a secondary log file. When the file becomes full, the database administrator checks whether the status of the main log file is unavailable. If yes, a secondary log file is assigned and entries are written into the file. This process repeats until all secondary log files are allocated and fully written. If no major log file is available for writing Redo entries and The maximum number of secondary log files has been allocated, the application will receive The following error message: SQL0964C the transaction log for The database is full. I hope you have encountered this error. However, if this error occurs, increase the number of major and secondary log files (or their sizes) as needed. Ideally, the number or size of major log files should be sufficient to store the largest transaction. Allocating a secondary Log File consumes a considerable amount of resources because it will be executed at runtime. Therefore, we should minimize the number of secondary log files that need to be allocated during peak workloads. To UPDATE the number of primary or secondary log files, run the following command: update db cfg for db_name using logprimary value update db cfg for db_name using logsecond value. Note: if this problem occurs, analyze the reasons for the full log file space. It may be caused by out-of-control queries or user errors, so increasing the number or size of log files can only solve the problem on the surface. For example, assume that a user initiates a delete from tab1 statement and TAB1 is a large table. Although this statement does not seem to be a problem, each line generates a delete log record, but it can easily fill up the log space without being configured. When the cycle log takes effect, the transaction data is written to the main Log File cyclically. When all records stored in a log file no longer need to be used for restoration, the log file will be reused and can become an active log file again later. This means that in the loop log mode, the log file content will eventually be overwritten by new log entries. Because the log file content is overwritten, we can only restore the database to the last complete database backup. You cannot use point-in-time to restore the cyclic logs. In the archive log mode, redo log entries are written to the main log file. However, unlike cyclic logs, these log files cannot be reused. When all records stored in a log file no longer need to be used for restoration, the log file will be marked as non-active rather than reusable. This means that its content will never be overwritten. When the first major Log File becomes full, the system allocates a new log file, so that the configuration quantity of the main log file (LOGPRIMARY database parameter) is always available. All entries related to a single transaction must be consistent in the active log space. If the log space required by a long-running transaction is greater than the space available for the primary log file, the secondary log file may be allocated and used. In the archive log mode, we can use the database backup image and log file to restore the database to a specific time point. For a detailed description of this process, see the following. When you create a new DB2 database by modifying the log mode, the default log mode is circular log. To change the log mode from cyclic to archive, perform the following steps: Create a folder on the disk (for example, e: \ db_name \ archive ), the disk must have enough space to store archived log files. Ensure that the target folder of the archive file is separated from the target folder of the activity log file. TERMINATE the connection to the database: TERMINATE updates the destination folder of the archive log file (you can specify a path for the archive log file to open the archive log mode ). Update db cfg for db_name USING LOGARCHMETH1 "Disk: e: \ db_name \ archive" failed to connect to database: connect to db_name and the following error message is displayed: SQL1116N A connection to or activation of database db_name cannot be made because of backup pending: SQLSTATE = 57019 the cause of the error message is that the log mode has been changed from cyclic to archive, complete database backup is required. The database is not fully backed up when it is in the cyclic log mode. Therefore, a new backup is required after switching mode. Run the following command TO perform a full database backup: backup DATABASE db_name TO d: \ db_name \ backup to connect TO the DATABASE again. This time should be successful. A connect to db_name transaction is a logical unit of work. Each transaction is stored in the transaction log file. Each transaction has a corresponding Redo Log entry. Redo Log entries are written to the current active Log file. When the active Log File becomes full, it is marked as unavailable. At this point, DB2 will create another log file for this active log file and continue to write log entries in it. When the active Log File becomes full, DB2 repeats this cycle. After the transaction is completed (initiate a COMMIT or ROLLBACK Statement), the corresponding log entries are released because they are no longer needed to be used to restore the database.