db2常用动态性能视图及监控表函数是什么

63次阅读

共计 20071 个字符，预计需要花费 51 分钟才能阅读完成。

这期内容当中丸趣 TV 小编将会给大家带来有关 db2 常用动态性能视图及监控表函数是什么，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

DB2 常用动态视图、监控表函数总结

小秦我在这里总结了下 DB2 中比较常用的动态视图和监控表函数。等有空的时候会再发一篇和监控有关的文章，介绍一下 DB2 中监控的具体组件以及相关的监控模式、监控参数。

查看容器信息：
db2
“select
tbsp_name,container_name,container_type,total_pages,usable_pages,accessible,1.0*fs_total_size_kb/1024
as fs_total_size_mb,1.0*fs_used_size_kb/1024 as
fs_used_size_mb,1.0*fs_used_size_kb/fs_total_size_kb as fs_used_pct from
SYSIBMADM.CONTAINER_UTILIZATION”

查看 app 的 rows read/rows return:
db2“select application_handle,rows_read_per_rows_returned from sysibmadm.mon_connection_summary”

查看当前正在执行的耗时最长的 SQL：
db2
“select
elapsed_time_sec,activity_state,activity_type,application_handle from
sysibmadm.mon_current_sql order by elapsed_time_sec desc fetch first 5
rows only”

查看缓冲池的效率：
db2“SELECT BP_NAME, AVG_WRITE_TIME,
SYNC_WRITES_PERCENT,AVG_SYNC_WRITE_TIME, AVG_ASYNC_WRITE_TIME FROM
SYSIBMADM.MON_BP_UTILIZATION”

查看某个服务类中的各种耗时元素信息，单位是毫秒：
db2
“SELECT SUM(TOTAL_WAIT_TIME) AS WAIT,SUM(TOTAL_COMPILE_PROC_TIME) AS
COMPILE,SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS
IMP_COMPILE,SUM(TOTAL_SECTION_PROC_TIME) AS
SECTION,SUM(total_routine_user_code_proc_time) as
ROUTINE_USER_CODE,SUM(TOTAL_COMMIT_PROC_TIME) AS
COMMIT,SUM(TOTAL_REORG_PROC_TIME) AS REORG,SUM(TOTAL_RUNSTATS_PROC_TIME)
AS RUNSTATS,SUM(TOTAL_ROLLBACK_PROC_TIME) AS
ROLLBACK,SUM(TOTAL_LOAD_PROC_TIME) AS
LOAD,SUM(total_connect_request_proc_time) as CONNECT_REQUEST FROM
TABLE(MON_GET_SERVICE_SUBCLASS(‘SYSDEFAULTUSERCLASS’,’SYSDEFAULTSUBCLASS’,NULL))”

查看总的耗用时间百分比：
db2
“WITH PCTPROC AS (SELECT SUM(TOTAL_SECTION_TIME) AS SECT_TIME,
SUM(TOTAL_SECTION_PROC_TIME) AS SECT_PROC_TIME,SUM(TOTAL_COMPILE_TIME)
AS COMP_TIME, SUM(TOTAL_COMPILE_PROC_TIME) AS
COMP_PROC_TIME,SUM(TOTAL_IMPLICIT_COMPILE_TIME) AS IMP_C_TIME,
SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS
IMP_C_PROC_TIME,SUM(TOTAL_COMMIT_TIME) AS COMMIT_TIME,
SUM(TOTAL_COMMIT_PROC_TIME) AS COMMIT_PROC_TIME,SUM(TOTAL_ROLLBACK_TIME)
AS ROLLBACK_TIME, SUM(TOTAL_ROLLBACK_PROC_TIME) AS
ROLLBACK_PROC_TIME,SUM(TOTAL_RUNSTATS_TIME) AS RUNSTATS_TIME,
SUM(TOTAL_RUNSTATS_PROC_TIME)AS RUNSTATS_PROC_TIME,SUM(TOTAL_REORG_TIME)
AS REORG_TIME, SUM(TOTAL_REORG_PROC_TIME) AS
REORG_PROC_TIME,SUM(TOTAL_LOAD_TIME) AS LOAD_TIME,
SUM(TOTAL_LOAD_PROC_TIME) AS LOAD_PROC_TIME FROM
TABLE(MON_GET_CONNECTION(NULL, -2)) AS METRICS) SELECT CASE WHEN
SECT_TIME 0 THEN DEC((FLOAT(SECT_PROC_TIME) / FLOAT(SECT_TIME)) *
100,5,1) ELSE NULL END AS SECT_PROC_PCT, CASE WHEN COMP_TIME 0 THEN
DEC((FLOAT(COMP_PROC_TIME) / FLOAT(COMP_TIME)) * 100,5,1) ELSE NULL END
AS COMPILE_PROC_PCT,CASE WHEN IMP_C_TIME 0 THEN
DEC((FLOAT(IMP_C_PROC_TIME) / FLOAT(IMP_C_TIME)) * 100,5,1) ELSE NULL
END AS IMPL_COMPILE_PROC_PCT, CASE WHEN ROLLBACK_TIME 0 THEN
DEC((FLOAT(ROLLBACK_PROC_TIME) / FLOAT(ROLLBACK_TIME)) * 100,5,1) ELSE
NULL END AS ROLLBACK_PROC_PCT, CASE WHEN COMMIT_TIME 0 THEN
DEC((FLOAT(COMMIT_PROC_TIME) / FLOAT(COMMIT_TIME)) * 100,5,1) ELSE NULL
END AS COMMIT_PROC_PCT, CASE WHEN RUNSTATS_TIME 0 THEN
DEC((FLOAT(RUNSTATS_PROC_TIME) / FLOAT(RUNSTATS_TIME)) * 100,5,1) ELSE
NULL END AS RUNSTATS_PROC_PCT, CASE WHEN REORG_TIME 0 THEN
DEC((FLOAT(REORG_PROC_TIME) / FLOAT(REORG_TIME)) * 100,5,1) ELSE NULL
END AS REORG_PROC_PCT, CASE WHEN LOAD_TIME 0 THEN
DEC((FLOAT(LOAD_PROC_TIME) / FLOAT(LOAD_TIME)) * 100,5,1) ELSE NULL END
AS LOAD_PROC_PCT FROM PCTPROC”

根据 lock 名获取信息：
db2“SELECT
SUBSTR(NAME,1,20) AS NAME,SUBSTR(VALUE,1,50) AS VALUE FROM TABLE(
MON_FORMAT_LOCK_NAME(‘03001100000000000000000054’)) as LOCK”

MON_GET_ACTIVITY：只会的获取正在运行的 application 信息。换句话说，应该就不能查看历史信息了，除非使用 ACTIVITY 事件监视器：
一些基础信息：
[db2inst1@DB2_105
~]$ db2“select
application_handle,LOCAL_START_TIME,uow_id,activity_id,parent_uow_id,parent_activity_id,activity_state,activity_type
FROM table(MON_GET_ACTIVITY(NULL, -1))”
第一组：
db2“select
application_handle,uow_id,activity_id,total_cpu_time,rows_read,rows_returned,query_cost_estimate
FROM table(MON_GET_ACTIVITY(NULL, -1))”
第二组：
db2“select
application_handle,uow_id,activity_id,direct_reads,direct_writes,effective_isolation,effective_lock_timeout,effective_query_degree
FROM table(MON_GET_ACTIVITY(NULL, -1))”
第三组：
db2“select
application_handle,uow_id,activity_id,direct_reads,direct_writes,effective_isolation,effective_lock_timeout,effective_query_degree
FROM table(MON_GET_ACTIVITY(NULL, -1))”
第四组：
db2“select application_handle,uow_id,activity_id,stmt_text,eff_stmt_text FROM table(MON_GET_ACTIVITY(NULL, -1))”

获取 mempool 信息，单位都是 KB：
db2
“SELECT varchar(memory_set_type, 20) AS
set_type,varchar(memory_pool_type,20) AS pool_type,varchar(db_name, 20)
AS dbname,memory_pool_used,memory_pool_used_hwm FROM
TABLE(MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, -2))”

MON_GET_PKG_CACHE_STMT：和上面那个 MON_GET_ACTIVITY 的区别在于，上面那个东西只会的去查找正在运行的 XXX，而这个则从 package
cache 里找。不过，对于那些 package cache 中已经不存在的东东来说，就看不到啦，要看的话开 package cache 监视器:
第一组：
db2“select section_type,effective_isolation,num_executions FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2))”
第二组：
db2
“select
lock_wait_time,log_buffer_wait_time,log_disk_wait_time,evmon_wait_time,diaglog_write_wait_time,pool_read_time,pool_write_time,direct_read_time,direct_write_time,prefetch_wait_time,total_section_proc_time
FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2))”

获取 Bufferpool 命中率：
db2
“WITH BPMETRICS AS (SELECT bp_name, pool_data_l_reads +
pool_temp_data_l_reads +pool_index_l_reads + pool_temp_index_l_reads
+pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
pool_data_p_reads + pool_temp_data_p_reads +pool_index_p_reads +
pool_temp_index_p_reads +pool_xda_p_reads + pool_temp_xda_p_reads as
physical_reads,member FROM TABLE(MON_GET_BUFFERPOOL(”,-2)) AS METRICS)
SELECT VARCHAR(bp_name,20) AS bp_name,logical_reads,physical_reads,CASE
WHEN logical_reads 0 THEN DEC((1 – (FLOAT(physical_reads) /
FLOAT(logical_reads))) * 100,5,2) ELSE NULL END AS HIT_RATIO,member FROM
BPMETRICS”
或
db2“select substr(bp_name,1,30) as
BP_NAME,data_hit_ratio_percent,index_hit_ratio_percent,prefetch_ratio_percent
from sysibmadm.mon_bp_utilization where bp_name not like‘IBMSYSTEM%”

查看热表：
db2
“select substr(tabschema,1,15) as tabschema,substr(tabname,1,15) as
tabname,TAB_TYPE,TABLE_SCANS,ROWS_READ,(ROWS_INSERTED+ROWS_UPDATED+ROWS_DELETED)
as rows_IUD from table(MON_GET_TABLE(null,null,null)) order by
rows_read desc”

查看索引的相关信息：
db2“select substr(mon.tabname,1,16)
as table,member,substr(cat.indname,1,16) as IX_Name,mon.IID as
Index_id,mon.index_scans, mon.index_only_scans from
table(mon_get_index(NULL,NULL,-2)) as mon,syscat.indexes as cat where
mon.tabname=cat.tabname and mon.tabschema=cat.tabschema and
mon.iid=cat.iid order by mon.tabname”

查看排序的相关信息：
db2“with
dbcfg1 as(select int(value) as sheapthres_shr from sysibmadm.dbcfg where
name=’sheapthres_shr’) select
sheapthres_shr,sort_shrheap_allocated,dec((100*sort_shrheap_allocated)/sheapthres_shr,5,2)
as sheap_alloc_pct,dec((100*sort_shrheap_top)/sheapthres_shr,5,2) as
MAX_SHEAP_ALLOC_PCT,total_sorts,total_sort_time,1.0 *
total_sort_time/(total_sorts + 1) as avg_sort_time,sort_overflows,1.0 *
sort_overflows / (total_sorts+1) as overflow_pct,active_sorts from
dbcfg1,sysibmadm.snapdb”

查看包缓存的一些信息：
db2“with dbcfg1 as(select
int(value) as pckcachesz from sysibmadm.dbcfg where name=’pckcachesz’)
select
pckcachesz,pkg_cache_lookups,pkg_cache_inserts,pkg_cache_num_overflows,100*pkg_cache_size_top/(pckcachesz*4096)
as pkg_cache_alloc_pct from dbcfg1,sysibmadm.snapdb”

查看编目缓存的一些信息：
db2
“with dbcfg1 as(select int(value) as catcachesz_pages from
sysibmadm.dbcfg where name=’catalogcache_sz’) select
catcachesz_pages,CAT_CACHE_LOOKUPS,CAT_CACHE_INSERTS,CAT_CACHE_OVERFLOWS,(1.0-1.0*CAT_CACHE_INSERTS/CAT_CACHE_LOOKUPS)
as cat_insert_pct,100*CAT_CACHE_SIZE_TOP/(catcachesz_pages*4096) as
cat_cache_alloc_pct from dbcfg1,sysibmadm.snapdb”

查看日志使用信息：
db2
“select 1.0*TOTAL_LOG_AVAILABLE/1024/1024 as
total_log_available_mb,1.0*TOTAL_LOG_USED/1024/1024 as
total_log_used_mb,1.0*SEC_LOG_USED_TOP/1024/1024 as
sec_log_used_top_mb,1.0*TOT_LOG_USED_TOP/1024/1024 as
total_log_used_top,1.0*SEC_LOGS_ALLOCATED/1024/1024 as
sec_logs_allocated_mb,APPL_ID_OLDEST_XACT from TABLE(SNAP_GET_DB(CAST
(NULL AS VARCHAR(128)), -2))”

查看日志速率：
db2“select
log_reads,(1.0*LOG_READ_TIME_S+1.0*LOG_READ_TIME_NS)/1000000000.0 as
log_read_time_s,LOG_WRITES,(1.0*LOG_WRITE_TIME_S +
1.0*LOG_WRITE_TIME_NS)/1000000000.0 as
log_write_time_s,NUM_LOG_WRITE_IO,NUM_LOG_READ_IO,(1.0*LOG_READ_TIME_S+1.0*LOG_READ_TIME_NS)/1000000000.0/(1.0+num_log_read_io)
as read_io_speed_s,(1.0*LOG_WRITE_TIME_S +
1.0*LOG_WRITE_TIME_NS)/1000000000.0/(1.0+num_log_write_io) as
write_io_speed_s,NUM_LOG_PART_PAGE_IO,NUM_LOG_BUFFER_FULL,NUM_LOG_DATA_FOUND_IN_BUFFER
FROM TABLE(SNAP_GET_DB(CAST (NULL AS VARCHAR(128)), -2))”

语句执行时间，可以调整那个 order by 来看：
db2
“select num_executions,total_act_time as
total_time_ms,(total_act_time/num_executions) as
avg_time_ms,total_sorts,(total_sorts/num_executions) as
sorts_per_stmt,substr(stmt_text,1,35) as sql_stmt from
table(mon_get_pkg_cache_stmt(‘d’,NULL,NULL,-1)) as dyn_cache where
num_executions 0 order by 2 desc fetch first 5 rows only”

查看长时间运行的 SQL 语句：
db2
“select substr(application_name,1,16) as appl_name,elapsed_time_sec as
elapsed_secondes,substr(activity_state,1,20) as
status,substr(session_auth_id,1,16) as
auth_id,total_cpu_time,rows_returned,substr(stmt_text,1,30) as sql_stmt
from sysibmadm.mon_current_sql order by 2 desc”

查看当前 app 的 wait time：
db2
“select application_handle as
app_id,total_wait_time,pool_read_time,pool_write_time,log_disk_wait_time,log_buffer_wait_time,tcpip_send_wait_time,tcpip_recv_wait_time,lock_wait_time
from table(mon_get_connection(NULL,-1)) order by total_wait_time”

查看锁等：
db2
“select substr(lw.hld_application_name,1,10) as
hold_app,substr(lw.hld_userid,1,10) as
holder,substr(HLD_CURRENT_STMT_TEXT,1,16) as
holder_stmt,substr(lw.req_application_name,1,10) as
wait_app,substr(lw.req_userid,1,10) as waiter,substr(REQ_STMT_TEXT,1,16)
as waiter_stmt,lw.lock_mode,lw.lock_object_type,substr(lw.tabname,1,10)
as tabname,substr(lw.tabschema,1,10) as
schema,lw.lock_wait_elapsed_time as waiting_seconds from
sysibmadm.mon_lockwaits lw”

查看持有的锁信息：
db2“select
substr(conn.application_name,1,10) as
application,substr(conn.system_auth_id,1,10) as
authid,conn.num_locks_held as number_of_locks,conn.lock_escals as
escalations,conn.lock_timeouts as lock_timeouts,conn.deadlocks as
deadlocks,(conn.lock_wait_time/100) as lock_wait_time_ms from
table(mon_get_connection(null,-1)) as conn”

查看锁内存相关：
db2“with
dbcfg1 as (select float(bigint(value)*4096) as locklist from
sysibmadm.dbcfg where name=’locklist’),dbcfg2 as (select
float(bigint(value)) as maxlocks from sysibmadm.dbcfg where
name=’maxlocks’) select dec((lock_list_in_use/locklist)*100,4,1) as
LOCK_LIST_USED_PCT,dec((lock_list_in_use/(locklist*(maxlocks/100))*100),4,1)
as TO_MAXLOCK_PCT,appls_cur_cons as
NUMBER_OF_CONS,lock_list_in_use/appls_cur_cons as
avg_lock_mem_per_con_bytes from dbcfg1,dbcfg2,sysibmadm.snapdb”

查看全表扫描的信息：
db2
“select substr(session_auth_id,1,10) as
auth_id,substr(application_name,1,20) as appl_name,io_wait_time_percent
as io_wait_pct,rows_read_per_rows_returned as rows_read_vs_returned from
sysibmadm.mon_connection_summary”

查看脏页换取的百分比：
db2“with
db_snap as(select float(pool_drty_pg_steal_clns) as
pg_steal,float(pool_drty_pg_thrsh_clns) as
chg_pg_thrsh,float(pool_lsn_gap_clns) as
softmax,float(pool_drty_pg_steal_clns+pool_drty_pg_thrsh_clns+pool_lsn_gap_clns)
as total_clns from sysibmadm.snapdb where
(pool_drty_pg_steal_clns+pool_drty_pg_thrsh_clns+pool_lsn_gap_clns)
0) select dec((pg_steal/total_clns)*100,4,1) as
steals_pct,dec((chg_pg_thrsh/total_clns)*100,4,1) as
threshold_pct,dec((softmax/total_clns)*100,4,1) as softmax_pct from
db_snap”

查看预取方面的信息（asny 读就是直接读数据，而不是从索引读数据，且一开始读的时候还不在缓存池中，sync 读就死直接从索引读数据，且不用从索引在去扫表）：
db2
“with bp_info as(select substr(bp_name,1,30) as
bp_name,unread_prefetch_pages,pool_async_data_reads+pool_async_index_reads
async_reads,pool_data_p_reads+pool_index_p_reads+pool_temp_data_p_reads+pool_temp_index_p_reads
as total_reads from table(mon_get_bufferpool(null,-1)) as bp_stats
where bp_name not like‘IBMSYSTEM%’) select
bp_name,unread_prefetch_pages,dec(100*(total_reads-async_reads)/total_reads,5,2)
as sync_reads_pct,dec(100*unread_prefetch_pages/total_reads,5,2) as
unread_pages_pct from bp_info”

查看 memory pool 的信息：
db2“select pool_id,pool_secondary_id,pool_cur_size,pool_watermark from sysibmadm.snapdb_memory_pool order by 1,2”

查看最老事务的信息：
db2
“select substr(uow.workload_occurrence_state,1,20) as
status,substr(uow.session_auth_id,1,10) as authid,uow.application_handle
as appl_handle,int(uow.uow_log_space_used/1024/1024) as
log_used_MB,uow.total_act_time as
total_activity_time_ms,uow.total_act_wait_time as
total_activity_wait_time,uow.uow_start_time as uow_start_time from
sysibmadm.snapdb db,table(mon_get_unit_of_work(null,-1)) as uow where
uow.application_handle=db.appl_id_oldest_xact”

查看表空间相关信息：
db2
“select substr(tbsp_name,1,30) as tablespace_name,tbsp_type as
type,TBSP_USING_AUTO_STORAGE,TBSP_AUTO_RESIZE_ENABLED,substr(tbsp_state,1,20)
as status,(tbsp_total_size_kb/1024) as
size_MB,(100-tbsp_utilization_percent) as
free_space_pct,(((100-tbsp_utilization_percent)*tbsp_usable_size_kb)/100000)
as free_space_mb from sysibmadm.mon_tbsp_utilization”

获取 PATH 的信息：
db2“select substr(type,1,20) as type,substr(path,1,50) as path from sysibmadm.dbpaths order by type”

当前 connection 的一些信息，如平均 CPU 使用时间、ROWS_READ_PER_ROWS_RETURNED：
db2
“select application_handle as
app_hdl,total_app_commits,total_app_rollbacks,avg_rqst_cpu_time,ROWS_READ_PER_ROWS_RETURNED
from sysibmadm.MON_CONNECTION_SUMMARY”
当前 connection 中 wait time 在总请求时间中的所占比例：
db2
“select application_handle as
app_hdl,ROUTINE_TIME_RQST_PERCENT,RQST_WAIT_TIME_PERCENT from
sysibmadm.MON_CONNECTION_SUMMARY”
当前 connection 中各种 WAIT TIME 所占时间百分比（IO 里没有算 LOG 的时间）：
db2
“select application_handle as
app_hdl,IO_WAIT_TIME_PERCENT,LOCK_WAIT_TIME_PERCENT,AGENT_WAIT_TIME_PERCENT,NETWORK_WAIT_TIME_PERCENT
from sysibmadm.MON_CONNECTION_SUMMARY”
当前 connection 中实际干活的用的时间的百分比：
db2
“select application_handle as
app_hdl,SECTION_SORT_PROC_TIME_PERCENT,(SECTION_PROC_TIME_PERCENT-SECTION_SORT_PROC_TIME_PERCENT)
SECTION_NON_SORT_TIME_PERCENT,COMPILE_PROC_TIME_PERCENT,TRANSACT_END_PROC_TIME_PERCENT,UTILS_PROC_TIME_PERCENT
from sysibmadm.MON_CONNECTION_SUMMARY”

获取当前 rows read/rows returned 前 10 的 SQL：
db2
“select APPLICATION_HANDLE,substr(ACTIVITY_STATE,1,15) as
state,substr(ACTIVITY_TYPE,1,15) as
type,ELAPSED_TIME_SEC,TOTAL_CPU_TIME,ROWS_READ,ROWS_RETURNED,QUERY_COST_ESTIMATE,substr(STMT_TEXT,1,15)
as stmt from sysibmadm.mon_current_sql order by
ROWS_READ/(ROWS_RETURNED+1) desc fetch first 10 rows only”

获取热表信息：
db2
“SELECT varchar(tabschema,20) as tabschema, varchar(tabname,20) as
tabname, sum(rows_read) as total_rows_read, sum(rows_inserted) as
total_rows_inserted, sum(rows_updated) as total_rows_updated,
sum(rows_deleted) as total_rows_deleted,sum(TABLE_SCANS) as TABLE_SCANS
FROM TABLE(MON_GET_TABLE(”,”,-2)) AS t GROUP BY tabschema, tabname ORDER
BY TABLE_SCANS DESC fetch first 20 rows only”

–Time Span Monitor SQL
1. 整体级别查看（在服务类、workload 级别）：
查看某个服务类中的各种耗时元素信息，单位是毫秒。先用这个看下某个服务子类下的耗时的实际情况：
db2
“SELECT SUM(TOTAL_WAIT_TIME) AS WAIT,SUM(TOTAL_COMPILE_PROC_TIME) AS
COMPILE,SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS
IMP_COMPILE,SUM(TOTAL_SECTION_PROC_TIME) AS
SECTION,SUM(total_routine_user_code_proc_time) as
ROUTINE_USER_CODE,SUM(TOTAL_COMMIT_PROC_TIME) AS
COMMIT,SUM(TOTAL_REORG_PROC_TIME) AS REORG,SUM(TOTAL_RUNSTATS_PROC_TIME)
AS RUNSTATS,SUM(TOTAL_ROLLBACK_PROC_TIME) AS
ROLLBACK,SUM(TOTAL_LOAD_PROC_TIME) AS
LOAD,SUM(total_connect_request_proc_time) as CONNECT_REQUEST FROM
TABLE(MON_GET_SERVICE_SUBCLASS(‘SYSDEFAULTUSERCLASS’,’SYSDEFAULTSUBCLASS’,NULL))”
老版本的 DB2 用这个：
db2
“SELECT SUM(TOTAL_WAIT_TIME) AS WAIT,SUM(TOTAL_COMPILE_PROC_TIME) AS
COMPILE,SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS
IMP_COMPILE,SUM(TOTAL_SECTION_PROC_TIME) AS
SECTION,SUM(TOTAL_COMMIT_PROC_TIME) AS COMMIT,SUM(TOTAL_REORG_PROC_TIME)
AS REORG,SUM(TOTAL_RUNSTATS_PROC_TIME) AS
RUNSTATS,SUM(TOTAL_ROLLBACK_PROC_TIME) AS
ROLLBACK,SUM(TOTAL_LOAD_PROC_TIME) AS LOAD FROM
TABLE(MON_GET_SERVICE_SUBCLASS(‘SYSDEFAULTUSERCLASS’,’SYSDEFAULTSUBCLASS’,NULL))”
或用这个基于 XML 的：
db2
“SELECT SUBSTR(T.SERVICE_SUPERCLASS_NAME,1,19) as
SUPERCLASS,SUBSTR(T.SERVICE_SUBCLASS_NAME,1,19) as
SUBCLASS,T.MEMBER,SUBSTR(U.METRIC_NAME, 1,20) AS
METRIC_NAME,SUBSTR(U.PARENT_METRIC_NAME,1,20) AS
PARENT_NAME,U.TOTAL_TIME_VALUE,U.COUNT FROM
TABLE(MON_GET_SERVICE_SUBCLASS_DETAILS(NULL, NULL, -2)) AS T,
TABLE(MON_FORMAT_XML_TIMES_BY_ROW(T.DETAILS)) AS U where
U.PARENT_METRIC_NAME is null or U.PARENT_METRIC_NAME=’TOTAL_RQST_TIME’
order by superclass,subclass,total_time_value desc”

然后，如果发现等待时间过长，就要用下面这个去看下到底是哪一部分的等待时间过长：
db2
“SELECT sum(agent_wait_time) as agent,sum(wlm_queue_time_total) as
wlm,sum(lock_wait_time) as lock,sum(log_buffer_wait_time) as
log_buffer,sum(log_disk_wait_time) as
log_disk,sum(tcpip_recv_wait_time+tcpip_send_wait_time) as
tcpip,sum(ipc_recv_wait_time+ipc_send_wait_time) as
ipc,sum(fcm_recv_wait_time+fcm_send_wait_time) as
fcm,sum(audit_subsystem_wait_time+audit_file_write_wait_time) as
audit,sum(diaglog_write_wait_time) as diaglog,sum(pool_write_time) as
pool_write,sum(pool_read_time) as pool_read,sum(direct_read_time) as
direct_read,sum(direct_write_time) as direct_write,sum(evmon_wait_time)
as evmon,sum(total_extended_latch_wait_time) as
latch,sum(prefetch_wait_time) as prefetch,sum(comm_exit_wait_time) as
comm_exit,sum(ida_send_wait_time+ida_recv_wait_time) as
ida,sum(cf_wait_time) as cluster_caching_facility,sum(reclaim_wait_time)
as reclaim,sum(spacemappage_reclaim_wait_time) as space_map FROM
TABLE(MON_GET_SERVICE_SUBCLASS(‘SYSDEFAULTUSERCLASS’,’SYSDEFAULTSUBCLASS’,NULL))”
老版本的用这个：
db2
“SELECT sum(agent_wait_time) as agent,sum(wlm_queue_time_total) as
wlm,sum(lock_wait_time) as lock,sum(log_buffer_wait_time) as
log_buffer,sum(log_disk_wait_time) as
log_disk,sum(tcpip_recv_wait_time+tcpip_send_wait_time) as
tcpip,sum(ipc_recv_wait_time+ipc_send_wait_time) as
ipc,sum(fcm_recv_wait_time+fcm_send_wait_time) as
fcm,sum(pool_write_time) as pool_write,sum(pool_read_time) as
pool_read,sum(direct_read_time) as direct_read,sum(direct_write_time) as
direct_write FROM
TABLE(MON_GET_SERVICE_SUBCLASS(‘SYSDEFAULTUSERCLASS’,’SYSDEFAULTSUBCLASS’,NULL))”
或者，用下面这个更加方便的基于 XML 的东东，这里看到的 TOTAL_WAIT_TIME 是下面总的时间之和：
db2
“SELECT SUBSTR(TFXML.WORKLOAD_NAME, 1, 13) AS
WORKLOAD_NAME,SUBSTR(WAITS.METRIC_NAME, 1, 25) AS
METRIC_NAME,WAITS.TOTAL_TIME_VALUE,WAITS.COUNT FROM TABLE(
MON_GET_WORKLOAD_DETAILS(NULL, -2) ) AS TFXML,TABLE(
MON_FORMAT_XML_WAIT_TIMES_BY_ROW(TFXML.DETAILS)) AS WAITS ORDER BY
WAITS.TOTAL_TIME_VALUE DESC”

PS：
可以用这个看看某一部分的 PROC 时间和总时间，来看下 WAIT 时间是否正常：
db2
“SELECT SUBSTR(T.SERVICE_SUPERCLASS_NAME,1,19) AS
SUPERCLASS,SUBSTR(T.SERVICE_SUBCLASS_NAME,1,19) AS
SUBCLASS,T.MEMBER,SUBSTR(COMP.METRIC_NAME,1,20) AS
METRIC_NAME,SUBSTR(COMP.PARENT_METRIC_NAME,1,20) AS
PARENT_NAME,COMP.TOTAL_TIME_VALUE AS TOTAL_TIME,COMP.PROC_TIME_VALUE AS
TOTAL_PROC_TIME,COMP.COUNT FROM TABLE
(MON_GET_SERVICE_SUBCLASS_DETAILS(NULL, NULL,-2)) AS
T,TABLE(MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW(T.DETAILS)) AS COMP WHERE
COMP.PARENT_METRIC_NAME IS NOT NULL order by (COMP.TOTAL_TIME_VALUE –
COMP.PROC_TIME_VALUE) desc”

2. 如果在上面发现了问题，那么就要去定位了。一般问题都是发生在 SQL 上。换句话说，我们要找到哪条 SQL，这条 SQL 的某个指标过大，造成了上面的 WAIT 等指标过大。
比如，如果 POOL_READ_TIME 时间过大，那么：
db2
“select
SECTION_TYPE,NUM_EXECUTIONS,POOL_READ_TIME,substr(STMT_TEXT,1,32) as
stmt from TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) order by
POOL_READ_TIME desc”
又比如，LOCK_WAIT_TIME 过大，那么：
db2“select
SECTION_TYPE,NUM_EXECUTIONS,LOCK_WAIT_TIME,LOCK_ESCALS,LOCK_ESCALS,DEADLOCKS,LOCK_TIMEOUTS,substr(STMT_TEXT,1,32)
as stmt from TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) order
by LOCK_WAIT_TIME desc”
如果 DIRECT_READ_TIME 过大，那么：
db2“select
SECTION_TYPE,NUM_EXECUTIONS,direct_read_time,substr(STMT_TEXT,1,32) as
stmt from TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) order by
direct_read_time desc fetch first 5 rows only”
还比如，TOTAL_SECTION_PROC_TIME 过大，那么（另外，还可以从 CPU 角度去看，但这其实就是另外一种衡量的方法了）：
db2
“select
SECTION_TYPE,NUM_EXECUTIONS,total_section_time,total_section_sort_time,total_col_time,total_routine_non_sect_time,substr(STMT_TEXT,1,32)
as stmt from TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) order
by total_section_time desc”
一般 WAIT 的指标都可以在 MON_GET_PKG_CACHE_STMT 去找对应的指标来看，可以查查 Infocenter。
从 CPU 的角度的化，建议用下面的这个视图 MON_PKG_CACHE_SUMMARY（注意，有些单位是微秒，有些则是毫秒）：
db2
“select
section_type,TOTAL_STMT_EXEC_TIME,AVG_STMT_EXEC_TIME,TOTAL_CPU_TIME,AVG_CPU_TIME,TOTAL_LOCK_WAIT_TIME,AVG_LOCK_WAIT_TIME,TOTAL_IO_WAIT_TIME,AVG_IO_WAIT_TIME,PREP_TIME,ROWS_READ_PER_ROWS_RETURNED,AVG_ACT_WAIT_TIME,AVG_LOCK_ESCALS,AVG_RECLAIM_WAIT_TIME,AVG_SPACEMAPPAGE_RECLAIM_WAIT_TIME,substr(stmt_text,1,16)
from SYSIBMADM.MON_PKG_CACHE_SUMMARY”
9.7 用下面这个：
db2“select
section_type,TOTAL_STMT_EXEC_TIME,AVG_STMT_EXEC_TIME,TOTAL_CPU_TIME,AVG_CPU_TIME,TOTAL_LOCK_WAIT_TIME,AVG_LOCK_WAIT_TIME,TOTAL_IO_WAIT_TIME,AVG_IO_WAIT_TIME,PREP_TIME,ROWS_READ_PER_ROWS_RETURNED,substr(stmt_text,1,16)
from SYSIBMADM.MON_PKG_CACHE_SUMMARY order by AVG_STMT_EXEC_TIME desc
fetch first 5 rows only”
或者试试这个，可以按照平均执行时间或排序时间来看下问题 SQL：
db2
“select
num_executions,AVERAGE_EXECUTION_TIME_S,STMT_SORTS,SORTS_PER_EXECUTION,substr(stmt_text,1,16)
as stmt from SYSIBMADM.TOP_DYNAMIC_SQL“