DB2 transaction log usage (1)

We often receive calls from customers. My database logs are full. Is there any way to quickly clear them? Especially for some beginner DB2 users to maintain a large data volume system, this is almost a problem they will inevitably encounter.

We often receive urgent calls from our customers, and my database cannot be used. Because the log occupies too much space and the file system is full, the log is deleted. Currently, the database cannot be used, this is a production system and needs to be restored as soon as possible. Is there any way to immediately use the database?

When encountering such a problem, we can explain the cause of the problem to the customer over and over again, or provide a very clear solution, but for many customers, this seems to be a helpless solution. They can only handle the system interruptions caused by such operations.

Therefore, it is very important for database designers, developers, and maintenance personnel to clearly understand the log principles of the database and reasonably plan some operations to avoid such a situation! Next, we will share with you the principles and frequently encountered problems of database logs and their solutions.

1. Log principles of the DB2 database

Transaction logs record changes to all objects and data in the database. In earlier versions, the maximum value is 256 GB, and its size is (logprimary + logsecond) * logfilsiz, the value of logprimary + logsecond is less than or equal to 256, and the maximum value of logfilsiz is 262144. In version 9.5, the maximum log size is 512 GB, and the size of logfilsz is changed to 524286.

The logs of the DB2 database are classified into primary and secondary logs. The primary logs are allocated immediately after the first connection arrives at the database or the database is activated, auxiliary logs are dynamically allocated when the size of the primary log is insufficient. Note that the size of the file system where the log is located must be greater than the sum of the size of the master log file and the auxiliary log file.

The DB2 database has two log Configuration Methods: cyclic logs and archived logs.

Cyclic log: This is the default log usage method of the database. The primary log is used to record all changes. After the transaction is committed, the log file will be reused. When the main log file reaches the limit, the auxiliary log file will be used. This log method can be used for crash recovery and version recovery, and cannot be used for roll-forward recovery. Online backup is not supported.

When the space used by the active transaction exceeds the limit of the primary log and the secondary log, or the log space exceeds the available space on the disk, the error of full log is returned.

 

Archive logs: Enable logarchmetd1 and logarchmetd2, or enable the logretain parameter. Note that the logretain parameter is not recommended in version 9.5, and all its setting values will be ignored. We recommend that you stop using logretain when planning database archive logs. Log Files will not be deleted-remain online or offline. Supports Rollback Recovery and online backup.

 

Q: Why is there a full log error when the logs are retained and new logs are generated continuously under the archive log?

The size of active logs available in archive logs is still limited by the sum of active logs and auxiliary logs, scenarios with full logs are exactly the same as those with active logs.

2. Problems and Solutions in log use

In daily use, the most common problem we encounter is that logs are full. Now we use a few practical examples to see how to analyze and solve the problem of full logs. Generally, full logs can be divided into the following scenarios:

A. Prepare the environment and introduce how to evaluate the size of database logs:

The database parameter settings are as follows:

Log File size (4 kb) (LOGFILSIZ) = 10000

Number of Primary log files (LOGPRIMARY) = 3

Number of auxiliary log files (LOGSECOND) = 2

The log size is 200 MB.

Create a test table:

C:\Documents and Settings\administrator>db2 "create table test_log(col int, col2 char(10),col3 timestamp,col4 varchar(100),col5 varchar(100),col6 varchar(100),col7 varchar(100),col8 varchar(100))"

The DB20000I SQL command is successfully completed.

Create a stored procedure for data insertion:

C:\Documents and Settings\administrator>db2 -td@ -vf proc_testlog.sqlcreate procedure proc_testlog(v1 int)begindeclare time int default 0;while (time < v1)doinsert into test_log values(1,'testlog',current timestamp,'testlogtestlogte……');set time = time + 1;end while;end

The DB20000I SQL command is successfully completed.

We will evaluate the insertion of use logs to construct a scenario where logs are full and use db2pd to view the use of transaction logs.

Open two db2cmd session Windows respectively. In window 1, execute:

C: \ Documents ents and Settings \ administrator> db2 + c call proc_testlog (1)

Return status = 0

Run the following command in session 2:

C:\Documents and Settings\administrator>db2pd -db sample -transactionsDatabase Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:29:20Transactions:Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2Firstlsn Lastlsn LogSpace SpaceReserved TIDAxRegCnt GXID0x7FC21A80 7 [000-00007] 2 7 WRITE 0x00000000 0x00000000 0x000027718800 0x000027718800 110 700 0x000000004F131 0

The log occupied by this write operation is about 700 bytes. Repeat the above command in session 1 and view the output of db2pd in session 2:

C:\Documents and Settings\administrator>db2pd -db sample -transactionsDatabase Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:45:55Transactions:Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2Firstlsn Lastlsn LogSpace SpaceReserved TIDAxRegCnt GXID0x7FC21A80 7 [000-00007] 2 8 WRITE 0x00000000 0x00000000 0x000028E385B8 0x000028E38806 154 1334 0x000000004F571 0

1334-700 = 634. We can evaluate that every time a single transaction executes a table insert, the log used to insert a row is about 700 bytes. In a transaction, multiple records are inserted, the log used to insert a row of records is about 634 bytes. Of course, when multiple rows are inserted, the log size is slightly larger than the calculated value.

This method can be used to evaluate the log size that needs to be configured by the database based on the business running situation, or to evaluate the log space required by a single large transaction.

Estimated total log size of 200 MB, 1024*1024/635*330781 =. Therefore, approximately records can be inserted at a time to construct a scenario where logs are full.