Shulou(Shulou.com)05/31 Report--

This article introduces how to achieve DB2 database transaction log full analysis, the content is very detailed, interested friends can refer to, hope to be helpful to you.

One afternoon, the developer reported an error that the database reported that the transaction log was full while performing a DML operation.

Error message in db2diag.log database log:

2013-04-01-16.43.30.736907 E2147482982C502 LEVEL: Error

PID: 126660 TID: 1 PROC: db2agent (SSO) 0

INSTANCE: db2inst1 NODE: 000 DB: SSO

APPHDL: 0-1103 APPID: GA0429B5.F627.130401082711

FUNCTION: DB2 UDB, data protection, sqlpgResSpace, probe:2860

MESSAGE: ADM1823E The active log is full and is held by application handle

1103. Terminate this application by COMMIT, ROLLBACK or FORCE

APPLICATION.

The details of the client are as follows:

$db2 delete from tzsdb01

DB21034E this command is treated as a SQL statement because it is not a valid command line processor command.

During SQL processing, it returns:

The transaction log for the SQL0964C database is full. SQLSTATE=57011

The reason for this problem is that the amount of data in the table is too large and the log is written to the delete, but the log capacity is too small.

Solution: increase the log capacity, the amount of data or reduce the amount of data deleted at one time, and delete it multiple times.

In order to avoid stopping the warehouse during the day, the production environment prompts the developer to delete it several times, so that there will be no mistakes. However, in order to solve this problem completely, it is necessary to apply for stopping the database to modify the database parameters.

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

$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 (4KB) (DATABASE_MEMORY) = AUTOMATIC (1662183)

Database memory threshold (DB_MEM_THRESH) = 10

Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC (130720)

Percent. Of lock lists per application (MAXLOCKS) = AUTOMATIC (97)

Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC (421273)

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC (46809)

Sort list heap (4KB) (SORTHEAP) = AUTOMATIC (9361)

Database heap (4KB) (DBHEAP) = AUTOMATIC (2626)

Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300

Log buffer size (4KB) (LOGBUFSZ) = 4096

Log file size (4KB) (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/

Let's first look at increasing the capacity of the log. Notice that the red value is 1024.

$db2 update db cfg for zssqdb01 using logfilsiz 8192 increases it to 8192

Then stop the application, stop the library and then start the library will take effect.

$db2 force applications all

$db2stop

$db2start

If you need to increase the amount of data in the log, you need to calculate and set it like this, and the log capacity has been increased to 8192.

Log file size (4KB) (LOGFILSIZ) = 8192

Number of primary log files (LOGPRIMARY) = 10

Number of secondary log files (LOGSECOND) = 4

The amount of log data now is

The calculation formula is as follows:

Data volume size of database transaction log = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4KB

That is:

(10 + 4) * 8192 * 4KB = 458752 K = 458m (large divisor)

Let's disconnect all connections to this database.

Modify the number of primary log files: db2 update db cfg for using LOGPRIMARY 15

Modify the number of secondary log files: db2 update db cfg for using LOGSECOND 10

The size at this time = (15-10) * 8192*4KB=819200K=819M (large divisor)

Then stop the library and then open the library, and the problem is solved.

Note: LOGPRIMARY+LOGSECOND cannot exceed 255G and log space cannot exceed 256g. Changes to the LOGPRIMARY and LOGFILSIZ parameters need to be disconnected for reconnection to take effect. The modification of the LOGSECOND parameter will take effect immediately. For the emergency solution of the production system, you can directly modify this parameter first.

Additional knowledge:

1, the number of primary log files LOGPRIMARY

This database configuration parameter is used to specify the number of primary log files to be preallocated. The master log file establishes a fixed amount of memory allocated to the recovery log file. In circular log management mode, database transactions will reuse the primary log sequentially, that is, when one primary log is full, the next primary log will be used sequentially, and if the primary log is full, one secondary log will be allocated at a time as needed, and the secondary log will be released after use. If you find that the database allocates secondary log files frequently, you may need to improve system performance by increasing the log file size or increasing the number of primary log files.

2, the number of secondary log files LOGSECOND

This database configuration parameter is used to specify the number of secondary log files allocated as needed. Try not to set the value of this parameter to "- 1". "- 1" means that you are requesting an unlimited activity log space, and the database will not report that the database transaction log is full. If there is insufficient space, it will report that the log disk is full.

3, log file size LOGFILSIZ

This database configuration parameter specifies the size of the log file.

4. Database transaction log is full error

The database transaction log full error means that the current transaction cannot be written to the activity log (at this time, the primary and secondary log files have been used up or there is not enough space for the current transaction to write). This error and log disk space are two different concepts. The fact that the database transaction log is full is not caused by the full disk space, but because the overall size of the unimplemented transaction is too large, which exceeds the maximum size that the database transaction log can hold.

When such an error occurs, do not try to use the DB2STOP FORCE command to force the database to stop. It is recommended that you use the FORCE APPLICATION command to stop the application that caused the error or stop all applications. It is also not recommended that you use the KILL command to kill any DB2-related processes. If you use the DB2STOP FORCE command to hang, finally try to kill the process, re-DB2START, or restart the server.

This is the end of the analysis on how to realize the full transaction log of DB2 database. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.