db2日常巡檢（适用db2 v9.5)
1、日志中是否有錯誤：
db2diag -H 2d -level "Severe,Error" （顯示2天内severe和error級别的日志）
2、記憶體使用情況：
db2mtrk -i -d -v
3、連接配接情況檢查
db2 list application |wc -l
db2 list application show detail|grep -i Executing |wc -l
4、備份檢查
db2 list history backup all for $ DBNAME
5、 表空間狀态是否正常
db2pd -d $ dbname -tab
6、以下是資料庫sql語句方面的檢查
----檢查表空間使用率----
select char(TBSP_NAME,50) as TBSP_NAME, TBSP_USABLE_PAGES, TBSP_USED_PAGES, rtrim(100*TBSP_USED_PAGES/TBSP_USABLE_PAGES)||'%' as USED_RATE from sysibmadm.SNAPTBSP_PART where TBSP_USABLE_PAGES>0 order by TBSP_NAME,DBPARTITIONNUM
（這個關聯了資料庫sample，是以不是很通用）
select 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(' sample', -1)) t
---執行時間長的SQL
select AVERAGE_EXECUTION_TIME_S , SUBSTR(STMT_TEXT,1,1000) AS STMT_TEXT from SYSIBMADM.TOP_DYNAMIC_SQL order by AVERAGE_EXECUTION_TIME_S desc fetch first 20 rows only
---執行頻率高的SQL
select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION,SUBSTR(STMT_TEXT,1, 1000) AS STMT_TEXT from SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS desc fetch first 20 rows only
---排序最多SQL：
select STMT_SORTS, SORTS_PER_EXECUTION, substr(STMT_TEXT,1,1000) as STMT_TEXT from SYSIBMADM.TOP_DYNAMIC_SQL order by STMT_SORTS desc fetch first 20 rows only
---全表掃描最多SQL：
select appl_p.agent_id,COALESCE(percent_rows_selected,0) as percent_rows_selected,substr(stmt_text,1,500) as stmt_text from sysibmadm.appl_performance appl_p, sysibmadm.snapstmt stmt where stmt.agent_id = appl_p.agent_id and stmt_text is not null order by COALESCE(percent_rows_selected,0) fetch first 20 rows only
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30227852/viewspace-1622125/，如需轉載，請注明出處，否則将追究法律責任。
轉載于:http://blog.itpub.net/30227852/viewspace-1622125/