DB2日常監控指令碼

--每天需要監控的內容

--監控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