DB2的监控(监控开关)

DB2数据库可以在bufferpool,lock,table,statement,sort,uow,timestamp这七个类别上进行监控，默认只有timestamp开启，其它都是关闭的。

 

实例级别

这些监控类别默认开启在INSTANCE级别，对应DATABASE MANANGER的DFT_MON的七个参数：

 

db2 get dbm cfg|grep DFT_MON      
   Buffer pool (DFT_MON_BUFPOOL) = ON
   Lock (DFT_MON_LOCK) = OFF
   Sort (DFT_MON_SORT) = OFF
   Statement (DFT_MON_STMT) = OFF
   Table (DFT_MON_TABLE) = OFF
   Timestamp (DFT_MON_TIMESTAMP) = ON
   Unit of work (DFT_MON_UOW) = OFF

 

 

开关修改语句：

1. INSTANCE LEVEL：
   db2 update dbm cfg using DFT_MON_BUFPOOL on
2. SESSION LEVEL:
   db2 update monitor switches using bufferpool on table on ...

 

全部打开:

db2 update dbm cfg using DFT_MON_BUFPOOL on DFT_MON_LOCK on DFT_MON_SORT on DFT_MON_STMT on DFT_MON_TABLE on DFT_MON_UOW on DFT_MON_TIMESTAMP on

 

对于这种实例级别的监控开启之后，如果应用已经连接，则需要 Reattach实例才会生效:

db2 attach to <inst_name>

 

会话级别

参数监控开关也可以在会话级别开启；当会话退出后，监控开关的改动失效。

 

db2inst@linux11:~$ db2 get dbm cfg|grep MON_BUFPOOL
   Buffer pool (DFT_MON_BUFPOOL) = OFF

db2inst@linux11:~$ db2 update dbm cfg using DFT_MON_BUFPOOL on
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.
db2inst@linux11:~$ db2 get dbm cfg|grep MON_BUFPOOL
   Buffer pool (DFT_MON_BUFPOOL) = ON

db2inst@linux11:~$ db2 get monitor switches

            Monitor Recording Switches

Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 02/20/2012 09:47:17.384609
Lock Information (LOCK) = ON 02/13/2012 08:47:27.618836
Sorting Information (SORT) = OFF
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 02/13/2012 08:47:27.618836
Unit of Work Information (UOW) = OFF

 

全部打开:

db2 update monitor switches using BUFFERPOOL on LOCK on SORT on STATEMENT on TABLE on TIMESTAMP on UOW on

 

另外，BUFFERPOOL的信息也可以通过DB2PD工具获取：

db2pd -d sample -bufferpools