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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30227852/viewspace-1622125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30227852/viewspace-1622125/