对 DB2 监控数据的 SQL 访问: 捕获快照

Dirk deRoos, DB2 信息开发部, IBM 多伦多实验室
简介： DB2 UDB V8.1 的特点是能够使用 SQL 表函数捕获数据库系统监控数据的快照。本文概述了这样的能力并详述了如何实现这种监控技术。
标记本文！
发布日期： 2003 年 5 月 01 日
级别： 初级
访问情况 173 次浏览
建议: 0 (添加评论)
平均分 （共 0 个评分 ）
监控 IBM? DB2? Universal Database?（UDB）实例的状态的一种方法是通过捕获数据库系统监视器数据的 快照。监控数据的快照描述了在拍摄快照这个特定时间点上数据库的活动状态。在评估数据库系统性能以及检测潜在问题方面，这种数据库系统监控方法特别有用。
DB2 UDB V8.1 for UNIX?、Linux 和 Windows? 的特点是能够使用 SQL 表函数捕获快照。这代表了一种明显的改进，从而可以轻松地捕获并存储快照，以便快速且灵活地进行检索。
回页首
可用的快照监视器 SQL 表函数（称为 快照表函数）有很多，其中的每一个表函数都捕获一种特定类型的监视器数据。表 1列出了更常用的快照表函数。
注：有关各个快照表函数的详细信息（例如返回的列及其数据类型），请参阅与该快照表函数名称相链接的 DB2 文档主题。另外，有关完整的列表，请参阅 DB2 文档主题 快照监视器 SQL 表函数。
快照表函数 返回的信息
SNAPSHOT_DBM 数据库管理器信息。
SNAPSHOT_DATABASE 数据库信息。只有当至少有一个应用程序连接至数据库时，才会返回信息。
SNAPSHOT_APPL 连接至分区上数据库的应用程序上有关锁等待的应用程序信息。这包括累积计数器、状态信息和最近执行的 SQL 语句（如果设置了语句监视器开关）。
SNAPSHOT_APPL_INFO 每个连接至分区上数据库的应用程序的常规应用程序标识信息。
SNAPSHOT_LOCKWAIT 有关锁等待连接至分区上数据库的应用程序的应用程序信息。
SNAPSHOT_STATEMENT 有关连接至分区上数据库的应用程序的语句的应用程序信息。这包括最近执行的 SQL 语句（如果设置了语句监视器开关）。
SNAPSHOT_TABLE 连接至数据库的应用程序所访问的每个表的表活动信息。需要表监视器开关。
SNAPSHOT_LOCK 数据库级别上的锁信息，以及每个连接至数据库的应用程序在应用程序级别上的锁信息。需要锁监视器开关。
SNAPSHOT_TBS 数据库级别上的表空间活动信息、每个连接至数据库的应用程序在应用程序级别上的表空间活动信息以及连接至数据库的应用程序已访问过的每个表空间在表空间级别上的表空间活动信息。需要缓冲池监视器开关。
SNAPSHOT_BP 指定数据库的缓冲池活动计数器。需要缓冲池监视器开关。
SNAPSHOT_DYN_SQL 来自用于数据库的 SQL 语句高速缓存的某个时间点语句信息。
回页首
所有的快照表函数都返回一张监视器数据表，其中的每一行代表一个正被监控的数据库对象实例，而每一列代表一个监视器元素。监视器元素代表数据库系统状态的特定属性。
下面的 DB2 文档主题中列出了可用于快照监控的所有监视器元素： 快照监视器逻辑数据组和监视器元素。快照表函数的列名称直接对应于该主题中列出的监视器元素名称。
回页首
快照表函数能够捕获的许多监视器元素都受控于监视器开关。如果快照表函数表中的某些函数描述中提到特定监视器开关，则表明受控于该开关。DB2 包含监视器开关以向 DBA 提供禁止收集监视器元素集的选项，从而优化数据库性能。还有大量监视器元素不受开关的控制。
表 2列出了所有的监视器开关及其数据库管理器级别和数据库级别标识。
数据库管理器开关 数据库开关 提供的信息
BUFFERPOOL DFT_MON_BUFFERPOOL 读写的次数，花费的时间
LOCK DFT_MON_LOCK 锁等待次数，死锁
SORT DFT_MON_SORT 使用的堆数量，排序性能
STATEMENT DFT_MON_STMT 启动／停止时间，语句标识
TABLE DFT_MON_TABLE 活动度量（读取／写入行）
UOW DFT_MON_UOW 启动／终止次数，完成状态
TIMESTAMP DFT_MON_TIMESTAMP 时间戳记
在数据库管理器级别，监视器开关设置在 dft_monswitches数据库管理器配置参数中。要查看所有的监视器开关设置的设置选项，请使用 GET DATABASE MANAGER MONITOR SWITCHES 命令。要启用或禁用数据库管理器级别的监视器开关设置，请使用 UPDATE DBM CFG 命令，并指定要更改的个别监视器开关。例如，以下命令关闭 DFT_MON_TIMESTAMP 监视器开关，来终止时间戳记监视器数据的收集：
db2 update dbm cfg using DFT_MON_TIMESTAMP off
每个连接至数据库的应用程序都有其自己的监视器开关集，这些监视器开关与数据库管理器和其它应用程序无关。应用程序在连接至数据库时，从数据库管理器上继承它们的监视器开关设置。要查看应用程序的所有监视器开关设置的设置选项，请使用 GET MONITOR SWITCHES 命令。您可以使用 UPDATE MONITOR SWITCHES 命令来更改应用程序的监视器开关设置。例如，下面的命令打开 LOCK 监视器开关，从而启用 SNAPSHOT_LOCK 快照表函数所使用的监视器元素的收集：
db2 update monitor switches using LOCK on
回页首
使用快照表函数访问监视器数据的方法有两种： 直接访问和 文件访问。
对快照数据进行直接访问时，已授权用户用快照表函数发出查询，并接收包含监视器数据的结果集。已授权用户（要执行快照监控任务）意味着必须拥有 SYSADM、SYSCTRL 或 SYSMAINT 权限。
通过文件访问，已授权用户可以使所有用户都可以使用特定的快照数据集合。要这样做，已授权用户调用 SNAPSHOT_FILEW 存储过程，来确定快照请求类型和受影响的分区和数据库。SNAPSHOT_FILEW 存储过程将监视器数据保存到数据库服务器上的文件中。随后任何数据库用户都可以使用对应的快照表函数（使用指出文件访问的参数）来发出查询。从 SNAPSHOT_FILEW 存储过程生成的文件中抽取出他们接收的监视器数据。因此，用户将只接收快照表函数的结果，已授权用户已对这些快照表函数使用了 SNAPSHOT_FILEW 存储过程。
要使用快照表函数捕获直接访问的快照，请完成以下步骤：
连接至数据库。它可以是您需要监控的实例中的任何数据库。要能够使用快照表函数发出 SQL 查询，您必须连接至数据库。
例如：
db2 connect to sample
注：本节中显示的示例都假定您已创建了 sample 数据库。如果没有创建，那么可以通过运行 db2sampl 实用程序创建。
确定您需要捕获的快照类型，以及您需要监控的数据库和分区。除了收集这个信息之外，请打开任何可应用的监视器开关（通过检查表 1. 快照表函数中的快照表函数描述可以确定这一点）。
例如，如果您想要捕获表活动数据的快照（使用函数 SNAPSHOT_TABLE），那么将需要激活 TABLE 监视器开关：
db2 update dbm cfg using DFT_MON_TABLE on
使用期望的快照表函数发出查询。
例如，以下是一个查询，它捕获有关当前已连接分区的 SAMPLE 数据库的表活动信息的快照：
db2 "select * from table(SNAPSHOT_TABLE('sample',-1)) as T"
快照表函数有两个输入参数：
VARCHAR(255)，用于数据库名称。如果您输入 NULL，那么就使用当前已连接的数据库名称。
注 1：这个参数不能应用于只返回数据库管理器信息的快照表函数（例如，SNAPSHOT_DBM）。这样的快照表函数只有一个分区号参数。
注 2：对于以下的快照表函数列表，如果您输入 NULL 表示使用当前已连接的数据库，那么您将得到实例中所有数据库的快照信息：SNAPSHOT_DATABASE、SNAPSHOT_APPL、SNAPSHOT_APPL_INFO、SNAPSHOT_LOCKWAIT、SNAPSHOT_STATEMENT 和 SNAPSHOT_BP。
SMALLINT，用于分区号。对于分区号参数，输入一个整数（0 和 999 之间的值）以对应您需要监控的分区号。要捕获当前已连接分区的快照，请输入值 -1 或 NULL。要捕获全局快照，请输入值 -2。
以下示例将演示前面给出的各个步骤。在这个方案中，已有三个应用程序连接至 Sample 数据库。两个是本地连接，另一个连接自远程客户机。一个本地应用程序已经对 Sample 数据库的 STAFF 表中的记录作了一些更新。同时，远程应用程序已经在 Sample 数据库的 SALES 表中插入了一条记录。第二个本地应用程序用于执行快照监控活动。
以下是这三个应用程序执行的命令和语句的顺序：
监控应用程序：
设置 DFT_MON_TABLE 监视器开关。
db2 update dbm cfg using DFT_MON_TABLE on
应用程序 1（远程应用程序）：
向 SALES 表插入一条记录。
db2 "insert into sales values ('03/20/2003','LEE','Atlantic',5)"
应用程序 2（本地应用程序）：
对 STAFF 表更新 12 条记录。
db2 "update staff set salary = salary * 1.1 where JOB = 'Clerk'"
监控应用程序：
捕获数据库 Sample 中表信息的快照。
db2 connect to sample db2 "select * from table(SNAPSHOT_TABLE('sample',-1)) as T"
上面查询的结果集中包含许多列，因此从命令行读取会很困难。如果您只对几个特定监视器元素感兴趣，那么可以将 select 语句限制在相关的监视器元素列。例如，以下是这样一个查询及其对应的结果集：
监控应用程序：
db2 "select snapshot_timestamp, table_name, rows_written, rows_read from table(SNAPSHOT_TABLE('sample',-1)) as T"
SNAPSHOT_TIMESTAMP TABLE_NAME ROWS_WRITTEN ROWS_READ -------------------------- ----------------- ------------ ------------ 2003-04-07-09.33.27.468598 SYSROUTINES 0 4 2003-04-07-09.33.27.468598 STAFF 12 47 2003-04-07-09.33.27.468598 SALES 1 0 2003-04-07-09.33.27.468598 SYSTABLES 0 2 2003-04-07-09.33.27.468598 SYSPLAN 0 1 2003-04-07-09.33.27.468598 SYSEVENTMONITORS 0 1 2003-04-07-09.33.27.468598 SYSDBAUTH 0 5 2003-04-07-09.33.27.468598 SYSBUFFERPOOLS 0 1 2003-04-07-09.33.27.468598 SYSTABLESPACES 0 3 2003-04-07-09.33.27.468598 SYSVERSIONS 0 1 10 record(s) selected.
存储定期捕获的监视器快照结果可以提供大量有用信息，从而确定 DBMS 的状态和行为趋势。这样做的一个简单方式是对您正监控的实例数据库中的监视器数据创建一个（或多个）表。例如，下面这个正在创建的表将要存储有关连接至实例中数据库的应用程序数的监视器数据。
监控应用程序：
db2 "create table instance_snap (snap_time timestamp NOT NULL, local_cons bigint, rem_cons_in bigint)"
以下语句捕获实例中各个数据库的连接数的快照，以及时间戳记，并将这个数据插入到上面创建的 INSTANCE_SNAP 表中。
监控应用程序：
db2 "insert into instance_snap select snapshot_timestamp, local_cons, rem_cons_in from table (snapshot_dbm(-1)) as snapshot_dbm" db2 "select * from instance_snap" SNAP_TIME LOCAL_CONS REM_CONS_IN -------------------------- -------------------- -------------------- 2003-04-07-09.40.49.867659 2 1 1 record(s) selected.
上面的输出指出有两个本地应用程序和一个远程应用程序连接到了数据库 Sample 上。
通过文件访问使用快照表函数提供了一种使所有用户都可以访问快照监视器数据的安全方式。这种方法有一些局限性：
从 SNAPSHOT_FILEW 文件可获得的快照监视器数据只是已授权用户最近一次调用 SNAPSHOT_FILEW 存储过程的数据。通过定期调用 SNAPSHOT_FILEW 存储过程，您可以确保获得最新的快照监视器数据。例如，可以创建包含调用 SNAPSHOT_FILEW 存储过程的 DB2 CLP 脚本。通过使用 DB2 的任务中心（Task Center）工具，可以调度该脚本，例如让它在每个星期日的午夜运行。
使用快照表函数发出查询的用户不能标识要监控的数据库或分区。发出 SNAPSHOT_FILEW 调用的已授权用户标识的数据库名称和分区号确定了快照表函数可以访问的文件内容。
在提供对快照监视器数据的开放访问时，对快照表函数拥有执行特权（这个特权在缺省情况下指派给 PUBLIC）的所有用户都可以获得诸如已连接用户列表及其已经提交给数据库的 SQL 语句之类的敏感信息。使用快照监视器表函数不会暴露数据库上的任何实际数据或用户密码。
在调用 SNAPSHOT_FILEW 存储过程时，除了确定要监控的数据库和分区以外，您需要指定 快照请求类型号。其中的每个类型号代表一个或多个快照表函数可以访问的快照监视器信息集。表 3列出了快照表函数及其对应的快照请求类型号。
快照表函数 作用域（所有数据库或特定数据库） 快照请求类型号
SNAPSHOT_DBM - 1
SNAPSHOT_DATABASE 所有 9
SNAPSHOT_DATABASE 特定 2
SNAPSHOT_APPL 所有 10
SNAPSHOT_APPL 特定 6
SNAPSHOT_APPL_INFO 所有 10
SNAPSHOT_APPL_INFO 特定 6
SNAPSHOT_LOCKWAIT 所有 10
SNAPSHOT_LOCKWAIT 特定 6
SNAPSHOT_STATEMENT 所有 10
SNAPSHOT_STATEMENT 特定 6
SNAPSHOT_TABLE 特定 5
SNAPSHOT_LOCK 特定 8
SNAPSHOT_TBS 特定 13
SNAPSHOT_BP 所有 23
SNAPSHOT_BP 特定 22
SNAPSHOT_DYN_SQL 特定 36
要使用 SNAPSHOT_FILEW 存储过程捕获快照并存至文件，请完成以下步骤：
连接至数据库。它可以是您需要监控的实例中的任何数据库。要能使用快照表函数发出 SQL 查询，您必须连接至数据库。
例如：
db2 connect to sample
注：本节中显示的示例都假定您已创建了 sample 数据库。如果没有创建，那么可以通过运行 db2sampl 实用程序创建。
确定数据库用户需要捕获的快照类型，以及他们需要监控的数据库和分区。除了收集这个信息之外，请打开任何可应用的监视器开关（通过检查表 1. 快照表函数中的快照表函数描述可以确定这一点）。
使用快照请求类型、数据库和分区的适当参数设置来 调用 SNAPSHOT_FILEW 存储过程。
例如，以下是一个查询，它将捕获有关当前已连接分区上的 SAMPLE 数据库的应用程序信息的快照：
db2 "call SNAPSHOT_FILEW(6,'sample',-1)"
SNAPSHOT_FILEW 存储过程有三个输入参数：
SMALLINT，用于快照请求类型（请参阅表 3. 快照请求类型，它提供了快照表函数及其对应快照请求类型号的交叉引用）。
VARCHAR(128)，用于数据库名称。如果您输入 NULL，那么就使用当前已连接的数据库名称。
注：这个参数不能应用于只返回数据库管理器信息的快照表函数（例如，SNAPSHOT_DBM）。这些快照表函数只有快照请求类型号参数和分区号参数。
SMALLINT，用于分区号。对于分区号参数，请输入一个整数（0 和 999 之间的值）以对应您需要监控的分区号。要捕获当前已连接分区的快照，请输入值 -1 或 NULL。要捕获全局快照，请输入值 -2。
要使用快照表函数从 SNAPSHOT_FILEW 文件访问快照数据，请完成以下步骤：
连接至数据库。它可以是您需要监控的实例中的任何数据库。要能使用快照表函数发出 SQL 查询，您必须连接至数据库。
例如：
db2 connect to sample
确定需要捕获的快照类型。对于您访问 SNAPSHOT_FILEW 文件所要使用的每个快照表函数，已授权用户必定已经用对应的快照请求类型号发出了 SNAPSHOT_FILEW 存储过程调用。同样，在访问由 SNAPSHOT_FILEW 生成的文件时，您不能标识数据库名称和分区号，因为这些是由发出 SNAPSHOT_FILEW 调用的用户所指定的。
使用期望的快照表函数发出查询。
以下是一个查询示例，它捕获有关连接至当前已连接的分区上 SAMPLE 数据库的应用程序的信息快照：
db2 "select * from table(SNAPSHOT_APPL_INFO((CAST(NULL AS VARCHAR(1)), CAST (NULL AS INTEGER))) AS as A"
注：您必须对数据库名称参数和分区号参数输入 NULL 值，因为它们是在调用 SNAPSHOT_FILEW 存储过程时确定的。同样，数据库名称参数也不可应用于数据库管理器级别的快照表函数，因为这些函数只有分区号参数。
回页首
有了快照表函数，您可以使用 SQL 轻松地捕获数据库系统监视器数据的快照。将所选监视器数据集存储到 SQL 表中的这种能力允许存在许多监控应用程序。以下列表给出了一些示例：
使没有 SYSADM、SYSMAINT 或 SYSCTRL 特权的用户能获得所选的数据库系统快照监视器数据集。
定期捕获并存储数据库系统快照。
对特定时间段内数据库系统使用情况和性能作统计分析。
只要使用 SQL 和 DB2 任务中心就可以轻松实现上述每一项。您自己可以试一下，以增长数据库方面的知识，并提高您管理和调优的能力以获得最佳性能。