Analyze the causes of full DB2 Activity logs and solutions to full DB2 logs, db2 logs

Analyze the causes of full DB2 Activity logs and solutions to full DB2 logs, db2 logs

Log usage

Displays the usage of logs under concurrent transaction conditions

There are three concurrent programs: Process 1, Process 2, and Process 3. Each program has two transactions. Blue blocks represent SQL statements, red blocks represent commit operations, and green blocks represent rollback operations. Each downward arrow indicates that the data in the log buffer is refreshed to the log disk (by default, every commit operation will cause the log buffer to be refreshed to the disk ).

At the time T1, the transaction A commit and the log buffer are refreshed to the disk.
At T2. transaction B commit, the log buffer is refreshed to the disk. At this time, log X is used up, but since transaction C in X has not been committed, X is still the active log.

In, if transaction C has not been committed, log X will always be the first activity log (oldest transaction log), and subsequent logs will also be activity logs, other applications will eventually cause full logs.

Activity Log

If a log contains uncommitted transactions, this log is the activity log (there are other cases, for example, although all transactions have been committed, but the corresponding changes have not been persisted to the disk ).

First Active Log)

The first activity log, the log after the first activity log (that is, the log with a higher number than the first activity log) is the activity log. You can view the first active log through the snapshot of the database, current active log and last active log.

$ db2 get snapshot for db on sample | grep -i "File number"File number of first active log      = 0File number of last active log       = 2File number of current active log     = 0File number of log being archived     = Not applicable

Log full reason

The maximum space available for DB2 activity logs is limited. When the limit is reached, the log is full. The limit is (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4KB.

There are two reasons for full logs:

1.) A small transaction holds the first activity log and has not been committed. As a result, the first activity log remains active and is not released. This is similar to a traffic jam. A vehicle blocks an intersection due to an engine fault (the transaction is not submitted) (occupying the first activity log), even if there is no problem with the subsequent vehicles (the subsequent transactions are submitted normally ), it is also impossible to pass through the intersection, and the accumulation will increase, resulting in the entire road congestion (full logs ).

2.) a transaction is very large and all logs are quickly used up.

Full logs:

First, the application reports the SQL0964C error:

$ db2 "insert into test select * from test"DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:SQL0964C The transaction log for the database is full. SQLSTATE=57011

The following error is reported in db2diag. log:

2017-03-09-17.24.50.315000+480 E3234873F644     LEVEL: ErrorPID   : 8532         TID : 13028     PROC : db2syscs.exeINSTANCE: DB2INST1       NODE : 000      DB  : SAMPLEAPPHDL : 0-453        APPID: *LOCAL.DB2INST1.170309092321AUTHID : MIAOQINGSONG     HOSTNAME: ADMINIB-PR7US3IEDUID  : 13028        EDUNAME: db2agent (SAMPLE)FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:2860MESSAGE : ADM1823E The active log is full and is held by application handle     "0-441". Terminate this application by COMMIT, ROLLBACK or FORCE     APPLICATION.

Temporary processing of full logs:

1. You can increase the available Log Size by adding LOGSECOND (the immediate option needs to be added to make it take effect immediately). Adding LOGPRIMARY does not work because you need to restart the database to make it take effect.

2. force: Drop the application that holds the first activity log. Before force, you can capture snapshot and check the status of the application:

$ Db2 get snapshot for database on sample | grep-I oldestAppl id holding the oldest transaction = 441 $ db2 get snapshot for application agentid 441 Application SnapshotApplication handle = 441 Application status = UOW Waiting <-- The Application Status is UOW WaitingStatus change time = 17:23:15. 068895 Application code page = 1386 Application country/region code = 86 DUOW correlation token = * LOCAL. DB2INST1.170309092244Application name = db2bp.exe Application ID = * LOCAL. DB2INST1.170309092244 .. connection request start timestamp = 17:22:44. 963163 <-- application database connection time Connect request completion timestamp = 17:22:45. 961157 Application idle time = 4 minutes 7 seconds .. UOW log space used (Bytes) = 664 Previous UOW completion timestamp = 17:22:45. 961157 Elapsed time of last completed uow (sec. ms) = 0.000000UOW start timestamp = 17:23:02. 770477 <-- current transaction start time UOW stop timestamp = <-- the current transaction End Time is null, indicating that no commitUOW completion status = .. statement type = Dynamic SQL StatementStatement = CloseSection number = 201 Application creator = NULLIDPackage name = partition Token = Package Version ID = Cursor name = SQLCUR201Statement member number = 0 Statement start timestamp = 17:23:15. 067789 Statement stop timestamp = 17:23:15. 068893 Elapsed time of last completed stmt (sec. ms) = 0.000024 Total Statement user CPU time = 0.000000 Total Statement system CPU time = 0. 000000 .. dynamic SQL statement text: select * from t1

<-- A transaction may have multiple SQL statements. This statement only indicates the currently executed or last executed SQL statements. It cannot indicate that this SQL statement causes full logs, here, a SELECT statement is captured, and the SELECT statement does not occupy logs.

$ db2 "force application (441)"DB20000I The FORCE APPLICATION command completed successfully.DB21024I This command is asynchronous and may not be effective immediately.

Avoid full logs:

1.) based on the captured snapshot of the application, find the application developer to check why the application is not submitted. This is the fundamental way to avoid the problem from happening again.
2) from the DB2 management layer, you can set database configuration parameters max_log and num_log_span.
3.) You can write a script to capture the Appl id holding the oldest transaction in the database snapshot at a fixed interval. If it remains unchanged for a long time (for example, 2 days), Force it out.

Note:

View the log size of each application:

$ db2 "select application_handle,UOW_LOG_SPACE_USED,UOW_START_TIME FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) order by UOW_LOG_SPACE_USED" 

You can also use db2pd-db <dbname>-transactions to view the status of each log in use.

Important parameters include:

ApplHandl
The application handle of the transaction.
SpaceReserved
The amount of log space that is reserved for the transaction.
LogSpace
The total log space that is required for the transaction, including the used space and the reserved space for compensation log records.

Through the analysis of the reason why DB2 activity logs are full, we can find a solution to this problem and avoid this problem from happening again.