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))”
第四组：