事务执行情况监控

800x600

一、建立workload并激活该工作负载

create workload wl_test applname('*') system_user('AMISUSR') session_user('AMISUSR') collect activity data with details;

--注意红色字体部分需要大写，如果需要activity事件监听器中包含事务中涉及的SQL执行情况的信息，在创建工作负载时必须带有蓝色字体部分的选项内容

alter workload wl_test enable;

二、建立事件监听器

create event monitor uow_evmon for unit of work write to UNFORMATTED EVENT TABLE autostart;

--注意uow事件监听器只记录已经完成的事务

CREATE EVENT MONITOR activity_evmon　FOR ACTIVITIES

       WRITE TO TABLE

ACTIVITY (TABLE ACTIVITY_DB2ACTIVITIES

                 IN USERSPACE1

                 PCTDEACTIVATE 100),       

ACTIVITYMETRICS (TABLE ACTIVITYMETRICS_DB2ACTIVITIES

                   IN USERSPACE1

                   PCTDEACTIVATE 100),      

ACTIVITYSTMT (TABLE ACTIVITYSTMT_DB2ACTIVITIES

                     IN USERSPACE1

                     PCTDEACTIVATE 100),

ACTIVITYVALS (TABLE ACTIVITYVALS_DB2ACTIVITIES

                     IN USERSPACE1

                     PCTDEACTIVATE 100),

CONTROL (TABLE CONTROL_DB2ACTIVITIES

                IN USERSPACE1

                PCTDEACTIVATE 100)

AUTOSTART;

--注意activity事件监听器记录的内容可以包含未提交的事务中包含的SQL信息

三、收集事件监听器监听信息

Call　EVMON_FORMAT_UE_TO_TABLES('UOW',NULL,NULL,NULL,NULL,NULL,NULL,-1,'select * from uow_evmon')—将非格式化监控信息进行格式化之后输出到监控表中

--该调用将先将uow_evmon中的非格式化信息转换为xml形式，再加载到目标表中

Flush event monitor uow_evmon;

Flush event monitor activity_evmon;

四、通过查看事件监听器的输出表了解事务的完整信息

select PARTICIPANT_NO,

LOCK_MODE,

LOCK_MODE_REQUESTED,

LOCK_CURRENT_MODE,

LOCK_STATUS,

TABLE_NAME,

LOCK_OBJECT_TYPE

from LOCK_PARTICIPANTs

where xmlid like '%DEADLOCK%';

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}