实验十二 事件监控.pdf

《实验十二 事件监控.pdf》由会员分享，可在线阅读，更多相关《实验十二 事件监控.pdf（13页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、实验十二实验十二 事事件件监控监控 本实验中，你将使用DB2的事件监控工具收集多个应用程序的性能统计数据。第一步，创建一个文件事件监视器以收集语句和连接事件数据。使用 DB2BATCH 执行一组 5 个查询，并利用 DB2EVMON 工具把收集到的统计数据整理成报告。然后我们将创建一个表事件监视器收集多个表上的事务活动。使用 MULTI 程序生成一系列不同的应用程序事务。使用 SQL 查询语句审查表事件的监测数据。最后使用事务分析器来查看这些信息。第一部分第一部分 创建文件事创建文件事件件监视器监视器及及 DB2EVMON 命令命令的使用的使用 在本实验中，我们将创建一个文件事件监视器来收集

2、SQL 语句以及连接事件的监测统计信息。然后利用 DB2EVMON 命令生成报告。1.创建一个文件监视器STMTFILE来收集SQL语句及连接事务统计信息。事务监视器应该包含以下选项：NONBLOCKED BUFFERSIZE 8 MAXFILES 3 MAXFILESIZE 1024 FILE/home/inst411/stmt 在终端中使用以下命令来创建该监视器：cd$HOME mkdir stmt db2 activate db tp1 db2 connect to tp1 db2“CREATE EVENT MONITOR STMTFILE FOR STATEMENTS,CONNECTI

3、ONS WRITE TO FILE/home/inst411/stmt BUFFERSIZE 8 NONBLOCKED MAXFILES 3 MAXFILESIZE 1024“2.启用该监视器并使用 DB2BATCH 命令产生一组 SQL 查询的性能统计信息。在终端中使用以下命令检查 STMTFILE 是否已经启用 db2 select substr(evmonname,1,18)as event_monitor,event_mon_state(evmonname)as mon_state from syscat.eventmonitors EVENT_MONITOR MON_STATE-DB

4、2DETAILDEADLOCK 1 STMTFILE 0 2 条记录已选择。这表明监视器并没有启用，使用以下命令启用监视器。db2 set event monitor STMTFILE state 1 使用 db2batch执行一组查询来产生事务监视数据。cd$HOME/bin db2batch-d tp1-f batch1.sql-i complete batchmon1.out 停用 STMTFILE db2 set event monitor STMTFILE state 0 3.使用 DB2EVMON 命令把事务监视器中的数据整理成报告保存到文件 eventstmt.txt 中 db2

5、evmon-db tp1-evm stmtfile eventstmt.txt vi eventstmt.txt 找到第一条语句事务 18)Statement Event.Appl Handle:34 Appl Id:*LOCAL.inst411.090731071219 Appl Seq number:00001 Record is the result of a flush:FALSE -Type :Dynamic Operation:Prepare Section :4 Creator :NULLID Package :SYSSN400 Consistency Token :SYSLVL

6、01 Package Version ID :Cursor :SQL_CURSN400C4 Cursor was blocking:FALSE Text :SELECT HISTORY.BRANCH_ID,TELLER.TELLER_NAME,HISTORY.ACCTNAME,HISTORY.ACCT_ID,HISTORY.BALANCE FROM HISTORY AS HISTORY,TELLER AS TELLER WHERE HISTORY.TELLER_ID=TELLER.TELLER_ID AND HISTORY.BRANCH_ID BETWEEN 20 AND 29 ORDER B

7、Y HISTORY.BRANCH_ID ASC,HISTORY.ACCT_ID ASC -Start Time:2009-07-31 15:12:18.527201 Stop Time:2009-07-31 15:12:18.692865 Elapsed Execution Time:0.165664 seconds Number of Agents created:1 User CPU:0.009652 seconds System CPU:0.000000 seconds Statistic fabrication time(milliseconds):0 Synchronous runs

8、tats time (milliseconds):0 Fetch Count:0 Sorts:0 Total sort time:0 Sort overflows:0 Rows read:6 Rows written:0 Internal rows deleted:0 Internal rows updated:0 Internal rows inserted:0 Bufferpool data logical reads:18 Bufferpool data physical reads:6 Bufferpool temporary data logical reads:0 Bufferpo

9、ol temporary data physical reads:0 Bufferpool index logical reads:33 Bufferpool index physical reads:16 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 pag

10、e reads:0 Bufferpool temporary xda physical page reads:0 SQLCA:sqlcode:0 sqlstate:00000 19)Statement Event.Appl Handle:34 Appl Id:*LOCAL.inst411.090731071219 Appl Seq number:00001 Record is the result of a flush:FALSE -Type :Dynamic Operation:Open Section :4 Creator :NULLID Package :SYSSN400 Consist

11、ency Token :SYSLVL01 Package Version ID :Cursor :SQL_CURSN400C4 Cursor was blocking:TRUE Text :SELECT HISTORY.BRANCH_ID,TELLER.TELLER_NAME,HISTORY.ACCTNAME,HISTORY.ACCT_ID,HISTORY.BALANCE FROM HISTORY AS HISTORY,TELLER AS TELLER WHERE HISTORY.TELLER_ID=TELLER.TELLER_ID AND HISTORY.BRANCH_ID BETWEEN

12、20 AND 29 ORDER BY HISTORY.BRANCH_ID ASC,HISTORY.ACCT_ID ASC -Start Time:2009-07-31 15:12:18.693156 Stop Time:2009-07-31 15:12:18.693212 Elapsed Execution Time:0.000056 seconds Number of Agents created:1 User CPU:0.000049 seconds System CPU:0.000000 seconds Statistic fabrication time(milliseconds):0

13、 Synchronous runstats time (milliseconds):0 Fetch Count:0 Sorts:0 Total sort time:0 Sort overflows:0 Rows read:0 Rows written:0 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

14、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 x

15、da logical page reads:0 Bufferpool temporary xda physical page reads:0 SQLCA:sqlcode:0 sqlstate:00000 20)Statement Event.Appl Handle:34 Appl Id:*LOCAL.inst411.090731071219 Appl Seq number:00001 Record is the result of a flush:FALSE -Type :Dynamic Operation:Close Section :4 Creator :NULLID Package :S

16、YSSN400 Consistency Token :SYSLVL01 Package Version ID :Cursor :SQL_CURSN400C4 Cursor was blocking:TRUE Text :SELECT HISTORY.BRANCH_ID,TELLER.TELLER_NAME,HISTORY.ACCTNAME,HISTORY.ACCT_ID,HISTORY.BALANCE FROM HISTORY AS HISTORY,TELLER AS TELLER WHERE HISTORY.TELLER_ID=TELLER.TELLER_ID AND HISTORY.BRA

17、NCH_ID BETWEEN 20 AND 29 ORDER BY HISTORY.BRANCH_ID ASC,HISTORY.ACCT_ID ASC -Start Time:2009-07-31 15:12:18.693156 Stop Time:2009-07-31 15:12:20.091577 Elapsed Execution Time:1.398421 seconds Number of Agents created:1 User CPU:0.197032 seconds System CPU:0.000000 seconds Statistic fabrication time(

18、milliseconds):0 Synchronous runstats time (milliseconds):0 Fetch Count:24936 Sorts:2 Total sort time:233 Sort overflows:0 Rows read:24936 Rows written:0 Internal rows deleted:0 Internal rows updated:0 Internal rows inserted:0 Bufferpool data logical reads:1142 Bufferpool data physical reads:1 Buffer

19、pool temporary data logical reads:0 Bufferpool temporary data physical reads:0 Bufferpool index logical reads:49 Bufferpool index physical reads:28 Bufferpool temporary index logical reads:0 Bufferpool temporary index physical reads:0 Bufferpool xda logical page reads:0 Bufferpool xda physical page

20、reads:0 Bufferpool temporary xda logical page reads:0 Bufferpool temporary xda physical page reads:0 SQLCA:SQL0100W 找不到进行 FETCH、UPDATE 或 DELETE 操作的行；或者查询的结果是一个空表。SQLSTATE=02000 对于每一个 SQL 语句都会有三个记录。记录的类型分别是 PREPARE，OPEN，CLOSE，完整的统计信息保存在 CLOSE 记录中。Batch1.sql文件中包含五个查询，分别找到这五个查询的 CLOSE 段，记录以下信息 信息 查询 1

21、2 3 4 5 Fetch Count 24936 23524 51 23794 50335 Sorts 2 1 0 1 2 Total sort time 233 169 0 105 446 Sort overflows 0 0 0 0 0 Rows read 24936 23524 51204 335109 50335 第二部分第二部分 创建一个表事创建一个表事件件监视器监视器 在本实验中，我们将创建一个表事务监视器来收集 SQL 事务的统计信息，并使用 SQL 查询来审查结果。1.事务监视器的结果记录可以直接到数据库的表中。为了限制事务记录的大小，我们创建一个新的 DMS 类型表空间供事

22、务监视器使用。db2 connect to tp1 实验文件 montspace.ddl 中包含创建表空间所需要的命令 CREATE REGULAR TABLESPACE TP1EVENT PAGESIZE 4 K INITIALSIZE 10 M EXTENTSIZE 8 PREFETCHSIZE 16 使用该文件创建表空间 cd$HOME/bin db2-tvf montspace.ddl 2.db2evtbl命令可以用来生成包含一条创建事务监视器的 SQL 语句的文件，该语句可以指定特定的监视要求。使用 db2evtbl生成这样一个文件。db2evtbl-evm tp1samp conn

23、ections,transactions,statements evtsample.ddl 查看该文件：vi evtsample.ddl CREATE EVENT MONITOR tp1samp FOR CONNECTIONS,TRANSACTIONS,STATEMENTS WRITE TO TABLE CONNHEADER(TABLE CONNHEADER_tp1samp INCLUDES(AGENT_ID,APPL_ID,APPL_NAME,AUTH_ID,CLIENT_DB_ALIAS,CLIENT_NNAME,CLIENT_PID,CLIENT_PLATFORM,CLIENT_PRDI

24、D,CLIENT_PROTOCOL,CODEPAGE_ID,CONN_TIME,CORR_TOKEN,EXECUTION_ID,SEQUENCE_NO,TERRITORY_CODE),STMT(TABLE STMT_tp1samp INCLUDES(AGENT_ID,AGENTS_TOP,APPL_ID,BLOCKING_CURSOR,CONSISTENCY_TOKEN,CREATOR,CURSOR_NAME,EVMON_FLUSHES,FETCH_COUNT,INT_ROWS_DELETED,INT_ROWS_INSERTED,INT_ROWS_UPDATED,PACKAGE_NAME,PA

25、CKAGE_VERSION_ID,PARTIAL_RECORD,POOL_DATA_L_READS,POOL_DATA_P_READS,POOL_INDEX_L_READS,POOL_INDEX_P_READS,POOL_TEMP_DATA_L_READS,POOL_TEMP_DATA_P_READS,POOL_TEMP_INDEX_L_READS,POOL_TEMP_INDEX_P_READS,POOL_TEMP_XDA_L_READS,POOL_TEMP_XDA_P_READS,POOL_XDA_L_READS,POOL_XDA_P_READS,ROWS_READ,ROWS_WRITTEN

26、,SECTION_NUMBER,SEQUENCE_NO,SORT_OVERFLOWS,SQL_REQ_ID,SQLCABC,SQLCAID,SQLCODE,SQLERRD1,SQLERRD2,SQLERRD3,SQLERRD4,SQLERRD5,SQLERRD6,SQLERRM,SQLERRP,SQLSTATE,SQLWARN,START_TIME,STATS_FABRICATE_TIME,STMT_OPERATION,STMT_TEXT,STMT_TYPE,STOP_TIME,SYNC_RUNSTATS_TIME,SYSTEM_CPU_TIME,TOTAL_SORT_TIME,TOTAL_S

27、ORTS,USER_CPU_TIME),XACT(TABLE XACT_tp1samp INCLUDES(AGENT_ID,APPL_ID,EVMON_FLUSHES,LOCK_ESCALS,LOCK_WAIT_TIME,LOCKS_HELD_TOP,PARTIAL_RECORD,PREV_UOW_STOP_TIME,ROWS_READ,ROWS_WRITTEN,SEQUENCE_NO,STOP_TIME,SYSTEM_CPU_TIME,TPMON_ACC_STR,TPMON_CLIENT_APP,TPMON_CLIENT_USERID,TPMON_CLIENT_WKSTN,UOW_LOG_S

28、PACE_USED,UOW_START_TIME,UOW_STATUS,USER_CPU_TIME,X_LOCK_ESCALS),CONN(TABLE CONN_tp1samp INCLUDES(ACC_CURS_BLK,AGENT_ID,APPL_ID,APPL_PRIORITY,APPL_PRIORITY_TYPE,APPL_SECTION_INSERTS,APPL_SECTION_LOOKUPS,APPL_STATUS,AUTHORITY_BITMAP,AUTHORITY_LVL,BINDS_PRECOMPILES,CAT_CACHE_HEAP_FULL,CAT_CACHE_INSERT

29、S,CAT_CACHE_LOOKUPS,CAT_CACHE_OVERFLOWS,CAT_CACHE_SIZE_TOP,COMMIT_SQL_STMTS,COORD_NODE,DDL_SQL_STMTS,DEADLOCKS,DIRECT_READ_REQS,DIRECT_READ_TIME,DIRECT_READS,DIRECT_WRITE_REQS,DIRECT_WRITE_TIME,DIRECT_WRITES,DISCONN_TIME,DYNAMIC_SQL_STMTS,ELAPSED_EXEC_TIME,EVMON_FLUSHES,FAILED_SQL_STMTS,HASH_JOIN_OV

30、ERFLOWS,HASH_JOIN_SMALL_OVERFLOWS,INT_AUTO_REBINDS,INT_COMMITS,INT_DEADLOCK_ROLLBACKS,INT_ROLLBACKS,INT_ROWS_DELETED,INT_ROWS_INSERTED,INT_ROWS_UPDATED,LOCK_ESCALS,LOCK_TIMEOUTS,LOCK_WAIT_TIME,LOCK_WAITS,OLAP_FUNC_OVERFLOWS,PARTIAL_RECORD,PKG_CACHE_INSERTS,PKG_CACHE_LOOKUPS,POOL_DATA_L_READS,POOL_DA

31、TA_P_READS,POOL_DATA_WRITES,POOL_INDEX_L_READS,POOL_INDEX_P_READS,POOL_INDEX_WRITES,POOL_READ_TIME,POOL_TEMP_DATA_L_READS,POOL_TEMP_DATA_P_READS,POOL_TEMP_INDEX_L_READS,POOL_TEMP_INDEX_P_READS,POOL_TEMP_XDA_L_READS,POOL_TEMP_XDA_P_READS,POOL_WRITE_TIME,POOL_XDA_L_READS,POOL_XDA_P_READS,POOL_XDA_WRIT

32、ES,PREFETCH_WAIT_TIME,REJ_CURS_BLK,ROLLBACK_SQL_STMTS,ROWS_DELETED,ROWS_INSERTED,ROWS_READ,ROWS_SELECTED,ROWS_UPDATED,ROWS_WRITTEN,SELECT_SQL_STMTS,SEQUENCE_NO,SORT_OVERFLOWS,STATIC_SQL_STMTS,SYSTEM_CPU_TIME,TOTAL_HASH_JOINS,TOTAL_HASH_LOOPS,TOTAL_OLAP_FUNCS,TOTAL_SORT_TIME,TOTAL_SORTS,UID_SQL_STMTS

33、,UNREAD_PREFETCH_PAGES,USER_CPU_TIME,X_LOCK_ESCALS,XQUERY_STMTS),CONTROL(TABLE CONTROL_tp1samp INCLUDES(EVENT_MONITOR_NAME,MESSAGE,MESSAGE_TIME);可以看出，该文件会为每一种事务监视记录创建一个表来存储相应的记录。需要监视的元素可以有选择的增加或者减少。3.使用实验文件 tp1tran.ddl 创建表事件监视器。该文件中包含以下的 SQL 语句：CREATE EVENT MONITOR tp1tran FOR TRANSACTIONS WRITE TO

34、TABLE CONNHEADER(TABLE CONNHEADER_tp1tran,in tp1event,INCLUDES(AGENT_ID,APPL_ID,APPL_NAME,AUTH_ID,CLIENT_DB_ALIAS,CLIENT_NNAME,CLIENT_PID,CLIENT_PLATFORM,CLIENT_PRDID,CLIENT_PROTOCOL,CODEPAGE_ID,CONN_TIME,CORR_TOKEN,EXECUTION_ID,SEQUENCE_NO,TERRITORY_CODE),XACT(TABLE XACT_tp1tran,in tp1event,INCLUDE

35、S(AGENT_ID,APPL_ID,EVMON_FLUSHES,LOCK_ESCALS,LOCK_WAIT_TIME,LOCKS_HELD_TOP,PARTIAL_RECORD,PREV_UOW_STOP_TIME,ROWS_READ,ROWS_WRITTEN,SEQUENCE_NO,STOP_TIME,SYSTEM_CPU_TIME,UOW_LOG_SPACE_USED,UOW_START_TIME,UOW_STATUS,USER_CPU_TIME,X_LOCK_ESCALS),CONTROL(TABLE CONTROL_tp1tran,in tp1event,INCLUDES(EVENT

36、_MONITOR_NAME,MESSAGE,MESSAGE_TIME)MANUALSTART;执行该文件：db2-tvf tp1tran.ddl 4.启用该事务监视器收集交易事务的统计信息。以TP1MIX.CFG配置文件运行MULTI程序以产生事务处理 启用监视器 db2 set event monitor TP1TRAN state 1 打开一个新的终端窗口，执行 MULTI 程序 cd$HOME/bin multi tp1mix.cfg 运行完毕后关闭 MULTI 程序，并停用 TP1TRAN 监视器。db2 set event monitor TP1TRAN state 0 5.使用以下

37、查询语句查询 XACTTP1TRAN 表中的部分交易统计信息 SELECT AGENT_ID,STOP_TIME-UOW_START_TIME AS DURATION,ROWS_READ,ROWS_WRITTEN,UOW_LOG_SPACE_USED,LOCK_WAIT_TIME FROM XACT_TP1TRAN WHERE ROWS_READ 30 ORDER BY ROWS_READ DESC;该语句保存在 evtquery1.sql 中 执行该脚本 db2-tvf evtquery1.sql evtreport1.txt 查看输出 vi evtreport1.txt 第三部分第三部分 事件分析器事件分析器 1.打开新的终端窗口，使用以下命令运行事务分析工具 db2eva 数据名称选择 TP1 事件监视器名选择 TP1TRAN 确定 选择一条记录，使用菜单 所选下寻至 事务 这里列出了事务的详细信息，你也可以选择其中一条记录然后选择 所选下寻至数据元素来查看这些详细信息。关闭所有窗口。