分析DB2活动日志满的原因及解决DB2日志满方法与避免方案

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

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

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

$ db2 get snapshot for database on sample | grep -i oldest
Appl id holding the oldest transaction   = 441

$ db2 get snapshot for application agentid 441

      Application Snapshot

Application handle             = 441
Application status             = UOW Waiting         <<--应用状态为UOW Waiting
Status change time             = 2017-03-09 17:23:15.068895
Application code page           = 1386
Application country/region code      = 86
DUOW correlation token           = *LOCAL.DB2INST1.170309092244
Application name              = db2bp.exe
Application ID               = *LOCAL.DB2INST1.170309092244

..

Connection request start timestamp     = 2017-03-09 17:22:44.963163 <<--应用连库时间
Connect request completion timestamp    = 2017-03-09 17:22:45.961157
Application idle time           = 4 minutes 7 seconds

..

UOW log space used (Bytes)         = 664
Previous UOW completion timestamp     = 2017-03-09 17:22:45.961157
Elapsed time of last completed uow (sec.ms)= 0.000000
UOW start timestamp            = 2017-03-09 17:23:02.770477 <<--当前事务开始时间
UOW stop timestamp             =              <<--当前事务结束时间为空，说明还没有commit
UOW completion status           =

..

Statement type               = Dynamic SQL Statement
Statement                 = Close
Section number               = 201
Application creator            = NULLID
Package name                = SQLC2K26
Consistency Token             =
Package Version ID             =
Cursor name                = SQLCUR201
Statement member number          = 0
Statement start timestamp         = 2017-03-09 17:23:15.067789
Statement stop timestamp          = 2017-03-09 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

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

$ 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"