目的:
测试DB2使用事件监视器,本脚本摘录自DB2安装目录admin_scripts/tbeventmon.db2。
版本:Windows DB2 Express-C V9.7
操作步骤:
使用"db2cmd db2 -t"进入交互模式,执行后续操作。
connect to sample;
-- To create event monitors for event types statements , deadlocks and
-- connections which are written to a table
CREATE EVENT MONITOR dlmon
FOR STATEMENTS, DEADLOCKS WITH DETAILS, CONNECTIONS
WRITE TO TABLE
CONNHEADER (TABLE CONNHEADER_dlmon,
INCLUDES (AGENT_ID,
APPL_ID,
APPL_NAME,
TERRITORY_CODE )),
DEADLOCK (TABLE DEADLOCK_dlmon),
DLCONN (TABLE mydept.dlconnections,
EXCLUDES (
LOCK_OBJECT_NAME,
LOCK_OBJECT_TYPE,
TABLESPACE_NAME )),
STMT (TABLE STMT_dlmon,
INCLUDES (AGENT_ID,
APPL_ID,
CREATOR,
INT_ROWS_DELETED,
INT_ROWS_INSERTED,
INT_ROWS_UPDATED,
ROWS_READ,
ROWS_WRITTEN,
SQLCODE,
SQLSTATE,
SQLWARN,
START_TIME,
STMT_OPERATION,
STMT_TEXT )),
CONN ,
CONTROL (TABLE CONTROL_dlmon,
INCLUDES (EVENT_MONITOR_NAME,
MESSAGE,
MESSAGE_TIME ))
BUFFERSIZE 8 NONBLOCKED MANUALSTART;
-- Activate event monitor
SET EVENT MONITOR dlmon STATE=1;
-- The following SQL statements generate sample events that populate
-- CONN_dlmon table
CONNECT RESET;
CONNECT TO SAMPLE;
-- Reactivate event monitor
SET EVENT MONITOR dlmon STATE = 1;
-- Retrieve data from the event monitor tables
SELECT agent_id, appl_id, territory_code FROM CONNHEADER_dlmon;
SELECT agent_id, appl_id, int_rows_inserted,
system_cpu_time FROM CONN_dlmon;
SELECT * FROM CONTROL_dlmon;
-- Deactivate event monitor
SET EVENT MONITOR dlmon STATE = 0;
-- Drop event monitor
DROP EVENT MONITOR dlmon;
-- Dropping the monitor doesn't remove tables. They have to be
-- dropped explicitly
DROP TABLE CONNHEADER_dlmon;
DROP TABLE DEADLOCK_dlmon;
DROP TABLE mydept.dlconnections;
DROP TABLE STMT_dlmon;
DROP TABLE CONN_dlmon;
DROP TABLE CONTROL_dlmon;
-- db2evtbl is a tool that generates sample CREATE EVENT MONITOR SQL
-- statements that can be used when defining event monitors that write
-- to sql tables.
-- Uuncomment the following statement to generate a CREATE EVENT
-- MONITOR sql statement
-- ! db2evtbl -evm dlmon STATEMENTS, DEADLOCKS WITH DETAILS, CONNECTIONS;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-702294/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-702294/