Db2中如果有长期未提交的事务,会占用较多的日志或者引起其他的问题,如何找出未提交的事务?并查看这些事务的开始时间、占用了多少日志?应用的IP地址是什么?该事务最后一次执行的SQL语句是什么? 可以通过MON_GET_UNIT_OF_WORK和MON_GET_PKG_CACHE_STMT表函数来查看:
db2 "select m.MEMBER, substr(APPLICATION_ID,1,30) as APPLICATION_ID, m.UOW_ID, substr(UOW_START_TIME,1,19) as UOW_START_TIME, UOW_LOG_SPACE_USED/1024.0/1024.0 as LOG_USED_MB, substr(STMT_TEXT,1,50) as STMT_TEXT from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS m left outer join TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as n on m.LAST_EXECUTABLE_ID = n.EXECUTABLE_ID where UOW_STOP_TIME is NULL order by UOW_START_TIME"
上面的SQL中,判断一个事务未提交的依据是UOW_STOP_TIME为空,表示事务没有结束。查询的字段中,APPLICATION_ID表示对应应用ID,UOW_START_TIME表示事务开始时间,LOG_USED_MB表示占用的日志量, STMT_TEXT 表示正在执行的SQL语句,或者最后一次执行的SQL语句(最后一次执行的SQL语句放在package cache里,如果package cache里不存在了,会显示为空)
测试:下面的测试中,模拟了3个应用。其中Session 1和Session 3没有提交
Session 1:
$ db2 connect to sample
$ db2 +c "insert into t4 select * from t1"
DB20000I The SQL command completed successfully
Session 2:
$ db2 connect to sample
$ db2 "update t2 set B=B+1"
$ db2 connect to sample
$ db2 +c "delete from t3"
DB20000I The SQL command completed successfully.
新开一个Session, 查找未提交的事务信息:
Session 4:
$ db2 connect to sample
$ db2 "select m.MEMBER, substr(APPLICATION_ID,1,30) as APPLICATION_ID, m.UOW_ID, substr(UOW_START_TIME,1,19) as UOW_START_TIME, UOW_LOG_SPACE_USED/1024.0/1024.0 as LOG_USED_MB, substr(STMT_TEXT,1,50) as STMT_TEXT from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS m left outer join TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as n on m.LAST_EXECUTABLE_ID = n.EXECUTABLE_ID where UOW_STOP_TIME is NULL order by UOW_START_TIME"
flush package cache之后,对应的SQL语句找不到了, STMT_TEXT 显示为空:
$ db2 flush package cache dynamic
DB20000I The SQL command completed successfully.
db2 "select m.MEMBER, substr(APPLICATION_ID,1,30) as APPLICATION_ID, m.UOW_ID, substr(UOW_START_TIME,1,19) as UOW_START_TIME, UOW_LOG_SPACE_USED/1024.0/1024.0 as LOG_USED_MB, substr(STMT_TEXT,1,50) as STMT_TEXT from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS m left outer join TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as n on m.LAST_EXECUTABLE_ID = n.EXECUTABLE_ID where UOW_STOP_TIME is NULL order by UOW_START_TIME"
上面的SQL中,判断一个事务未提交的依据是UOW_STOP_TIME为空,表示事务没有结束。查询的字段中,APPLICATION_ID表示对应应用ID,UOW_START_TIME表示事务开始时间,LOG_USED_MB表示占用的日志量, STMT_TEXT 表示正在执行的SQL语句,或者最后一次执行的SQL语句(最后一次执行的SQL语句放在package cache里,如果package cache里不存在了,会显示为空)
测试:下面的测试中,模拟了3个应用。其中Session 1和Session 3没有提交
Session 1:
$ db2 connect to sample
$ db2 +c "insert into t4 select * from t1"
DB20000I The SQL command completed successfully
Session 2:
$ db2 connect to sample
$ db2 "update t2 set B=B+1"
DB20000I The SQL command completed successfully
$ db2 connect to sample
$ db2 +c "delete from t3"
DB20000I The SQL command completed successfully.
新开一个Session, 查找未提交的事务信息:
Session 4:
$ db2 connect to sample
$ db2 "select m.MEMBER, substr(APPLICATION_ID,1,30) as APPLICATION_ID, m.UOW_ID, substr(UOW_START_TIME,1,19) as UOW_START_TIME, UOW_LOG_SPACE_USED/1024.0/1024.0 as LOG_USED_MB, substr(STMT_TEXT,1,50) as STMT_TEXT from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS m left outer join TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as n on m.LAST_EXECUTABLE_ID = n.EXECUTABLE_ID where UOW_STOP_TIME is NULL order by UOW_START_TIME"
$ db2 "select m.MEMBER, substr(APPLICATION_ID,1,30) as APPLICATION_ID, m.UOW_ID, substr(UOW_START_TIME,1,19) as UOW_START_TIME, UOW_LOG_SPACE_USED/1024.0/1024.0 as LOG_USED_MB, substr(STMT_TEXT,1,50) as STMT_TEXT from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS m left outer join TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as n on m.LAST_EXECUTABLE_ID = n.EXECUTABLE_ID where UOW_STOP_TIME is NULL order by UOW_START_TIME"
MEMBER APPLICATION_ID UOW_ID UOW_START_TIME LOG_USED_MB STMT_TEXT
------ ------------------------------ ----------- ------------------- --------------------------------- --------------------------------------------------
0 *LOCAL.inst105.180225152019 1 2018-02-25-07.20.19 23.1301593780 insert into t4 select * from t1
0 *LOCAL.inst105.180225152236 1 2018-02-25-07.22.36 46.2501010894 delete from t3
0 *LOCAL.inst105.180225152252 1 2018-02-25-07.23.02 0.0000000000 select m.MEMBER, substr(APPLICATION_ID,1,30) as AP
3 record(s) selected.
flush package cache之后,对应的SQL语句找不到了, STMT_TEXT 显示为空:
$ db2 flush package cache dynamic
DB20000I The SQL command completed successfully.
$ db2 "select m.MEMBER, substr(APPLICATION_ID,1,30) as APPLICATION_ID, m.UOW_ID, substr(UOW_START_TIME,1,19) as UOW_START_TIME, UOW_LOG_SPACE_USED/1024.0/1024.0 as LOG_USED_MB, substr(STMT_TEXT,1,50) as STMT_TEXT from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS m left outer join TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as n on m.LAST_EXECUTABLE_ID = n.EXECUTABLE_ID where UOW_STOP_TIME is NULL order by UOW_START_TIME"
$ db2 "select m.MEMBER, substr(APPLICATION_ID,1,30) as APPLICATION_ID, m.UOW_ID, substr(UOW_START_TIME,1,19) as UOW_START_TIME, UOW_LOG_SPACE_USED/1024.0/1024.0 as LOG_USED_MB, substr(STMT_TEXT,1,50) as STMT_TEXT from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS m left outer join TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as n on m.LAST_EXECUTABLE_ID = n.EXECUTABLE_ID where UOW_STOP_TIME is NULL order by UOW_START_TIME"
MEMBER APPLICATION_ID UOW_ID UOW_START_TIME LOG_USED_MB STMT_TEXT
------ ------------------------------ ----------- ------------------- --------------------------------- --------------------------------------------------
0 *LOCAL.inst105.180225152019 1 2018-02-25-07.20.19 23.1301593780 -
0 *LOCAL.inst105.180225152236 1 2018-02-25-07.22.36 46.2501010894 -
0 *LOCAL.inst105.180225152252 3 2018-02-25-07.23.35 0.0000000000 select m.MEMBER, substr(APPLICATION_ID,1,30) as AP
3 record(s) selected.