本篇文章主要介绍了"DB2活动日志满，如何找到占用最老日志的进程并将其force掉以释放日志"，主要涉及到方面的内容，对于DB2感兴趣的同学可以参考一下：
下面的过程显示了如何查看DB2日志使用百分比，如何找到占用最老日志的进程并发现其执行的SQL语句，最后force掉这个进程以释放日志1.）查看日志使用百分比$ ...
下面的过程显示了如何查看DB2日志使用百分比，如何找到占用最老日志的进程并发现其执行的SQL语句，最后force掉这个进程以释放日志
1.）查看日志使用百分比
$
db2 "select TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB, LOG_UTILIZATION_PERCENT from SYSIBMADM.LOG_UTILIZATION where db_name='QSMIAO'"TOTAL_LOG_USED_KB TOTAL_LOG_AVAILABLE_KB LOG_UTILIZATION_PERCENT
-------------------- ---------------------- -----------------------
24784 15975 60.80
1 record(s) selected.
2.）找到占用最老日志的进程，并找到其运行的SQL：
$
db2 get snapshot for database on qsmiao | grep -i oldestAppl id holding the oldest transaction =
7$
db2pd -db qsmiao -appDatabase Partition 0 -- Database QSMIAO -- Active -- Up 0 days 00:21:43 -- Date 2016-03-22-01.50.52.704810
Applications:
Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID CollectActData CollectActPartition
CollectSectionActuals
0x0780000000C30080 13 [000-00013] 1 8226 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092916 0 0 N C
N
0x0780000000CE0080 19 [000-00019] 1 9768 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092922 0 0 N C
N
0x0780000000C10080 12 [000-00012] 1 7969 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092915 0 0 N C
N
0x0780000000CC0080 18 [000-00018] 1 9511 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092921 0 0 N C
N
0x0780000000BF0080 11 [000-00011] 1 7712 UOW-Waiting 0 0 0 0 *LOCAL.DB2.160322092914 0 0 N C
N
0x0780000000CA0080 17 [000-00017] 1 9254 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092920 0 0 N C
N
0x0780000000D10080 30 [000-00030] 1 10026 UOW-Waiting 0 0 25 1 *LOCAL.e97q6c.160322093455 1 2 N C
N
0x0780000000BE0080 10 [000-00010] 1 7455 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092913 0 0 N C
N
0x0780000000C90080 16 [000-00016] 1 8997 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092919 0 0 N C
N
0x0780000000BB0080 9 [000-00009] 1 7198 UOW-Waiting 0 0 0 0 *LOCAL.DB2.160322092912 0 0 N C
N
0x0780000000C60080 15 [000-00015] 1 8740 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092918 0 0 N C
N
0x0780000000BA0080 8 [000-00008] 1 6941 UOW-Waiting 0 0 0 0 *LOCAL.DB2.160322092911 0 0 N C
N
0x0780000000C50080 14 [000-00014] 1 8483 ConnectCompleted 0 0 0 0 *LOCAL.DB2.160322092917 0 0 N C
N
0x0780000000B10080
7 [000-00007] 1 2572 UOW-Waiting 0 0
668 1 *LOCAL.e97q6c.160322092909
$
db2pd -db qsmiao -dynamicDatabase Partition 0 -- Database QSMIAO -- Active -- Up 0 days 00:23:51 -- Date 2016-03-22-01.53.00.949195
Dynamic Cache:
Current Memory Used 1203593
Total Heap Size 8136949
Cache Overflow Flag 0
Number of References 245
Number of Statement Inserts 75
Number of Statement Deletes 45
Number of Variation Inserts 40
Number of Statements 30
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x07000000549CF9A0 25 1 1 1 1 1 select TOTAL_LOG_USED_KB,
TOTAL_LOG_AVAILABLE_KB, LOG_UTILIZATION_PERCENT from SYSIBMADM.LOG_UTILIZATION where db_name='QSMIAO'
0x07000000532FFCC0 57 1 2 2 2 1 create table t2(id1 int, id2
int)
0x0700000053167A40 104 1 1 1 6 6 SELECT TRIGNAME FROM
SYSCAT.TRIGGERS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS'
0x07000000549CCF40 150 1 1 1 1 1 select total_log_used,
total_log_used + total_log_available as total, double(total_log_used)/(total_log_used + total_log_available) as PERCENT from
table(SNAP_GET_DB('qsmiao',-1))
0x070000005310EEC0 175 1 1 1 1 1 SELECT TABNAME, TABSCHEMA,
TYPE, CREATE_TIME FROM SYSCAT.TABLES WHERE TABSCHEMA = USER ORDER BY TABSCHEMA, TABNAME
0x0700000053162360 199 1 1 1 6 6 SELECT COLNAME, TYPENAME FROM
SYSCAT.COLUMNS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS'
0x07000000532DF800 230 1 1 1 1 1 SELECT IBM.TID, IBM.FID FROM
SYSIBM.SYSTABLES AS IBM, SYSTOOLS.HMON_ATM_INFO AS ATM WHERE ATM.STATS_FLAG <> 'Y' AND IBM.TYPE IN ( 'S', 'T' ) AND
ATM.CREATE_TIME = IBM.CTIME AND ATM.SCHEMA = IBM.CREATOR AND ATM.NAME = IBM.NAME ORDER BY IBM.FID, IBM.TID WITH UR
0x07000000532D6340 249 1 1 1 1 1 DELETE FROM
SYSTOOLS.HMON_ATM_INFO AS ATM WHERE NOT EXISTS ( SELECT * FROM SYSIBM.SYSTABLES AS IBM WHERE ATM.NAME = IBM.NAME AND
ATM.SCHEMA = IBM.CREATOR AND ATM.CREATE_TIME = IBM.CTIME ) WITH UR
0x07000000531003E0 309 1 1 1 1 1 select total_log_used,
total_log_used + total_log_available as total from table(SNAP_GET_DB('qsmiao',-1))
0x07000000532DFE60 314 1 1 1 4 4 SELECT POLICY FROM
SYSTOOLS.POLICY WHERE MED='DB2CommonMED' AND DECISION='NOP' AND NAME='CommonPolicy'
0x070000005316E180 328 1 1 1 2 2 CALL SYSINSTALLOBJECTS(
'DB2AC', 'V', NULL, NULL )
0x07000000532CFA20 390 1 1 1 141 141 SELECT CREATE_TIME FROM
SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? FOR UPDATE
0x07000000532DADA0 398 1 1 1 1 1 SELECT POLICY FROM
SYSTOOLS.POLICY WHERE MED='DB2TableMaintenanceMED' AND DECISION='TableRunstatsDecision' AND NAME='TableRunstatsPolicy'
0x07000000532DF1C0 455 1 1 1 1 1 UPDATE SYSTOOLS.HMON_ATM_INFO
AS ATM SET STATS_FLAG = 'N', REORG_FLAG = 'N' WHERE (ATM.SCHEMA, ATM.NAME) IN (SELECT IBM.CREATOR, IBM.NAME
FROM SYSIBM.SYSTABLES AS IBM WHERE IBM.TYPE = 'N' )
0x070000005314E500 467 1 1 1 6 6 CALL
SYSPROC.SYSINSTALLOBJECTS('POLICY','V','','')
0x07000000532CD6A0 532 1 1 1 1 1 SELECT CREATOR, NAME, CTIME
FROM SYSIBM.SYSTABLES WHERE TYPE='T' OR TYPE='S' OR TYPE='N' WITH UR
0x070000005330BA60 542 1 2 2 2 1 create table t2(id int)
0x07000000532DECA0 599 1 1 1 1 1 UPDATE SYSTOOLS.HMON_ATM_INFO
AS ATM SET ATM.STATS_FLAG = 'Y'
0x0700000053107460 621 1 1 1 3 3 select total_log_used,
total_log_used + total_log_available as total, (total_log_used/(total_log_used+total_log_available))*100 as PERCENT from
table(SNAP_GET_DB('qsmiao',-1))
0x070000005330FAA0 628 1 0 0 1 1 insert into t2
values(100,200)
0x070000005330F9A0 645 1 2 2 2 1 drop table t2
0x07000000532FFE40 655 1 1 1 1 1 select TOTAL_LOG_USED_KB,
TOTAL_LOG_AVAILABLE_KB, LOG_UTILIZATION_PERCENT from SYSIBMADM.LOG_UTILIZATION where db_name='qsmiao'
0x07000000549B7020 658 1 1 1 9 3 SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES WHERE TBSPACEID = ? AND TABLEID = ? AND CREATE_TIME = ? AND TYPE <> 'H' WITH UR
0x070000005310FD60
668 1 0 0 16 16
insert into t1 select * from
t10x070000005316B940 721 1 1 1 12 12 SET CURRENT LOCK TIMEOUT 5
0x07000000549BB560 779 1 1 1 9 3 SELECT STATS_FLAG FROM
SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? AND CREATE_TIME = ? WITH UR
0x070000005330B720 960 1 1 1 1 1 select total_log_used,
total_log_used + total_log_available as total, total_log_used/(total_log_used + total_log_available) as PERCENT from
table(SNAP_GET_DB('qsmiao',-1))
0x070000005316FDE0 992 1 1 1 2 2 SELECT TABNAME FROM
SYSCAT.TABLES WHERE TABNAME='HMON_ATM_INFO' AND TABSCHEMA='SYSTOOLS'
0x07000000532C94A0 996 2 1 1 1 1 UPDATE SYSTOOLS.HMON_ATM_INFO
SET STATS_LOCK = 'N', REORG_LOCK = 'N'
0x07000000532C5800 996 1 1 1 2 2 SELECT TABNAME FROM
SYSCAT.TABLES WHERE TABNAME='HMON_COLLECTION' AND TABSCHEMA='SYSTOOLS'
3.）将其force掉
$
db2 list applicationsAuth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
E97Q6C db2bp 30 *LOCAL.e97q6c.160322093455 QSMIAO 1
E97Q6C db2bp 7 *LOCAL.e97q6c.160322092909 QSMIAO 1
将这个进程force掉以释放日志
$
db2 "force application(7)"
以上就介绍了DB2活动日志满，如何找到占用最老日志的进程并将其force掉以释放日志，包括了方面的内容，希望对DB2有兴趣的朋友有所帮助。
本文网址链接：http://www.codes51.com/article/detail_498148.html
