DB2--数据库监视应用--2012-12-19

1、在实例级别上设置监视器开关(影响所有用户和数据库)
DFT_MON_STMT：语句监视器（用于动态 SQL）
DFT_MON_TABLE：表监视器
DFT_MON_LOCK：锁监视器
DFT_MON_BUFPOOL：缓冲池监视器
DFT_MON_SORT：排序监视器
DFT_MON_UOW：工作单元信息
DFT_MON_TIMESTAMP：跟踪时间戳记信息 
 2、更改参数语法：

 db2 update dbm cfg using monitorSwitch [ON|OFF]

3、更改完成后监视看一下监视的结果：

E:Program FilesIBMSQLLIBBIN>db2 get monitor switches

            监视器记录开关

数据库分区号 0 的开关列表
缓冲池活动信息        (BUFFERPOOL) = ON  2012-12-19 10:13:30.820490
锁定信息                    (LOCK) = ON  2012-12-19 10:13:30.820490
排序信息                    (SORT) = ON  2012-12-19 10:13:30.820490
SQL 语句信息           (STATEMENT) = ON  2012-12-19 10:13:30.820490
表活动信息                 (TABLE) = ON  2012-12-19 10:13:30.820490
获取时间戳记信息（时间戳记）    = ON  2012-12-19 10:13:30.820490
工作单元信息                 (UOW) = ON  2012-12-19 10:13:30.820490

4、数据库快照：

E:Program FilesIBMSQLLIBBIN>db2 get snapshot for database on test

5、表快照：

E:Program FilesIBMSQLLIBBIN>db2 get snapshot for tables on test

            表快照

第一个数据库连接时间戳记        = 2012-12-19 14:02:51.530114
上次复位时间戳记                =
快照时间戳记                    = 2012-12-19 14:15:41.002536
数据库名称                      = TEST
数据库路径                      = E:DB2_01NODE0000SQL00001
输入数据库别名                  = TEST
已访问的表的数目                = 26

表列表
 表模式               = SYSIBM
 表名                 = SYSTABLES
 表类型               = 目录
 数据对象页   = 55
 索引对象页   = 33
 LOB 对象页   = 832
 读取的行             = 4381
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSCOLUMNS
 表类型               = 目录
 数据对象页   = 313
 索引对象页   = 157
 读取的行             = 40
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSTOOLS
 表名                 = POLICY
 表类型               = 用户
 数据对象页   = 1
 索引对象页   = 4
 LOB 对象页   = 64
 读取的行             = 7
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSTOOLS
 表名                 = HMON_ATM_INFO
 表类型               = 用户
 数据对象页   = 13
 索引对象页   = 6
 读取的行             = 2010
 写入的行             = 0
 溢出                 = 1200
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSPLAN
 表类型               = 目录
 数据对象页   = 47
 索引对象页   = 23
 LOB 对象页   = 448
 读取的行             = 332
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSDBAUTH
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 10
 读取的行             = 2
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSPLANAUTH
 表类型               = 目录
 数据对象页   = 9
 索引对象页   = 24
 读取的行             = 13
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSSURROGATEAUTHIDS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 8
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSVERSIONS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 4
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSROLEAUTH
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 10
 读取的行             = 2
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSROUTINEAUTH
 表类型               = 目录
 数据对象页   = 37
 索引对象页   = 142
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSCONTEXTS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 14
 LOB 对象页   = 1
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSCONTEXTATTRIBUTES
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 4
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSWORKLOADS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 10
 LOB 对象页   = 1
 读取的行             = 4
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSWORKLOADAUTH
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 10
 读取的行             = 2
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSSERVICECLASSES
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 6
 LOB 对象页   = 1
 读取的行             = 6
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSROUTINES
 表类型               = 目录
 数据对象页   = 132
 索引对象页   = 184
 LOB 对象页   = 1984
 读取的行             = 24
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSEVENTMONITORS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 6
 LOB 对象页   = 64
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSTABLESPACES
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 10
 读取的行             = 4
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSTRIGGERS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 8
 LOB 对象页   = 64
 读取的行             = 32
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSUSERAUTH
 表类型               = 目录
 数据对象页   = 9
 索引对象页   = 14
 LOB 对象页   = 1
 读取的行             = 53
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSNODEGROUPS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 6
 LOB 对象页   = 1
 读取的行             = 2
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSHISTOGRAMTEMPLATES
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 6
 LOB 对象页   = 1
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSXMLSTRINGS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 6
 读取的行             = 1
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSHISTOGRAMTEMPLATEBINS
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 4
 读取的行             = 40
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

 表模式               = SYSIBM
 表名                 = SYSHISTOGRAMTEMPLATEUSE
 表类型               = 目录
 数据对象页   = 1
 索引对象页   = 6
 读取的行             = 28
 写入的行             = 0
 溢出                 = 0
 重组的页         = 0

6、表空间快照：

E:Program FilesIBMSQLLIBBIN>db2 get snapshot for tablespaces on test

E:Program FilesIBMSQLLIBBIN>db2 get snapshot for bufferpools on test

             缓冲池快照

缓冲池名                                   = IBMDEFAULTBP
数据库名称                      = TEST
数据库路径                      = E:DB2_01NODE0000SQL00001
输入数据库别名                  = TEST
快照时间戳记                    = 2012-12-19 14:18:38.972400

缓冲池数据逻辑读取              = 4939
缓冲池数据物理读取              = 310
缓冲池临时数据逻辑读取                  = 0
缓冲池临时数据物理读取                  = 0
缓冲池数据写入                  = 0
缓冲池索引逻辑读取              = 3210
缓冲池索引物理读取              = 402
缓冲池临时索引逻辑读取                  = 0
缓冲池临时索引物理读取                  = 0
缓冲池 xda 逻辑读取                        = 0
缓冲池 xda 物理读取                        = 0
缓冲池临时 xda 逻辑读取                    = 0
缓冲池临时 xda 物理读取                    = 0
缓冲池 xda 写入                            = 0
缓冲池总计读取时间（毫秒）      = 1598
缓冲池总计写入时间（毫秒）      = 0
异步池数据页读取                = 90
异步池数据页写入                = 0
缓冲池索引写入                  = 0
异步池索引页读取                = 0
异步池索引页写入                = 0
异步池 xda 页读取                          = 0
异步池 xda 页写入                          = 0
异步读取总计耗用时间            = 328
异步写入总计耗用时间            = 0
异步数据读取请求                = 26
异步索引读取请求             = 0
异步 xda 读取请求                          = 0
无干扰缓冲区可用                       = 0
直接读取                        = 546
直接写入                        = 0
直接读取请求                    = 53
直接写入请求                    = 0
直接读取耗用时间（毫秒）        = 187
直接写入耗用时间（毫秒）        = 0
关闭的数据库文件数              = 0
未读预取页                                 = 0
向量 IO                                    = 26
来自向量 IO 的页                           = 90
块 IO                                      = 0
块 IO 的页                                 = 0

Node number                                = 0
正在使用缓冲池的表空间                     = 4
改变缓冲池信息：
 仍要除去的页数                            = 0
 当前大小                                  = 56598
 改变后的大小                              = 56598

             缓冲池快照

缓冲池名                                   = IBMSYSTEMBP4K
数据库名称                      = TEST
数据库路径                      = E:DB2_01NODE0000SQL00001
输入数据库别名                  = TEST
快照时间戳记                    = 2012-12-19 14:18:38.972400

缓冲池数据逻辑读取              = 0
缓冲池数据物理读取              = 0
缓冲池临时数据逻辑读取                  = 0
缓冲池临时数据物理读取                  = 0
缓冲池数据写入                  = 0
缓冲池索引逻辑读取              = 0
缓冲池索引物理读取              = 0
缓冲池临时索引逻辑读取                  = 0
缓冲池临时索引物理读取                  = 0
缓冲池 xda 逻辑读取                        = 0
缓冲池 xda 物理读取                        = 0
缓冲池临时 xda 逻辑读取                    = 0
缓冲池临时 xda 物理读取                    = 0
缓冲池 xda 写入                            = 0
缓冲池总计读取时间（毫秒）      = 0
缓冲池总计写入时间（毫秒）      = 0
异步池数据页读取                = 0
异步池数据页写入                = 0
缓冲池索引写入                  = 0
异步池索引页读取                = 0
异步池索引页写入                = 0
异步池 xda 页读取                          = 0
异步池 xda 页写入                          = 0
异步读取总计耗用时间            = 0
异步写入总计耗用时间            = 0
异步数据读取请求                = 0
异步索引读取请求             = 0
异步 xda 读取请求                          = 0
无干扰缓冲区可用                       = 0
直接读取                        = 0
直接写入                        = 0
直接读取请求                    = 0
直接写入请求                    = 0
直接读取耗用时间（毫秒）        = 0
直接写入耗用时间（毫秒）        = 0
关闭的数据库文件数              = 0
未读预取页                                 = 0
向量 IO                                    = 0
来自向量 IO 的页                           = 0
块 IO                                      = 0
块 IO 的页                                 = 0

Node number                                = 0
正在使用缓冲池的表空间                     = 0
改变缓冲池信息：
 仍要除去的页数                            = 0
 当前大小                                  = 16
 改变后的大小                              = 16

             缓冲池快照

缓冲池名                                   = IBMSYSTEMBP8K
数据库名称                      = TEST
数据库路径                      = E:DB2_01NODE0000SQL00001
输入数据库别名                  = TEST
快照时间戳记                    = 2012-12-19 14:18:38.972400

缓冲池数据逻辑读取              = 0
缓冲池数据物理读取              = 0
缓冲池临时数据逻辑读取                  = 0
缓冲池临时数据物理读取                  = 0
缓冲池数据写入                  = 0
缓冲池索引逻辑读取              = 0
缓冲池索引物理读取              = 0
缓冲池临时索引逻辑读取                  = 0
缓冲池临时索引物理读取                  = 0
缓冲池 xda 逻辑读取                        = 0
缓冲池 xda 物理读取                        = 0
缓冲池临时 xda 逻辑读取                    = 0
缓冲池临时 xda 物理读取                    = 0
缓冲池 xda 写入                            = 0
缓冲池总计读取时间（毫秒）      = 0
缓冲池总计写入时间（毫秒）      = 0
异步池数据页读取                = 0
异步池数据页写入                = 0
缓冲池索引写入                  = 0
异步池索引页读取                = 0
异步池索引页写入                = 0
异步池 xda 页读取                          = 0
异步池 xda 页写入                          = 0
异步读取总计耗用时间            = 0
异步写入总计耗用时间            = 0
异步数据读取请求                = 0
异步索引读取请求             = 0
异步 xda 读取请求                          = 0
无干扰缓冲区可用                       = 0
直接读取                        = 0
直接写入                        = 0
直接读取请求                    = 0
直接写入请求                    = 0
直接读取耗用时间（毫秒）        = 0
直接写入耗用时间（毫秒）        = 0
关闭的数据库文件数              = 0
未读预取页                                 = 0
向量 IO                                    = 0
来自向量 IO 的页                           = 0
块 IO                                      = 0
块 IO 的页                                 = 0

Node number                                = 0
正在使用缓冲池的表空间                     = 0
改变缓冲池信息：
 仍要除去的页数                            = 0
 当前大小                                  = 16
 改变后的大小                              = 16

             缓冲池快照

缓冲池名                                   = IBMSYSTEMBP16K
数据库名称                      = TEST
数据库路径                      = E:DB2_01NODE0000SQL00001
输入数据库别名                  = TEST
快照时间戳记                    = 2012-12-19 14:18:38.972400

缓冲池数据逻辑读取              = 0
缓冲池数据物理读取              = 0
缓冲池临时数据逻辑读取                  = 0
缓冲池临时数据物理读取                  = 0
缓冲池数据写入                  = 0
缓冲池索引逻辑读取              = 0
缓冲池索引物理读取              = 0
缓冲池临时索引逻辑读取                  = 0
缓冲池临时索引物理读取                  = 0
缓冲池 xda 逻辑读取                        = 0
缓冲池 xda 物理读取                        = 0
缓冲池临时 xda 逻辑读取                    = 0
缓冲池临时 xda 物理读取                    = 0
缓冲池 xda 写入                            = 0
缓冲池总计读取时间（毫秒）      = 0
缓冲池总计写入时间（毫秒）      = 0
异步池数据页读取                = 0
异步池数据页写入                = 0
缓冲池索引写入                  = 0
异步池索引页读取                = 0
异步池索引页写入                = 0
异步池 xda 页读取                          = 0
异步池 xda 页写入                          = 0
异步读取总计耗用时间            = 0
异步写入总计耗用时间            = 0
异步数据读取请求                = 0
异步索引读取请求             = 0
异步 xda 读取请求                          = 0
无干扰缓冲区可用                       = 0
直接读取                        = 0
直接写入                        = 0
直接读取请求                    = 0
直接写入请求                    = 0
直接读取耗用时间（毫秒）        = 0
直接写入耗用时间（毫秒）        = 0
关闭的数据库文件数              = 0
未读预取页                                 = 0
向量 IO                                    = 0
来自向量 IO 的页                           = 0
块 IO                                      = 0
块 IO 的页                                 = 0

Node number                                = 0
正在使用缓冲池的表空间                     = 0
改变缓冲池信息：
 仍要除去的页数                            = 0
 当前大小                                  = 16
 改变后的大小                              = 16

             缓冲池快照

缓冲池名                                   = IBMSYSTEMBP32K
数据库名称                      = TEST
数据库路径                      = E:DB2_01NODE0000SQL00001
输入数据库别名                  = TEST
快照时间戳记                    = 2012-12-19 14:18:38.972400

缓冲池数据逻辑读取              = 0
缓冲池数据物理读取              = 0
缓冲池临时数据逻辑读取                  = 0
缓冲池临时数据物理读取                  = 0
缓冲池数据写入                  = 0
缓冲池索引逻辑读取              = 0
缓冲池索引物理读取              = 0
缓冲池临时索引逻辑读取                  = 0
缓冲池临时索引物理读取                  = 0
缓冲池 xda 逻辑读取                        = 0
缓冲池 xda 物理读取                        = 0
缓冲池临时 xda 逻辑读取                    = 0
缓冲池临时 xda 物理读取                    = 0
缓冲池 xda 写入                            = 0
缓冲池总计读取时间（毫秒）      = 0
缓冲池总计写入时间（毫秒）      = 0
异步池数据页读取                = 0
异步池数据页写入                = 0
缓冲池索引写入                  = 0
异步池索引页读取                = 0
异步池索引页写入                = 0
异步池 xda 页读取                          = 0
异步池 xda 页写入                          = 0
异步读取总计耗用时间            = 0
异步写入总计耗用时间            = 0
异步数据读取请求                = 0
异步索引读取请求             = 0
异步 xda 读取请求                          = 0
无干扰缓冲区可用                       = 0
直接读取                        = 0
直接写入                        = 0
直接读取请求                    = 0
直接写入请求                    = 0
直接读取耗用时间（毫秒）        = 0
直接写入耗用时间（毫秒）        = 0
关闭的数据库文件数              = 0
未读预取页                                 = 0
向量 IO                                    = 0
来自向量 IO 的页                           = 0
块 IO                                      = 0
块 IO 的页                                 = 0

Node number                                = 0
正在使用缓冲池的表空间                     = 0
改变缓冲池信息：
 仍要除去的页数                            = 0
 当前大小                                  = 16
 改变后的大小                              = 16
7、锁快照：

E:Program FilesIBMSQLLIBBIN>db2 get snapshot for locks on test

            数据库锁定快照

数据库名称                      = TEST
数据库路径                      = E:DB2_01NODE0000SQL00001
输入数据库别名                  = TEST
挂起的锁定                      = 0
当前已连接的应用程序            = 1
当前正等待锁定的代理程序数      = 0
快照时间戳记                    = 2012-12-19 14:19:52.548340

应用程序句柄                    = 85
应用程序标识                    = *LOCAL.DB2.121219060255
序号                            = 00001
应用程序名                      = db2taskd
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = UOW 正在等待
状态更改时间                    = 2012-12-19 14:17:59.742363
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0

应用程序句柄                    = 84
应用程序标识                    = *LOCAL.DB2.121219060254
序号                            = 00001
应用程序名                      = db2stmm
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = UOW 正在等待
状态更改时间                    = 2012-12-19 14:17:59.729562
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0

应用程序句柄                    = 90
应用程序标识                    = *LOCAL.DB2.121219060300
序号                            = 00001
应用程序名                      = db2evmg_DB2DETAILDEA
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = 连接已完成
状态更改时间                    = 2012-12-19 14:02:54.435802
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0

应用程序句柄                    = 83
应用程序标识                    = *LOCAL.DB2_01.121219060251
序号                            = 00001
应用程序名                      = db2bp.exe
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = 连接已完成
状态更改时间                    = 2012-12-19 14:02:54.352520
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0

应用程序句柄                    = 89
应用程序标识                    = *LOCAL.DB2.121219060259
序号                            = 00001
应用程序名                      = db2fw1
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = 连接已完成
状态更改时间                    = 2012-12-19 14:02:54.260900
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0

应用程序句柄                    = 88
应用程序标识                    = *LOCAL.DB2.121219060258
序号                            = 00001
应用程序名                      = db2fw0
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = 连接已完成
状态更改时间                    = 2012-12-19 14:02:54.254707
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0

应用程序句柄                    = 87
应用程序标识                    = *LOCAL.DB2.121219060257
序号                            = 00001
应用程序名                      = db2lused
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = UOW 正在等待
状态更改时间                    = 2012-12-19 14:17:59.748673
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0

应用程序句柄                    = 86
应用程序标识                    = *LOCAL.DB2.121219060256
序号                            = 00001
应用程序名                      = db2wlmd
CONNECT 授权标识                = ADMINISTRATOR
应用程序状态                    = 连接已完成
状态更改时间                    = 2012-12-19 14:02:54.242898
应用程序代码页                  = 1386
挂起的锁定                      = 0
总计等待时间（毫秒）            = 0