--每天需要监控的内容

--监控CPU和内存
vmstat  1  5

--监控IO
iostat 1 5
sar -d 1 5

--查看表空间的存储空间状态
--查看数据库目录的可用空间 dbpath
--查看日志目录所在的文件系统的剩余空间
df -k /logdir

--查看归档的日志
db2adutl query db testdb

--检查诊断日志和管理通知日志
db2diag.log
db2instname.nfy

--检查数据库备份
db2 list history backup all for testdb
db2 list history archive log all for testdb
db2 list history all for  testdb 

--查看数据库表的状态
db2 "select tabname,colcount,status from syscat.tables
where tabschema not like 'SYS%' order by tabname
"
status = N 正常
status = C 需要检查完整性

db2 set integrity for tabname immediate checked

--查看缓存池命中率，数据逻辑读，数据物理读
db2 "select SNAPSHOT_TIMESTAMP,char(bp_name,15)
,TOTAL_LOGICAL_READS,TOTAL_PHYSICAL_READS,total_hit_ratio_percent
,data_logical_reads,data_physical_reads,data_hit_ratio_percent
from sysibmadm.bp_hitratio
"
db2 "select SNAPSHOT_TIMESTAMP,char(bp_name,15)
,TOTAL_LOGICAL_READS,TOTAL_PHYSICAL_READS,total_hit_ratio_percent
,index_logical_reads,index_physical_reads,index_hit_ratio_percent
from sysibmadm.bp_hitratio
"

--监控执行成本最高的SQL
db2 "select agent_id,PERCENT_ROWS_SELECTED 
from sysibmadm.appl_performance
order by percent_rows_selected
"
--监控运行最长的SQL
db2 "select agent_id,appl_status,elapsed_time_min
from sysibmadm.long_running_sql
order by elapsed_time_min desc fetch first 5 rows only
"

--监控执行次数最多的SQL
db2 "select SNAPSHOT_TIMESTAMP, NUM_EXECUTIONS, char(STMT_TEXT,150)
from sysibmadm.top_dynamic_sql
order by num_executions desc fetch first 10 rows only
"
--监控排序次数最多的SQL
db2 "select stmt_sorts,char(STMT_TEXT,150),SNAPSHOT_TIMESTAMP,sorts_per_execution
from sysibmadm.top_dynamic_sql
order by stmt_sorts desc fetch first 10 rows only
"

--监控引起锁等待的SQL
db2 "select agent_id, char(stmt_text,100) as statement, stmt_elapsed_time_ms
from table(snapshot_statement('TESTDB',-1)) as B
where agent_id in
(select agent_id_holding_lk from table(snapshot_lockwait('TESTDB',-1)) as A
  order by lock_wait_start_time asc fetch first 20 rows only
)
order by stmt_elapsed_time_ms desc
" 

 --查看选择的行数与读取的行数的比例 应大于 20%
db2 "select float(rows_selected)/rows_read from sysibmadm.snapdb where rows_read!=0 "

--表扫描次数 scans
db2pd -d testdb -tcbstats

--查看扫描次数多的表相关的语句
db2 " select stmt_text from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T WHERE stmt_text like '%TESTTAB%' "

--检查锁相关的等待，超时，升级，死锁
db2 get snapshot for all on testdb   > log.txt
grep -n "Deadlocks detected" log.txt  |  grep -v "= 0"
grep -n "Lock waits" log.txt  |  grep -v "= 0"
grep -n "Lock escalation" log.txt  |  grep -v "= 0"
grep -n "Lock Timeouts" log.txt  |  grep -v "= 0"

--查看当前运行最频繁、最消耗资源的SQL （Costly SQL）
db2 "select substr(stmt_text,1,100) as stmt_text
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T
where rows_read!=0
order by rows_returned/rows_read asc fetch first 10 rows only
"
--执行最频繁的SQL
db2 "select substr(stmt_text,1,100) as stmt_text, num_executions
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T
order by num_executions desc fetch first 10 rows only
"
--排序最多的语句
db2 "select substr(stmt_text,1,100) as stmt_text , total_sorts
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-2)) AS T
order by  total_sorts desc fetch first 10 rows only
"

----------------------------------------------------------------------------------
-- 每周需要监控的内容
----------------------------------------------------------------------------------
--检查备份完整性
db2ckbkp -h  TESTDB.0.db2inst1.NODE0000.CATN0000.20131023221025.001

--检查是否需要runstats
没有搜集过统计信息的表
db2 "select tabname from syscat.tables where stats_time  is null "
没有收集过统计信息的索引
db2 "select indname from syscat.indexes where stats_time  is null "
15天没有更新过统计信息的表
db2 "select tabname from syscat.tables where stats_time  < current timestamp - 15 days"

--监控表是否需要重组
db2 reorgchk  update statistics  on table all

--监控新对象
db2 "select tabschema, tabname, create_time
  from syscat.tables
  where create_time > '2014-01-01.00.00.00.000000'
"

--包缓存中的SQL
db2 "select substr(stmt_text,1,200) as sql_stmt, current date
from table(snapshot_dyn_sql('TESTDB',-1))  as snapshot_dyn_sql
"

监控系统资源占用情况
db2 "select application_handle, substr(application_name,1,30) as appname, total_cpu_time
from table(mon_get_connection(null,null)) as t
order by total_cpu_time desc
"
后台实用程序
db2 list utilities

--获得数据库总大小信息
db2 "call get_dbsize_info(?,?,?,0)"


--检查数据库用户db2inst1的权限
db2 " select substr(authority,1,30) as authority , d_user, d_group, d_public,role_user, role_group,role_public,d_role
from table(sysproc.auth_list_authorities_for_authid('DB2INST1','U')) as t
order by authority
"

--查找无效对象
db2 "select char(tabschema,20), char(tabname,40),type,status from syscat.tables order by 1"
db2 "select viewname from syscat.views where valid='N'"
db2 "select trigname from syscat.triggers where VALID='N' "

---------------------------------------------------------------
--内存监控
--数据库和实例每一个内存池的内存大小
db2mtrk -i -p -v -d

--实例总共占用的内存
db2pd -dbptnmem
Cached：含在MemUsed内，已经分配的内存，当前没有使用到，
但这部分内存不能给其他进程使用，DB2可以分配给其他内存池。

----
db2top 每隔一段时间收集一次快照，然后通过计算其与最近一次快照之间的数值差别与经过的时间

--交互模式
db2top -d testdb

db2top -d testdb -f db2top_collect.txt -C -m 2 -i 15
其中-m参数指定运行多少分钟，-i指定每隔多少秒收集一次快照

--播放监控文件
db2top -d testdb -f db2top_collect.txt -b l -A

--直接跳转到某个时间点重新播放监控
db2top -d testdb -f db2top_collect.txt /02:00:00


--IO监控 硬盘使用 5秒钟监控一次，监控10次
     --linux
          iostat -d 5 10 
     --aix
          iostat -D 5 10 
--CPU和虚拟内存监控（物理内存和交换空间），5秒钟监控一次，监控10次
vmstat  5  10

ipcs  -a 进程间通信的信息
db2grep -dump 查看DB2安装版本

http://www.ibm.com/developerworks/data/library/techarticle/hayes/0102_hayes.html