一、db2监控动态SQL（快照监控）
db2示例用户登陆后，使用脚本语句db2 get snapshot for all on dbname>snap.out
也可以使用db2 get snapshot for dynamic SQL on dbname>snap.out,此语句是只记录上一语句中的部分
Tips：如果需要监控尽可能多的SQL语句，建议增加DBM配置参数
以下是动态SQL快照
Dynamic SQL Snapshot Result Database name = QINDB Database path = /db2home/db2inst1/db2inst1/NODE0000/SQL00002/ Number of executions = 3 --编译次数，为累加值 Number of compilations = 1 --SQL语句编译的最长时间 Worst preparation time (ms) = 87 Best preparation time (ms) = 87 Internal rows deleted = Not Collected Internal rows inserted = Not Collected Rows read = Not Collected Internal rows updated = Not Collected Rows written = Not Collected Statement sorts = Not Collected Statement sort overflows = Not Collected Total sort time = Not Collected Buffer pool data logical reads = Not Collected Buffer pool data physical reads = Not Collected Buffer pool temporary data logical reads = Not Collected Buffer pool temporary data physical reads = Not Collected Buffer pool index logical reads = Not Collected Buffer pool index physical reads = Not Collected Buffer pool temporary index logical reads = Not Collected Buffer pool temporary index physical reads = Not Collected Buffer pool xda logical reads = Not Collected Buffer pool xda physical reads = Not Collected Buffer pool temporary xda logical reads = Not Collected Buffer pool temporary xda physical reads = Not Collected Total execution time (sec.microsec)= Not Collected --SQL语句的总执行时间 Total user cpu time (sec.microsec) = Not Collected Total system cpu time (sec.microsec)= Not Collected Total statistic fabrication time (milliseconds) = Not Collected Total synchronous runstats time (milliseconds) = Not Collected Statement text = select * from len.cust_info where cust_id like '%09' --SQL语句文本
Number of executetions：可以帮助找到最优的那些重要语句，它对于帮助计算语句的平均执行时间也很有用。
Rows read :可以帮助识别读取行数最多的动态SQL语句，如果读取行数最多，通常意味着进行全表扫描。
Total execution time:是将语句每次执行时间加起来得到的总时间，我们可以利用该时间除以Number of executrtions,可以得到平均执行时间。如果语句的平均执行时间很长，可能是因为表扫描或者出现锁等待。
基于上述使用语句db2 get snapshot for all on dbname生成的文本内容，我们可以使用grep对快照的输出内容执行搜索
例如：
<1> 识别是否存在死锁
grep -n "Deadlocks detected" snap.out |grep -v "= 0" |more
<2> 搜索执行最频繁的SQL语句
grep -n "Number of executions" snap.out | grep -v "= 0" | sort -k 6rn |more
<3> 查询总的执行时间
grep -n "Total execution time" snap.out | grep -v "= 0.0"| sort -k 6nr| more
二、监控动态SQL(db2top)
使用db2top命令找出最频繁、最耗时的SQL(使用db2数据库用户登录)
db2top –d dbname
按D，进入到SQL监控界面(如果没开大写，可以使用shift+d)
按z，倒序排序
输入排序列的序号，从0开始
按L，输入SQL的序列号，查看SQL明细
以下为示例操作：
（1）db2inst1@Linux:/qinys> db2top -d qindb
（2）按D进入
（3）按z倒序排序，此处我输入3，表示按照执行时间排序
（4）按L输入SQL序列号，查看SQL详细
从上图我们就可以查看SQL详细了