db2巡檢

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