# 获取数据库快照
GET SNAPSHOT FOR ALL DATABASES;

# 获取应用程序快照
GET SNAPSHOT FOR ALL APPLICATIONS;

# 获取锁快照
GET SNAPSHOT FOR LOCKS ON fgedb;

# 获取缓冲池快照
GET SNAPSHOT FOR BUFFERPOOLS ON fgedb;

# 获取表空间快照
GET SNAPSHOT FOR TABLESPACES ON fgedb;

# 获取动态SQL快照
GET SNAPSHOT FOR DYNAMIC SQL ON fgedb;

# 获取事务快照
GET SNAPSHOT FOR TRANSACTIONS ON fgedb;

# 数据库快照
SELECT * FROM TABLE(SYSPROC.ADMIN_GET_DB_INFO(‘FGEDB’)) AS T;

# 缓冲池信息
SELECT * FROM TABLE(SYSPROC.ADMIN_GET_BP_INFO(‘FGEDB’, -2)) AS T;

# 表空间信息
SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TBSP_INFO(‘FGEDB’, -2)) AS T;

# 应用程序信息
SELECT * FROM TABLE(SYSPROC.ADMIN_GET_APPL_INFO(‘FGEDB’, -1)) AS T;

# 锁信息
SELECT * FROM TABLE(SYSPROC.MON_GET_LOCKS(”, -2)) AS T;

# 等待事件
SELECT * FROM TABLE(SYSPROC.MON_GET_WAIT_EVENTS(NULL, NULL, -2)) AS T;

# 缓冲池命中率
SELECT BP_NAME,
TOTAL_LOGICAL_READS,
TOTAL_PHYSICAL_READS,
(TOTAL_LOGICAL_READS – TOTAL_PHYSICAL_READS) * 100.0 / NULLIF(TOTAL_LOGICAL_READS, 0) AS HIT_RATIO_PERCENT
FROM SYSIBMADM.SNAPBP;

# 表空间使用
SELECT TBSPACE,
TBSP_TOTAL_SIZE_KB,
TBSP_USED_SIZE_KB,
TBSP_FREE_SIZE_KB,
TBSP_UTILIZATION_PERCENT
FROM SYSIBMADM.SNAPTABSP;

# 最慢的SQL语句
SELECT STMT_TEXT,
NUM_EXECUTIONS,
TOTAL_EXEC_TIME / 1000 AS TOTAL_EXEC_TIME_SEC,
AVG_EXEC_TIME / 1000 AS AVG_EXEC_TIME_SEC
FROM SYSIBMADM.SNAPDYN_SQL
ORDER BY TOTAL_EXEC_TIME DESC
FETCH FIRST 20 ROWS ONLY;

# 创建死锁事件监控器
CREATE EVENT MONITOR deadlock_mon FOR DEADLOCKS
WRITE TO TABLE;

# 创建连接事件监控器
CREATE EVENT MONITOR conn_mon FOR CONNECTIONS
WRITE TO UNFORMATTED EVENT TABLE (CONN_EVENTS);

# 查看事件监控器
SELECT EVMONNAME, EVENT_TYPE FROM SYSCAT.EVENTMONITORS;

# 停用事件监控器
SET EVENT MONITOR stmt_mon STATE 0;

# 查看事件监控器状态
SELECT EVMONNAME, EVMON_STATE FROM SYSCAT.EVENTMONITORS;

# 查询表事件监控器
SELECT * FROM DEADLOCK_EVENT ORDER BY EVENT_TIMESTAMP DESC;

# 查询连接事件
SELECT * FROM CONN_EVENTS ORDER BY EVENT_TIME DESC;

# db2top命令
# s – SQL语句
# t – 表空间
# b – 缓冲池
# l – 锁
# a – 应用程序
# d – 数据库
# m – 内存

# 生成db2top报告
db2top -d fgedb -f db2top_report.txt -C 10 -n 60;

# 查看数据库信息
db2pd -db fgedb

# 查看应用程序
db2pd -db fgedb -applications

# 查看事务
db2pd -db fgedb -transactions

# 查看锁
db2pd -db fgedb -locks

# 查看缓冲池
db2pd -db fgedb -bufferpools

# 查看表空间
db2pd -db fgedb -tablespaces

# 查看动态SQL
db2pd -db fgedb -dynsql

# 查看内存使用
db2pd -db fgedb -memory

# 持续监控
db2pd -db fgedb -repeat 5 10

# 跟踪实例内存
db2mtrk -i

# 跟踪数据库内存
db2mtrk -d

# 跟踪应用程序内存
db2mtrk -a

# 详细输出
db2mtrk -i -d -a -v

DB_NAME=”fgedb”
LOG_DIR=”/db2/monitor/logs”
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $LOG_DIR

# 获取快照
db2 “GET SNAPSHOT FOR DATABASE ON $DB_NAME” > $LOG_DIR/snapshot_$DATE.log

# 获取缓冲池信息
db2 “SELECT * FROM TABLE(SYSPROC.ADMIN_GET_BP_INFO(‘$DB_NAME’, -2))” > $LOG_DIR/bp_$DATE.log

# 获取表空间信息
db2 “SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TBSP_INFO(‘$DB_NAME’, -2))” > $LOG_DIR/tbsp_$DATE.log

# 获取慢SQL
db2 “SELECT STMT_TEXT, NUM_EXECUTIONS, TOTAL_EXEC_TIME
FROM SYSIBMADM.SNAPDYN_SQL
ORDER BY TOTAL_EXEC_TIME DESC
FETCH FIRST 10 ROWS ONLY” > $LOG_DIR/slow_sql_$DATE.log

echo “Monitoring completed at $DATE”