用SQL语句实现DB2主要指标的监控

huyuhui001

用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

顶顶顶顶！！！

oprs

3q

mymm

鼓励一下，先加精啦！

再解释一下！

1. 快照表函数                      返回的信息
   
2.   
   
3. SNAPSHOT_DBM                 数据库管理器信息。
   
4. SNAPSHOT_DATABASE         数据库信息。只有当至少有一个应用程序连接至数据库时，才会返回信息。
   
5. SNAPSHOT_APPL                 连接至分区上数据库的应用程序上有关锁等待的应用程序信息。这包括累积计数器、状态信息和最近执行的 SQL 语句（如果设置了语句监视器开关）。
   
6. SNAPSHOT_APPL_INFO         每个连接至分区上数据库的应用程序的常规应用程序标识信息。
   
7. SNAPSHOT_LOCKWAIT         有关锁等待连接至分区上数据库的应用程序的应用程序信息。
   
8. SNAPSHOT_STATEMENT         有关连接至分区上数据库的应用程序的语句的应用程序信息。这包括最近执行的 SQL 语句（如果设置了语句监视器开关）。
   
9. SNAPSHOT_TABLE                 连接至数据库的应用程序所访问的每个表的表活动信息。需要表监视器开关。
   
10. SNAPSHOT_LOCK                 数据库级别上的锁信息，以及每个连接至数据库的应用程序在应用程序级别上的锁信息。需要锁监视器开关。
    
11. SNAPSHOT_TBS                 数据库级别上的表空间活动信息、每个连接至数据库的应用程序在应用程序级别上的表空间活动信息以及连接至数据库的应用程序已访问过的每个表空间在表空间级别上的表空间活动信息。需要缓冲池监视器开关。
    
12. SNAPSHOT_BP                 指定数据库的缓冲池活动计数器。需要缓冲池监视器开关。
    
13. SNAPSHOT_DYN_SQL         来自用于数据库的 SQL 语句高速缓存的某个时间点语句信息。

复制代码

huyuhui001

谢谢版主

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
arguments was found in the function path.  SQLSTATE=42884

mymm

原帖由 "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

db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
可以在当前会话环境里打开监控参数。

mymm

而且是在8.1里的功能，7.X看不了！

toms1981

No wonder I can't run it.... I'm using DB2 7.1