#监控执行成本最高的sql语句
select agent_id,rows_selected,rows_read from sysibmadm.snapappl fetch first 10 rows only;
#监控运行最长的sql语句
select substr(appl_name,1,15) as Appl_name,elapsed_time_min as "Elapsed Min.",appl_status as "Status ",substr(authid,1,10) as auth_id,
substr(inbound_comm_address,1,15) as "IP Address",substr(stmt_text,1,30) as "SQL Statement"
from sysibmadm.long_running_sql order by 2 desc;
select * from sysibmadm.long_running_sql order by 2 desc;
#监控sql准备和预编译时间最长的sql语句
average_execution_time_s sql语句平均执行时间
prep_time_ms 最长的sql预编译时间
prep_time_precent 预编译时间占整个执行时间的百分比
select num_executions,average_execution_time_s,prep_time_ms,prep_time_precent,substr(stmt_text,1,40) as "SQL_Text"
from sysibmadm.query_prep_cost where average_execution_time_s>0 order by prep_time_precent desc;
#监控执行次数最多的sql语句
Select num_executions "Num Execs",average_execution_time_s as "Avg Time(sec)",stmt_sorts as "Num Sorts",sorts_per_execution as "Sorts Per Stmt",
substr(stmt_text,1,35) as "SQL Stmt" from sysibmadm.top_dynamic_sql where num_executions>0 order by 1 desc fetch first 5 rows only;
Select num_executions "Num Execs",average_execution_time_s as "Avg Time(sec)",stmt_sorts as "Num Sorts",sorts_per_execution as "Sorts Per Stmt",
substr(stmt_text,1,35) as "SQL Stmt" from sysibmadm.top_dynamic_sql where num_executions>0 order by 2 desc fetch first 5 rows only;
#监控排序次数最多的sql语句
select stmt_sorts,sorts_per_execution,substr(stmt_text,1,60) as stmt_text from top_dynamic_sql order by stmt_sorts fetch first 5 rows only;
#建立EXPLAIN所需要的表。
在SQLLIB目录下的MISC下：EXPLAIN.DDL
#查看表状态
db2 load query table DM.TM_CHNL_INV_MO
db2 "select * from table(mon_get_pkg_cache_stmt('D', NULL, NULL, -2)) as T order by total_cpu_time fetch first 10 rows only"
（查看消耗CPU时间最多的10个静态SQL语句。将参数‘D’改为‘S’，可以得到对应动态SQL语句的结果，改为NULL则得到对应所有SQL语句的结果）
原创文章，转载请注明： 转载自肚腩照明月'blog
本文链接地址: DB2监控语句[总结]
文章的脚注信息由WordPress的wp-posturl插件自动生成
DB2监控语句[总结]