先建立一个监控器
db2 "create event monitor SQLCOST for statements write to file '/home/db2inst1'"
再设置事务状态为打开
db2 "set event monitor SQLCOST state=1"
注：1为打开，0为关闭，收集数据之后，记得关闭你的监控器，否则。。。
跑你的测试后，在你的/home/db2inst1目录下会生成一些evm文件
用下面指令获取诊断信息：
db2evmon -db eos51 -evm SQLCOST>sqlcost1.txt
完成之后删除你的监控器
db2 "drop event monitor SQLCOST"
生成的采样例子，从下面的例子中，你可以清除的看到SQL执行的时间，CPU消耗情况，排序是否溢出，BufferPool的使用情况，根据这些信息，SQL的执行效率一目了然：
26) Statement Event ...
Appl Handle: 336
Appl Id: C0A80421.O905.0ABDA5065446
Appl Seq number: 0657
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 7
Creator : NULLID
Package : SYSSN300
Consistency Token : SYSLVL01
Package Version ID :
Cursor : SQL_CURSN300C7
Cursor was blocking: FALSE
Text : update WFProcessInst set relateData=? where processInstID= ?
-------------------------------------------
Start Time: 04/25/2007 14:57:19.402248
Stop Time: 04/25/2007 14:57:19.409622
Exec Time: 0.007374 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds [licl1]
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0 [licl2]
Rows read: 1
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 9
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 3
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0 [licl3]
SQLCA:
sqlcode: 0
sqlstate: 00000
[licl1]SQL执行时间和CPU消耗情况
[licl2]SQL的排序情况，可以看到这个SQL没有排序，当然也没有排序溢出
[licl3]Bufferpool的使用情况，逻辑读和物理读的对比
del.icio.us Tags:
database ,
db2 ,
tuning