DB2可以设置,在诊断日志db2diag.log中出现某些字符串时,自动抓取您想要的数据。
比如,您想要在db2diag.log中出现'lock escalation'时,自动收集所有应用的snapshot和db2pd -locks的数据:
发生锁升级时,db2diag.log示例如下:
2017-07-11-00.03.05.697614-420 E98324E966 LEVEL: Info
PID : 23570 TID : 140136465557248 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-9 APPID: *LOCAL.inst105.170711070207
AUTHID : INST105 HOSTNAME: db2a
EDUID : 18 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:1
MESSAGE : ADM5501I DB2 is performing lock escalation. The affected application
is named "db2bp", and is associated with the workload name
"SYSDEFAULTUSERWORKLOAD" and application ID
"*LOCAL.inst105.170711070207" at member "0". The total number of
locks currently held is "104", and the target number of locks to hold
is "52". The current statement being executed is "select * from t1
with RS". Reason code: "1"
DATA #1 : Hex integer, 8 bytes
0x830000000004003A
抓取的办法如下,
1.)将db2cos文件从sqllib/bin目录拷贝到sqllib/adm目录,并确保所有人都有执行权限,
2.)修改db2cos的"DIAGSTRING"部分,这里主要是修改为抓取数据的命令:
3). 使用db2pdcfg命令,开启监控,diagstr设置为"lock escalation", count=1表示只在第一次出现lock escalation时收集数据
db2diag -A
db2pdcfg -catch diagstr="lock escalation" count=1
4) 等出现lock escalation之后,并闭db2cos
db2pdcfg -catch clear
5) 收集的数据可以在db2dump目录下找到,其中snapshot的数据放在*.cos.txt中,db2pd-locks的数据放在了db2pd.locks中
*.cos.txt
db2diag.log
db2pd.locks
其实,db2cos不止可以根据db2diag.log中的字符串搜索来触发数据自动收集,还可以根据其中的报错码,以上面的db2diag.log为例子,报错码为 ADM5501I 。具体使用可以参考下面的链接
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0023252.html
http://www-01.ibm.com/support/docview.wss?uid=swg21684132
附:如何快速模拟一个锁升级
inst105@db2a:~$ cat lockEscltn.sh
#!/bin/bash
db2 "update db cfg for sample using LOCKLIST 4"
db2 "connect to sample"
db2 "drop table t1"
db2 "create table t1(id int)"
db2 "insert into t1 values(1),(2)"
for i in {1..10}
do
db2 "insert into t1 select * from t1"
done
db2 "select * from t1 with RS"
比如,您想要在db2diag.log中出现'lock escalation'时,自动收集所有应用的snapshot和db2pd -locks的数据:
发生锁升级时,db2diag.log示例如下:
2017-07-11-00.03.05.697614-420 E98324E966 LEVEL: Info
PID : 23570 TID : 140136465557248 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-9 APPID: *LOCAL.inst105.170711070207
AUTHID : INST105 HOSTNAME: db2a
EDUID : 18 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:1
MESSAGE : ADM5501I DB2 is performing lock escalation. The affected application
is named "db2bp", and is associated with the workload name
"SYSDEFAULTUSERWORKLOAD" and application ID
"*LOCAL.inst105.170711070207" at member "0". The total number of
locks currently held is "104", and the target number of locks to hold
is "52". The current statement being executed is "select * from t1
with RS". Reason code: "1"
DATA #1 : Hex integer, 8 bytes
0x830000000004003A
抓取的办法如下,
1.)将db2cos文件从sqllib/bin目录拷贝到sqllib/adm目录,并确保所有人都有执行权限,
2.)修改db2cos的"DIAGSTRING"部分,这里主要是修改为抓取数据的命令:
"DIAGSTRING")
echo "Diag String Caught" >> $logfile
cd $diagpath
if [ ! -n "$database" ]
then
db2 get snapshot for all applications >> $logfile
db2pd -db $database -locks >> db2pd.locks
else
db2 get snapshot for all applications >> $logfile
db2pd -db $database -locks >> db2pd.locks
fi
;;
3). 使用db2pdcfg命令,开启监控,diagstr设置为"lock escalation", count=1表示只在第一次出现lock escalation时收集数据
db2diag -A
db2pdcfg -catch diagstr="lock escalation" count=1
4) 等出现lock escalation之后,并闭db2cos
db2pdcfg -catch clear
5) 收集的数据可以在db2dump目录下找到,其中snapshot的数据放在*.cos.txt中,db2pd-locks的数据放在了db2pd.locks中
*.cos.txt
db2diag.log
db2pd.locks
其实,db2cos不止可以根据db2diag.log中的字符串搜索来触发数据自动收集,还可以根据其中的报错码,以上面的db2diag.log为例子,报错码为 ADM5501I 。具体使用可以参考下面的链接
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0023252.html
http://www-01.ibm.com/support/docview.wss?uid=swg21684132
附:如何快速模拟一个锁升级
inst105@db2a:~$ cat lockEscltn.sh
#!/bin/bash
db2 "update db cfg for sample using LOCKLIST 4"
db2 "connect to sample"
db2 "drop table t1"
db2 "create table t1(id int)"
db2 "insert into t1 values(1),(2)"
for i in {1..10}
do
db2 "insert into t1 select * from t1"
done
db2 "select * from t1 with RS"