一 长事务

1  长时间运行大于60分钟的长事务（视图 sysibmadm.MON_CURRENT_SQ）

 db2 "select substr(APPLICATION_HANDLE,1,6) handle, substr(APPLICATION_ID,1,40) APPLICATION_ID, UOW_ID,substr(APPLICATION_NAME,1,21) appl_name, substr(SESSION_AUTH_ID,1,10) SESSION_AUTH, 

 substr(CLIENT_APPLNAME,1,10) CLIENT_APP, substr(ACTIVITY_STATE,1,18) ACTIVITY_STATE, substr(ACTIVITY_TYPE,1,10) ACTIV_TYPE, ELAPSED_TIME_SEC, TOTAL_CPU_TIME, ROWS_READ, ROWS_RETURNED,

 QUERY_COST_ESTIMATE, DIRECT_READS,DIRECT_WRITES, substr(STMT_TEXT,1,1000) STMT_TEXT from sysibmadm.MON_CURRENT_SQL where ELAPSED_TIME_SEC > 300 order by ELAPSED_TIME_SEC DESC”

UOW_START_TIME             UOW_STOP_TIME               AGENT_ID

-------------------------- -------------------------- --------------------

2011-12-10-02.47.14.788208 -                                           8430

2 做快照

db2 get snapshot for application  agentid 【 8430 】

3 结束事务（和甲方DBA及应用人员商量）

db2 “force applications(8430)”

二  消耗cpu高的sql

消耗用户cpu高的sql（视图 snapdyn_sql）

select stmt_text from sysibmadm.snapdyn_sql order by  TOTAL_USER_CPU_TIME desc fetch first 5 rows only;

三  监控运行时间长的sql

select * from sysibmadm.long_running_sql order by elapsed_time_min desc fetch first 3 rows noly;

四 监控执行次数多的sql

select stmt_text,num_executions from sysibmadm.top_dynamic_sql order by  num_executions 

desc fetch first 5 rows only;

五 监控排序次数多的sql

select stmt_text,stmt_sorts from sysibmadm.top_dynamic_sql order by stmt_sorts desc fetch first 5 rows only;

六 ：针对IO瓶颈，确定了热点对象后，检查该热点对象执行次数多的sql

1 已经知道热点对象如：FLIGHTHISTORY

select stmt_text,num_executions from sysibmadm.snapdyn_sql

where stmt_text like '% LIGHTHISTORY%' order by  num_executions desc fetch first 5 rows only;