DB2 快照表Snapshot

snapshot用于监视数据库对象活动快照信息。在命令中可以指明要扑捉快照的级别和数据库对象

一.Snapshot Monitor SQL Table Function
-- 查看DBM的监视器快照
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
--查看数据库的监视器快照(rows_read，total_log_used)
SELECT TOTAL_LOG_USED,TOTAL_LOG_AVAILABLE,ROWS_READ FROM TABLE( SNAPSHOT_DATABASE( 'ADB', -1 )) as SNAPSHOT_DATABASE ---TOTAL_LOG_USED,TOTAL_LOG_AVAILABLE,ROWS_READ
--查看数据库应用程序的监视器快照
SELECT * FROM TABLE( SNAPSHOT_APPL( 'SAMPLE', -1 )) as SNAPSHOT_APPL --ROWS_READ,ROWS_SELECTED
--查看数据库应用程序详细信息的监视器快照
SELECT * FROM TABLE( SNAPSHOT_APPL_INFO( 'SAMPLE', -1 )) as SNAPSHOT_APPL_INFO
--查询锁等待信息
SELECT * FROM TABLE( SNAPSHOT_LOCKWAIT('SAMPLE', -1 )) as SNAPSHOT_LOCKWAIT
--查询数据库执行的SQL语句
SELECT * FROM TABLE( SNAPSHOT_STATEMENT( 'SAMPLE', -1 )) as SNAPSHOT_STATEMENT
--查询数据库代理程序信息
SELECT * FROM TABLE( SNAPSHOT_AGENT( 'SAMPLE', -1 )) as SNAPSHOT_AGENT
--查询数据库子查询的快照
SELECT * FROM TABLE( SNAPSHOT_SUBSECT( 'SAMPLE', -1 )) as SNAPSHOT_SUBSECT
--查询缓冲池的监控快照
SELECT * FROM TABLE( SNAPSHOT_BP( 'SAMPLE', -1 )) as SNAPSHOT_BP
--查询表空间的监控快照
SELECT * FROM TABLE( SNAPSHOT_TBS( 'SAMPLE', -1 )) as SNAPSHOT_TBS
--查询表空间的配置信息快照
SELECT * FROM TABLE( SNAPSHOT_TBS_CFG( 'SAMPLE', -1 )) as SNAPSHOT_TBS_CFG
--查询表空间的使用情况
SELECT tablespace_id,tablespace_name,tablespace_type,sum(total_pages) as total_pages ,sum(usable_pages) as usable_pages,sum(used_pages) as used_pages FROM TABLE( SNAPSHOT_TBS_CFG( 'SAMPLE', -1 )) as SNAPSHOT_TBS_CFG GROUP BY tablespace_id,tablespace_name,tablespace_type
--表空间的使用情况视图
select tbsp_name,tbsp_total_size_kb,tbsp_usable_size_kb,tbsp_used_size_kb,tbsp_free_size_kb, tbsp_utilization_percent from sysibmadm.TBSP_UTILIZATION
--To capture a snapshot of table space quiescer information:
SELECT * FROM TABLE( SNAPSHOT_QUIESCER( 'SAMPLE', -1 )) as SNAPSHOT_QUIESCER
--查询表空间的监控器容器信息
SELECT * FROM TABLE( SNAPSHOT_CONTAINER( 'SAMPLE', -1 )) as SNAPSHOT_CONTAINER
--查看表空间的范围
SELECT * FROM TABLE( SNAPSHOT_RANGES( 'SAMPLE', -1 )) as SNAPSHOT_RANGES
--Table: To capture a snapshot of table information:
--查看数据库的锁信息
SELECT * FROM TABLE( SNAPSHOT_APPL_INFO( 'SAMPLE', -1 )) as SNAPSHOT_APPL_INFO

二.The Snapshot Monitor
db2 "get snapshot for dynamic sql on sample" > sql_info.txt
--Dynamic SQL cache: To capture a snapshot of dynamic SQL statement cache information:Snapshot monitor 24 System Monitor Guide and Reference
Snapshot on database manager (instance)
GET SNAPSHOT FOR DBM
Snapshot on database
GET SNAPSHOT FOR DB ON SAMPLE
查询某一db2 进程的快照信息
db2 get snapshot for application agentid 55531
查询某一db2进程的锁信息
db2 get snapshot for locks for application agentid 48551
Snapshot for bufferpools
GET SNAPSHOT FOR bufferpools ON SAMPLE
Snapshot for tables
GET SNAPSHOT FOR tables ON SAMPLE
Snapshot for locks
GET SNAPSHOT FOR locks ON SAMPLE
Snapshot for dynamic sql
GET SNAPSHOT FOR dynamic sql ON SAMPLE
Snapshot for all
GET SNAPSHOT FOR all ON SAMPLE

三.The Snapshot VIEWS
--Connected database applications
SELECT * FROM SYSIBMADM.APPLICATIONS
--information about rate of rows selected versus rows read application
SELECT * FROM SYSIBMADM.APPL_PERFORMANCE
--buffer pool hit ratios,including total,data,and index
select * from SYSIBMADM.BP_HITRATIO
--buffer pool read performance information
select * from SYSIBMADM.BP_READ_IO
--contains buffer pool write performance information
select * from SYSIBMADM.BP_WRITE_IO
--information about table space containers and utilization rates
select * from SYSIBMADM.CONTAINER_UTILIZATION
--information on locks that are waiting to granted
select * from SYSIBMADM.LOCKWAITS
--The LOG_UTILIZATION administrative view returns information about log utilization for the currently connected database
db2 => select * from SYSIBMADM.LOG_UTILIZATION                                                                       
                                                                                                                     
DB_NAME   LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB    TOTAL_LOG_AVAILABLE_KB TOTAL_LOG_USED_TOP_KB DBPARTITIONNUM   
--------- ----------------------- -------------------- ---------------------- --------------------- --------------   
SB1DM                        0.21                 8619                3975755                  8757              0   
 
  1 record(s) selected.                                                                                                                  
TOTAL_LOG_AVAILABLE_KB=(LOGPRIMARY+LOGSECOND)*LOGFILSIZ*(Log file size)-TOTAL_LOG_USED_KB
for example:
 Log file size (4KB)                         (LOGFILSIZ) = 10000
 Number of primary log files                (LOGPRIMARY) = 25
 Number of secondary log files               (LOGSECOND) = 75

TOTAL_LOG_AVAILABLE_KB=(75+25)*10000*4=4000000-8619=3991381

--The LOCKWAITS administrative view returns information about DB2® agents working on behalf of applications that are waiting to obtain locks.
select * from SYSIBMADM.LOCKWAITS
--The LONG_RUNNING_SQL administrative view returns the longest running SQL statements in the currently connected database.
select * from SYSIBMADM.LONG_RUNNING_SQL
--The QUERY_PREP_COST administrative view returns a list of statements with information about the time required to prepare the statement.
select * from SYSIBMADM.QUERY_PREP_COST
--This administrative view allows you to retrieve agent logical data group application snapshot information for the currently connected database
select * from SYSIBMADM.SNAPAGENT
--retrieve the memory_pool logical data group snapshot information about memory usage at the agent level for the currently connected database.
select * from SYSIBMADM.SNAPAGENT_MEMORY_POOL
--retrieve appl_info logical data group snapshot information for the currently connected database
select * from SYSIBMADM.SNAPAPPL_INFO
--retrieve bufferpool logical group snapshot information for the currently connected database
select * from SYSIBMADM.SNAPBP
select * from SYSIBMADM.SNAPBP_PART
--retrieve tablespace_container logical data group snapshot information for the currently connected database.
select * from SYSIBMADM.SNAPCONTAINER
--retrieve snapshot information from the dbase logical group for the currently connected database
select * from SYSIBMADM.SNAPDB
--retrieve database level memory usage information for the currently connected database
select * from SYSIBMADM.SNAPDB_MEMORY_POOL
select * from SYSIBMADM.SNAPDBM
select * from SYSIBMADM.SNAPDBM_MEMORY_POOL
select * from SYSIBMADM.SNAPDETAILLOG
select * from SYSIBMADM.SNAPDYN_SQL
select * from SYSIBMADM.SNAPFCM
select * from SYSIBMADM.SNAPFCM_PART
select * from SYSIBMADM.SNAPHADR

select * from SYSIBMADM.SNAPLOCK
SELECT AGENT_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS FROM SYSIBMADM.SNAPLOCK

select * from SYSIBMADM.SNAPLOCKWAIT
select * from SYSIBMADM.SNAPSTMT
select * from SYSIBMADM.SNAPSTORAGE_PATHS
select * from SYSIBMADM.SNAPSUBSECTION
select * from SYSIBMADM.SNAPSWITCHES
select * from SYSIBMADM.SNAPTAB
select * from SYSIBMADM.SNAPTAB_REORG
select * from SYSIBMADM.SNAPTBSP
select * from SYSIBMADM.SNAPTBSP_PART
select * from SYSIBMADM.SNAPTBSP_QUIESCER
select * from SYSIBMADM.SNAPTBSP_RANGE
select * from SYSIBMADM.SNAPUTIL
select * from SYSIBMADM.SNAPUTIL_PROGRESS
--returns table space configuration and utilization information
select * from SYSIBMADM.TBSP_UTILIZATION
select tbsp_name,tbsp_total_size_kb,tbsp_usable_size_kb,tbsp_used_size_kb,tbsp_free_size_kb, tbsp_utilization_percent
from sysibmadm.TBSP_UTILIZATION
select * from SYSIBMADM.TOP_DYNAMIC_SQL