前提开启监控参数:
[db2inst2@localhost event]$ db2 connect to ghan
[db2inst2@localhost event]$db2 update monitor switches using STATEMENT ON
[db2inst2@localhost event]$db2 update monitor switches using TABLE ON
[db2inst2@localhost event]$db2 update monitor switches using TIMESTAMP ON
[db2inst2@localhost event]$db2 update monitor switches using UOW ON
一、事件监控器文本方式
1.1、开启
[db2inst2@localhost event]$ db2 "create event monitor sql_trace_file for statements write to file '/db2inst2/autodb/event' "
DB20000I SQL 命令成功完成
1.2、激活
[db2inst2@localhost event]$ db2 "set event monitor sql_trace_file state = 1 "
DB20000I SQL 命令成功完成。
1.3、生成可读文本
[db2inst2@localhost event]$ db2evmon -db ghan -evm sql_trace_file > sql_trace_file.txt
1.4、查看结果：
......省略...........
56) Statement Event ...
Appl Handle: 1252
Appl Id: *LOCAL.db2inst2.151029081126
Appl Seq number: 00007
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute Immediate
Section : 203
Creator : NULLID
Package : SQLC2H22
Consistency Token : AAAAAFDb
Package Version ID :
Cursor :
Cursor was blocking: FALSE
Text : update empl set eno=999 where eno<10000
-------------------------------------------
Start Time: 2015-10-29 16:16:49.779623
Stop Time: 2015-10-29 16:16:52.269570
Elapsed Execution Time: 2.489947 seconds
Number of Agents created: 1
User CPU: 0.515969 seconds
System CPU: 0.000000 seconds
Statistic fabrication time (milliseconds): 0
Synchronous runstats time (milliseconds): 0
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 4560010
Rows written: 19996
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
Bufferpool xda logical page reads: 0
Bufferpool xda physical page reads: 0
Bufferpool temporary xda logical page reads: 0
Bufferpool temporary xda physical page reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
1.5、查看有那些事件管理器：
[db2inst2@localhost event]$ db2 "SELECT EVMONNAME,EVENT_MON_STATE(EVMONNAME)FROM SYSCAT.EVENTMONITORS "
EVMONNAME 2
-------------------------------------------------------------------------------------------------------------------------------- -----------
DB2DETAILDEADLOCK 1
SQL_TRACE 1
SQL_TRACE_FILE 1
1.6、停止事件管理器
[db2inst2@localhost event]$ db2 "set event monitor sql_trace_file state = 0 "
DB20000I SQL 命令成功完成。
[db2inst2@localhost event]$ db2 "SELECT EVMONNAME,EVENT_MON_STATE(EVMONNAME)FROM SYSCAT.EVENTMONITORS "
EVMONNAME 2
-------------------------------------------------------------------------------------------------------------------------------- -----------
DB2DETAILDEADLOCK 1
SQL_TRACE 1
SQL_TRACE_FILE 0
1.7、删除事件管理器
[db2inst2@localhost event]$ db2 "drop event monitor sql_trace_file"
DB20000I SQL 命令成功完成。
[db2inst2@localhost event]$ db2 "SELECT EVMONNAME,EVENT_MON_STATE(EVMONNAME)FROM SYSCAT.EVENTMONITORS "
EVMONNAME 2
-------------------------------------------------------------------------------------------------------------------------------- -----------
DB2DETAILDEADLOCK 1
SQL_TRACE 1
2 条记录已选择。
二、事件插入表方式
2.1开启会话监控
[b2inst2@localhost event]$ db2 update monitor switches using statement on
DB20000I UPDATE MONITOR SWITCHES 命令成功完成。
[db2inst2@localhost event]$ db2 get monitor switches
监视器记录开关
数据库分区号 0 的开关列表
缓冲池活动信息 (BUFFERPOOL) = OFF
锁定信息 (LOCK) = OFF
排序信息 (SORT) = OFF
SQL 语句信息 (STATEMENT) = ON 2015-10-29 16:32:42.243661
表活动信息 (TABLE) = OFF
获取时间戳记信息（时间戳记） = ON 2015-10-28 09:51:39.096275
工作单元信息 (UOW) = OFF
[db2inst2@localhost event]$
2.2 新建事件监控器
[db2inst2@localhost event]$ db2 "create event monitor sql_trace_file for statements write to table "
DB20000I SQL 命令成功完成。
[db2inst2@localhost event]$ db2 "set event monitor sql_trace_file state = 1 "
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 list tables |grep "FILE"
CONNHEADER_SQL_TRACE_FILE DB2INST2 T 2015-10-29-22.40.12.317309
CONTROL_SQL_TRACE_FILE DB2INST2 T 2015-10-29-22.40.12.402887
STMT_SQL_TRACE_FILE DB2INST2 T 2015-10-29-22.40.11.743193
[db2inst2@localhost ~]$
8 条记录已选择。
2.3 查看捕捉SQL情况
[db2inst2@localhost event]$
按照执行时间降序排列执行耗时最长的SQL语句（单位为秒）。为了确定这些语句，使用下面的SQL SELECT语句 前10条：
select stmt_text, (stop_time-start_time) as ExecutionTime from stmt_sql_trace where stmt_operation not in (7,8,9,19) order by decimal (ExecutionTime) desc fetch first 10 rows only
按照CPU时间降序排列最耗CPU时间的SQL语句（单位为微秒）。可以用下面这条查询来确定这些语句 前10条：
select stmt_text ,user_cpu_time as UserCPU from stmt_sql_trace_file where stmt_operation not in (7,8,9,19) order by usercpu desc fetch first 10 rows only;
2.4、停止事件状态及删除事件监控器
[db2inst2@localhost ~]$ db2 "set event monitor sql_trace_file state = 0 "
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "drop event monitor sql_trace_file"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 list tables
表/视图 模式 类型 创建时间
------------------------------- --------------- ----- --------------------------
CONNHEADER_SQL_TRACE_FILE DB2INST2 T 2015-10-29-16.33.37.436589
CONTROL_SQL_TRACE_FILE DB2INST2 T 2015-10-29-16.33.37.553326
EMPL DB2INST2 T 2015-10-23-03.28.03.862508
EMPLDD DB2INST2 T 2015-10-22-10.06.04.686401
STAFF DB2INST2 T 2015-10-26-09.38.25.482903
STMT_SQL_TRACE_FILE DB2INST2 T 2015-10-29-16.33.36.751731
T1 DB2INST2 T 2015-10-27-08.52.17.734637
T1_EXP DB2INST2 T 2015-10-27-08.52.44.016013
[db2inst2@localhost ~]$ db2 "drop table CONNHEADER_SQL_TRACE_FILE"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "drop table STMT_SQL_TRACE_FILE"
[db2inst2@localhost ~]$ db2 "drop table CONTROL_SQL_TRACE_FILE"
