用SQL语句实现DB2主要指标的监控
--Database manager:To capture a snapshot of database manager information:
SELECT * FROM TABLE( SNAPSHOT_DBM(-1)) as SNAPSHOT_DBM
--To capture a snapshot of database manager information specifically regarding the fast communication manager (FCM):
SELECT * FROM TABLE( SNAPSHOT_FCM(-1)) as SNAPSHOT_FCM
--To capture a snapshot of database manager information for a partition specifically regarding the fast communication manager(FCM):
SELECT * FROM TABLE( SNAPSHOT_FCMPARTITION(-1)) as SNAPSHOT_FCMPARTITION
--To capture the database manager's monitor switch settings:
SELECT * FROM TABLE( SNAPSHOT_SWITCHES(-1)) as SNAPSHOT_SWITCHES
--Database: To capture a snapshot of database information:
SELECT * FROM TABLE( SNAPSHOT_DATABASE( 'SAMPLE', -1 )) as SNAPSHOT_DATABASE
--Application:To capture a snapshot of application information:
SELECT * FROM TABLE( SNAPSHOT_APPL( 'SAMPLE', -1 )) as SNAPSHOT_APPL
--To capture a snapshot of application identification information:Chapter 3. Using the Snapshot Monitor 23
SELECT * FROM TABLE( SNAPSHOT_APPL_INFO( 'SAMPLE', -1 )) as SNAPSHOT_APPL_INFO
--To capture a snapshot of lock wait information:
SELECT * FROM TABLE( SNAPSHOT_LOCKWAIT('SAMPLE', -1 )) as SNAPSHOT_LOCKWAIT
--To capture a snapshot of statement information:
SELECT * FROM TABLE( SNAPSHOT_STATEMENT( 'SAMPLE', -1 )) as SNAPSHOT_STATEMENT
--To capture a snapshot of agent information:
SELECT * FROM TABLE( SNAPSHOT_AGENT( 'SAMPLE', -1 )) as SNAPSHOT_AGENT
--To capture a snapshot of subsection information:
SELECT * FROM TABLE( SNAPSHOT_SUBSECT( 'SAMPLE', -1 )) as SNAPSHOT_SUBSECT
--Buffer pool: To capture a snapshot of buffer pool information:
SELECT * FROM TABLE( SNAPSHOT_BP( 'SAMPLE', -1 )) as SNAPSHOT_BP
--Table space:To capture a snapshot of table space information:
SELECT * FROM TABLE( SNAPSHOT_TBS( 'SAMPLE', -1 )) as SNAPSHOT_TBS
--To capture a snapshot of table space configuration information:
SELECT * FROM TABLE( SNAPSHOT_TBS_CFG( 'SAMPLE', -1 )) as SNAPSHOT_TBS_CFG
--To capture a snapshot of table space quiescer information:
SELECT * FROM TABLE( SNAPSHOT_QUIESCER( 'SAMPLE', -1 )) as SNAPSHOT_QUIESCER
--To capture a snapshot of table space container configuration information:
SELECT * FROM TABLE( SNAPSHOT_CONTAINER( 'SAMPLE', -1 )) as SNAPSHOT_CONTAINER
--To capture a snapshot of the ranges for a table space map:
SELECT * FROM TABLE( SNAPSHOT_RANGES( 'SAMPLE', -1 )) as SNAPSHOT_RANGES
--Table: To capture a snapshot of table information:
SELECT * FROM TABLE( SNAPSHOT_TABLE( 'SAMPLE', -1 )) as SNAPSHOT_TABLE
--Lock: To capture a snapshot of lock information:
SELECT * FROM TABLE( SNAPSHOT_LOCK( 'SAMPLE', -1 )) as SNAPSHOT_LOCK
--Dynamic SQL cache: To capture a snapshot of dynamic SQL statement cache information:Snapshot monitor 24 System Monitor Guide and Reference
SELECT * FROM TABLE( SNAPSHOT_DYN_SQL( 'SAMPLE', -1 )) as SNAPSHOT_DYN_SQL
ibmxp 回复于：2004-08-05 09:48:38
顶顶顶顶！！！
oprs 回复于：2004-08-06 12:32:57
3q
mymm 回复于：2004-08-06 15:25:31
鼓励一下，先加精啦！
再解释一下！
快照表函数 返回的信息
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 语句高速缓存的某个时间点语句信息。
huyuhui001 回复于：2004-08-06 17:23:44
谢谢版主
toms1981 回复于：2004-08-07 19:05:29
How come when I run this command "SELECT * FROM TABLE( SNAPSHOT_DYN_SQL( 'ITEPS', -1 )) as SNAPSHOT_DYN_SQL" I hit this error:
SQL0440N No function by the name "SNAPSHOT_DYN_SQL" having compatible
arguments was found in the function path. SQLSTATE=42884
mymm 回复于：2004-08-08 02:34:40
引用：原帖由 "toms1981" 发表：
How come when I run this command "SELECT * FROM TABLE( SNAPSHOT_DYN_SQL( 'ITEPS', -1 )) as SNAPSHOT_DYN_SQL" I hit this error:
SQL0440N No function by the name "SNAPSHOT_DYN_SQL" having compatible
..........
1、你的DB2版本是多少？
2、是否打开监视器开关？
huyuhui001 回复于：2004-08-09 08:34:13
db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
可以在当前会话环境里打开监控参数。
mymm 回复于：2004-08-09 08:43:04
而且是在8.1里的功能，7.X看不了！
toms1981 回复于：2004-08-09 09:55:22
No wonder I can't run it.... I'm using DB2 7.1
kuake 回复于：2004-08-16 10:58:33
拜读此贴深受启发，强烈建议加精。
qdchenf 回复于：2004-08-16 20:09:44
我们的学习进度跟不上版本的进度呀!!!!!
handsun 回复于：2004-08-22 09:47:00
支持
handsun 回复于：2004-08-22 09:47:36
支持！
lklgdkp 回复于：2005-08-03 11:10:27
The general form of a snapshot function is:
SELECT * FROM TABLE(SNAPSHOT_FUNCTION(database, partition)) AS S;
The SNAPSHOT_FUNCTION is replaced with the appropriate snapshot command, and the DATABASE field contains the name of the database you want the snapshot to be taken against. The partition should be set to -1 to return information for the current partition and -2 for all partitions. If this field is not included or set to null, the command will only work against the current partition.
myuhan 回复于：2005-08-03 18:30:24
看看先
|
