Case study of full DB2 database transaction log

One afternoon, the development report said that when the DML operation was executed, the database reported an error where the transaction log was full. The details are as follows:

$ Db2 delete from tzsdb01
DB21034E this command is processed as an SQL statement because it is not a valid "command line processor" command.
During SQL processing, it returns:
The transaction log of the SQL0964C database is full. SQLSTATE = 57011

This problem occurs because the table data volume is too large. When you delete a table, logs are written, but the log capacity is too small.

Solution: Increase the log capacity, data volume, or reduce the delete data volume once, and delete the log multiple times.

To avoid database shutdown during the day, the production environment prompts developers to delete the Database Multiple times so that no error occurs. To completely solve this problem, you also need to apply for database shutdown to modify Database parameters.

Let's take a look at the log configuration parameters of the database.

$ Db2 get db cfg for tzsdb01

Database Configuration for Database zssqdb01

Database configuration release level = 0x0d00
Database release level = 0x0d00

Database territory = cn
Database code page = 1208
Database code set = UTF-8
Database country/region code = 86
Database collating sequence = IDENTITY

Multi-page file allocation enabled = YES

Log retain for recovery status = RECOVERY
User exit for logging status = YES

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4 kb) (DATABASE_MEMORY) = AUTOMATIC (1662183)
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4 kb) (LOCKLIST) = AUTOMATIC (130720)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC (97)
Package cache size (4 kb) (PCKCACHESZ) = AUTOMATIC (421273)
Sort heap thres for shared sorts (4 kb) (SHEAPTHRES_SHR) = AUTOMATIC (46809)
Sort list heap (4 kb) (SORTHEAP) = AUTOMATIC (9361)

Database heap (4 kb) (DBHEAP) = AUTOMATIC (2626)
Catalog cache size (4 kb) (CATALOGCACHE_SZ) = 300
Log buffer size (4 kb) (LOGBUFSZ) = 4096

Log file size (4 kb) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 4
Changed path to log files (NEWLOGPATH) =
Path to log files =/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

First, increase the log capacity. Note that the red value is 1024.

$ Db2 update db cfg for zssqdb01 using logfilsiz 8192 increase it to 8192

Stop the application, stop the database, and start the database again.

$ Db2 force applications all

$ Db2stop

$ Db2start

If you need to increase the log data volume, you need to perform computation and such settings, then the log capacity has changed to 8192

Log file size (4 kb) (LOGFILSIZ) = 8192
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 4

The current log data volume is

The calculation formula is as follows:

Size of Database Transaction logs = LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4KB

That is:

10 + 4) * 8192*4 kb = 458752 K = approximately 458 M)

Disconnect all connections to the database below

Modify the number of master log files: db2 update db cfg for <dbname> using LOGPRIMARY 15

Modify the number of auxiliary log files: db2 update db cfg for <dbname> using LOGSECOND 10

The size = 15 + 10) * 8192 * 4KB = 819200 K = 819 M (approximately)

Then stop the database and start the database. The problem is solved.

Note: LOGPRIMARY + LOGSECOND cannot exceed 255, and the log space cannot exceed 256 GB. Modifications to the LOGPRIMARY and LOGFILSIZ parameters need to be disconnected to take effect after the database is reconnected. Modifications to the LOGSECOND parameter take effect immediately. You can directly modify this parameter for emergency solutions that are faster in the production system.

Additional knowledge:

1. Number of master log files LOGPRIMARY

This database configuration parameter is used to specify the number of master log files to be preallocated. The number of fixed storage allocated to the recovery Log File Created by the primary log file. In the cyclic log management mode, database transactions reuse the primary log in sequence, that is, when a primary log is full, the next primary log is used in sequence. If the primary log is full, A secondary log is allocated at a time as needed. After the secondary log is used up, it is released. If you find that the database often allocates auxiliary log files, you may need to increase the size of the log files or the number of Primary log files to improve system performance.

2. Number of auxiliary log files LOGSECOND

This database configuration parameter is used to specify the number of auxiliary log files allocated on demand. Do not set the value of this parameter to "-1". "-1" indicates that you are requesting an infinite active log space. The database will not report that the database transaction log is full, if the disk space is insufficient, an error occurs when the log disk is full.

3. log file size: LOGFILSIZ

This database configuration parameter is used to specify the log file size.

4. Database Transaction Log full error

Database Transaction Log full error indicates that the current transaction cannot be written into the activity log. At this time, the master log file and the auxiliary log file are all used up or there is not enough space for writing the current transaction ), note that this error and the log disk space is full. The database transaction log is full, not because the disk space is full, but because the total number of unimplemented transactions is too large, which exceeds the maximum size that the database transaction log can accommodate.

When such an error occurs, do not use the DB2STOP FORCE command to FORCE the database to stop. We recommend that you use the force application command to stop the APPLICATION that causes the error or stop all applications. We do not recommend that you use the KILL command to KILL any DB2-related processes. If the DB2STOP FORCE command hang is used, kill the process, restart DB2START or restart the server.‍‍‍

This article from the "Drop water stone" blog, please be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1219692