DB2活动日志占用过大

原创

wx62e28ac222a20 2022-07-29 21:04:49 ©著作权

文章标签 数据库 mysql java sql mybatis 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

db2 get snapshot for db on dbname>/tmp/1.out
cat /tmp/1.out  | grep -i oldest 
cat /tmp/1.out  | grep -i "log space used"
db2 get snapshot for application agentid xxx
db2 force application xxx

可以通过增加LOGSECOND来临时增加可用的日志大小(修改时需要加上immediate选项使之立即生效)；增加LOGPRIMARY并没有用，因为需要重启数据库才能生效。
force掉hold住首个活动日志的的应用，在force之前，可以抓取snapshot，看一下这个应用的状态：

$ 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

<<—一个事务中可能有多条SQL，这个只表示当前正在执行或者最后执行过的SQL，并不能表示就是这条SQL导致了日志满，这里抓取到的是一条SELECT语句，SELECT语句不占用日志。抓取到的快照里没有这一项? 请点击我

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

日志满的避免：
1.)根据抓取到的应用的snapshot，找应用开发人员查看为何不肯提交，这才是避免问题再次出现的根本办法。
2.)从DB2管理层面，可以设置数据库配置参数max_log和num_log_span3.)可以写脚本，以固定的间隔抓取database snapshot中的Appl id holding the oldest transaction, 如果长时间不发生变化(比如2天)，就Force掉。

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"

补充说明：
查看数据库整体日志的作用率：https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0060791.html

查看每个应用使用的日志大小：
$ 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”https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0006018.html

Linux,oracle