DB2 Monitor

时间段监控
临时开启：(IBM 建议使用临时开启，并开启如下五个参数)
db2 get monitor switches
db2 update monitor switches using BUFFERPOOL on;
db2 update monitor switches using LOCK on;
db2 update monitor switches using SORT on;
db2 update monitor switches using STATEMENT on;
db2 update monitor switches using TIMESTAMP on;

截取报告：
db2 get snapshot for dynamic sql on <dbname>  > /tmp/dynamic_1.out
db2 get snapshot for bufferpools on <dbname> > /tmp/buff_1.out
db2 get snapshot for locks on <dbname> > /tmp/locks_1.out
db2 get snapshot for dbm > /tmp/dbm_1.out
db2 get snapshot for db on <dbname> > /tmp/db_1.out
db2 get snapshot for tables on <dbname> /tmp/table_1.out

清空数据：
db2 reset monitor all

分析：
 1)BUFFERPOOL    >db2 connect to BANK    >db2 get snapshot for BUFFERPOOLS on BANK  ...  Buffer pool data logical reads = 16359  Buffer pool data physical reads = 209  Buffer pool index logical reads = 90  Buffer pool index physical reads = 52  ...  计算buffer pool hit ratio =      (1- ((Buffer pool data physical reads + Buffer pool index physical reads) /      (Buffer pool data logical reads + Buffer pool index logical reads))) * 100%  判断是否需要加大buffer pool
 >db2 get snapshot for db on BANK  ..  已分配的专用排序堆总数          = 0  已分配的共享排序堆总数                  = 0  共享排序堆高水位标记                    = 0  总计排序                        = 0  总计排序时间（毫秒）            = 未收集  排序溢出                        = 0  活动排序数                      = 0  ..  计算Sort overflow ratio = (Sort overflows / Total sorts) * 100%  判断是否需要加大sort heap (SORTHEAP)
 3)Log buffer  >db2 get snapshot for db on BANK  ..  已读取的日志页                  = 0  日志读取时间（秒.纳秒）                = 0.000000004  已写入的日志页                  = 939  日志写入时间（秒.纳秒）                = 33.000000004  ..  判断是否需要加大log buffer (LOGBUFSZ)
  数据缓冲区命中率（Data Bufferpool Hit Ratio）：> 95%  索引缓冲区命中率（Index Bufferpool Hit Ratio）：> 98%  平均读写时间（Read / Write Avarage Times）：< 5 ms  高速缓存区命中率（Package Cache Hit Ratio）：> 98%  编目缓冲区命中率（Catalog Cache Hit Ratio）：> 98%  锁升级及死锁（Lock Escalations and Deadlocks）：仅有少量的锁升级或死锁  日志缓冲区（Log Buffer Hit Ratio）：> 98%  排序溢出次数 / 排序次数 （Sort Overflow / Total Sort）：< 1%
实时监控：db2top -d <dbname>
d: database
D: dynamic sql
U: locks
b: bufferpools
m: memory
s: statements

查看缓冲池命中率：select substr(bp_name,1,12) total_hit_ratio_percent,data_hit_ratio_percent,index_hit_ratio_percent from sysibmadm.bp_hitratio

查看执行成本最高的SQL语句：select agent_id, rows_selected, rows_read from sysibmadm.application_performance

查看运行时间最长的SQL语句：select elapsed_time_min,appl_status, agent_id from sysibmadm.long_running_sql order by elapsed_time_min desc fetch first 5 rows only

查看准备和预编译时间最长的SQL语句:select num_executions,average_execution_time_s,prep_time_percent from sysibmadm.query_prep_cost order by num_executions desc

查看执行次数最多的SQL语句：select * from sysibmadm.TOP_DYNAMIC_SQL order by num_executions desc fetch first 5 rows only

查看排序次数最多的SQL语句：select STMT_SORTS, SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,60) as STMT_TEXT from sysibmadm.top_dynamic_sql order by STMT_SORTS desc fetch first 5 rows only

查看引发锁等待语句：select agent_id, substr(STMT_TEXT,1,100) as statement, STMT_ELAPSED_TIME_MS from table(snapshot_statement('<dbname>', -1)) as B where agent_id in (select agent_id_holding_lk from table (snapshot_lockwait('<dbname>',-1)) as A order by LOCK_WAIT_START_TIME asc fetch first 20 rows only) order by STMT_ELAPSED_TIME_MS DESC

查看表空间使用率：1. select TABLESPACE_NAME,TOTAL_PAGES,USED_PAGES,FREE_PAGES,rtrim(char(USED_PAGES*100/TOTAL_PAGES))||'%' as USED_RATE,rtrim(char(FREE_PAGES*100/TOTAL_PAGES))||'%' as Free_RATE from table (SNAPSHOT_TBS_CFG('COMMERCE', -1)) t
2. select substr(tbsp_name,1,18), tbsp_type,tbsp_free_size_kb,tbsp_utilization_percent from sysibmadm.tbsp_utilization

查看表大小：select tabname, npages from syscat.tables where tabname not like 'SYS%' and type='T'

类似ORACLE AWR工具：select application_handle,client_idle_wait_time,agent_wait_time,total_app_rqst_time,total_wait_time,total_cpu_time,app_rqsts_completed_total,lock_wait_time from table(mon_get_connection(cast(null as bigint),-2)) as t
将数据导入EXCEL制作成饼图