Linux环境下数据库巡检,db2数据库日常巡检

查看db2 bufferpool

通过db2 "select * from syscat.bufferpools "查看bufferpool

数据库健康检查及优化的信息

Db2 get health snapshot for all on

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 $feas2

5、表空间状态是否正常

db2pd -d $feas2-tab

6、以下是数据库sql语句方面的检查

----检查表空间使用率----

db2 "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，因此不是很通用)

db2 "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('feas2', -1)) t"

---执行时间长的SQL

db2 "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

db2 "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：

db2 "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：

db2 "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"