# 创建性能基线表
CREATE TABLE PERFORMANCE_BASELINE (
BASELINE_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
METRIC_NAME VARCHAR(100) NOT NULL,
METRIC_VALUE DECIMAL(18, 2) NOT NULL,
METRIC_UNIT VARCHAR(50),
COLLECT_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
PERIOD_TYPE VARCHAR(20) NOT NULL,
CONSTRAINT PK_PERFORMANCE_BASELINE PRIMARY KEY (BASELINE_ID)
);

# 采集性能指标存储过程
CREATE OR REPLACE PROCEDURE SP_COLLECT_BASELINE()
LANGUAGE SQL
BEGIN
DECLARE v_metric_value DECIMAL(18, 2);

— 缓冲池命中率
SELECT AVG(DATA_HIT_RATIO_PERCENT) INTO v_metric_value
FROM SYSIBMADM.BP_HITRATIO;

INSERT INTO PERFORMANCE_BASELINE (METRIC_NAME, METRIC_VALUE, METRIC_UNIT, PERIOD_TYPE)
VALUES (‘BUFFERPOOL_HIT_RATIO’, v_metric_value, ‘%’, ‘HOURLY’);

— 排序溢出率
SELECT TOTAL_SORT_OVERFLOWS * 100.0 / NULLIF(TOTAL_SORTS, 0) INTO v_metric_value
FROM SYSIBMADM.SNAPDB;

INSERT INTO PERFORMANCE_BASELINE (METRIC_NAME, METRIC_VALUE, METRIC_UNIT, PERIOD_TYPE)
VALUES (‘SORT_OVERFLOW_RATIO’, v_metric_value, ‘%’, ‘HOURLY’);

— 平均响应时间
SELECT AVG(TOTAL_EXEC_TIME / NULLIF(NUM_EXECUTIONS, 0)) INTO v_metric_value
FROM SYSIBMADM.TOP_DYNAMIC_SQL;

INSERT INTO PERFORMANCE_BASELINE (METRIC_NAME, METRIC_VALUE, METRIC_UNIT, PERIOD_TYPE)
VALUES (‘AVG_RESPONSE_TIME’, v_metric_value, ‘MS’, ‘HOURLY’);

— 连接数
SELECT COUNT(*) INTO v_metric_value
FROM SYSIBMADM.APPLICATIONS;

INSERT INTO PERFORMANCE_BASELINE (METRIC_NAME, METRIC_VALUE, METRIC_UNIT, PERIOD_TYPE)
VALUES (‘CONNECTION_COUNT’, v_metric_value, ‘COUNT’, ‘HOURLY’);
END;

# 计算基线统计值
SELECT
METRIC_NAME,
AVG(METRIC_VALUE) AS AVG_VALUE,
MIN(METRIC_VALUE) AS MIN_VALUE,
MAX(METRIC_VALUE) AS MAX_VALUE,
STDDEV(METRIC_VALUE) AS STDDEV_VALUE
FROM PERFORMANCE_BASELINE
WHERE COLLECT_TIME >= CURRENT DATE – 30 DAYS
GROUP BY METRIC_NAME;

# 创建基线统计表
CREATE TABLE BASELINE_STATISTICS (
STAT_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
METRIC_NAME VARCHAR(100) NOT NULL,
AVG_VALUE DECIMAL(18, 2) NOT NULL,
MIN_VALUE DECIMAL(18, 2) NOT NULL,
MAX_VALUE DECIMAL(18, 2) NOT NULL,
STDDEV_VALUE DECIMAL(18, 2) NOT NULL,
CALCULATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_BASELINE_STATISTICS PRIMARY KEY (STAT_ID)
);

# 计算并保存基线统计
INSERT INTO BASELINE_STATISTICS (METRIC_NAME, AVG_VALUE, MIN_VALUE, MAX_VALUE, STDDEV_VALUE)
SELECT
METRIC_NAME,
AVG(METRIC_VALUE),
MIN(METRIC_VALUE),
MAX(METRIC_VALUE),
STDDEV(METRIC_VALUE)
FROM PERFORMANCE_BASELINE
WHERE COLLECT_TIME >= CURRENT DATE – 30 DAYS
GROUP BY METRIC_NAME;

# 性能监控脚本
#!/bin/bash
# performance_monitor.sh

DBNAME=FGEDB
LOG_FILE=/db2/logs/performance_$(date +%Y%m%d).log

while true; do
echo “=== $(date) ===” >> $LOG_FILE

# 1. 缓冲池命中率
db2 connect to $DBNAME
db2 “SELECT bpname, data_hit_ratio_percent FROM sysibmadm.bp_hitratio” >> $LOG_FILE

# 2. 慢查询
db2 “SELECT substr(stmt_text,1,100), total_exec_time
FROM sysibmadm.top_dynamic_sql
ORDER BY total_exec_time DESC FETCH FIRST 10 ROWS ONLY” >> $LOG_FILE

# 3. 锁等待
db2 “SELECT COUNT(*) FROM sysibmadm.lockwaits” >> $LOG_FILE

# 4. 连接数
db2 “SELECT COUNT(*) FROM sysibmadm.applications” >> $LOG_FILE

# 5. 表空间使用
db2 “SELECT tbsp_name, round(tbsp_used_pages*100.0/tbsp_total_pages,2)
FROM sysibmadm.tbsp_utilization” >> $LOG_FILE

db2 connect reset

sleep 300
done

# 性能告警存储过程
CREATE OR REPLACE PROCEDURE SP_CHECK_PERFORMANCE_ALERT()
LANGUAGE SQL
BEGIN
DECLARE v_bufferpool_hit DECIMAL(5, 2);
DECLARE v_sort_overflow DECIMAL(5, 2);
DECLARE v_connection_count INTEGER;
DECLARE v_tablespace_usage DECIMAL(5, 2);

— 检查缓冲池命中率
SELECT AVG(DATA_HIT_RATIO_PERCENT) INTO v_bufferpool_hit
FROM SYSIBMADM.BP_HITRATIO;

IF v_bufferpool_hit < 95 THEN INSERT INTO PERFORMANCE_ALERTS (ALERT_TIME, ALERT_TYPE, ALERT_MESSAGE) VALUES (CURRENT TIMESTAMP, 'BUFFERPOOL', 'Bufferpool hit ratio is below 95%: ' || v_bufferpool_hit || '%'); END IF; -- 检查排序溢出率 SELECT TOTAL_SORT_OVERFLOWS * 100.0 / NULLIF(TOTAL_SORTS, 0) INTO v_sort_overflow FROM SYSIBMADM.SNAPDB; IF v_sort_overflow > 5 THEN
INSERT INTO PERFORMANCE_ALERTS (ALERT_TIME, ALERT_TYPE, ALERT_MESSAGE)
VALUES (CURRENT TIMESTAMP, ‘SORT’,
‘Sort overflow ratio is above 5%: ‘ || v_sort_overflow || ‘%’);
END IF;

— 检查连接数
SELECT COUNT(*) INTO v_connection_count
FROM SYSIBMADM.APPLICATIONS;

IF v_connection_count > 500 THEN
INSERT INTO PERFORMANCE_ALERTS (ALERT_TIME, ALERT_TYPE, ALERT_MESSAGE)
VALUES (CURRENT TIMESTAMP, ‘CONNECTION’,
‘Connection count is above 500: ‘ || v_connection_count);
END IF;

— 检查表空间使用率
SELECT MAX(TBSP_USED_PAGES * 100.0 / TBSP_TOTAL_PAGES) INTO v_tablespace_usage
FROM SYSIBMADM.TBSP_UTILIZATION;

IF v_tablespace_usage > 85 THEN
INSERT INTO PERFORMANCE_ALERTS (ALERT_TIME, ALERT_TYPE, ALERT_MESSAGE)
VALUES (CURRENT TIMESTAMP, ‘TABLESPACE’,
‘Tablespace usage is above 85%: ‘ || v_tablespace_usage || ‘%’);
END IF;
END;

# 对比当前性能与基线
SELECT
CURRENT.METRIC_NAME,
CURRENT.METRIC_VALUE AS CURRENT_VALUE,
BASELINE.AVG_VALUE AS BASELINE_AVG,
BASELINE.MIN_VALUE AS BASELINE_MIN,
BASELINE.MAX_VALUE AS BASELINE_MAX,
(CURRENT.METRIC_VALUE – BASELINE.AVG_VALUE) / BASELINE.AVG_VALUE * 100 AS DEVIATION_PERCENT
FROM (
SELECT METRIC_NAME, METRIC_VALUE
FROM PERFORMANCE_BASELINE
WHERE COLLECT_TIME >= CURRENT TIMESTAMP – 1 HOUR
) CURRENT
JOIN BASELINE_STATISTICS BASELINE ON CURRENT.METRIC_NAME = BASELINE.METRIC_NAME
WHERE ABS((CURRENT.METRIC_VALUE – BASELINE.AVG_VALUE) / BASELINE.AVG_VALUE * 100) > 20;

# 性能趋势分析
SELECT
METRIC_NAME,
DATE(COLLECT_TIME) AS COLLECT_DATE,
AVG(METRIC_VALUE) AS DAILY_AVG
FROM PERFORMANCE_BASELINE
WHERE COLLECT_TIME >= CURRENT DATE – 30 DAYS
GROUP BY METRIC_NAME, DATE(COLLECT_TIME)
ORDER BY METRIC_NAME, COLLECT_DATE;

# 性能报告生成脚本
#!/bin/bash
# generate_performance_report.sh

DBNAME=FGEDB
REPORT_DATE=$(date +%Y%m%d)
REPORT_FILE=/db2/reports/performance_report_$REPORT_DATE.txt

echo “=== DB2 Performance Report ===” > $REPORT_FILE
echo “Date: $(date)” >> $REPORT_FILE
echo “” >> $REPORT_FILE

db2 connect to $DBNAME

echo “1. Bufferpool Hit Ratio” >> $REPORT_FILE
db2 “SELECT bpname, data_hit_ratio_percent FROM sysibmadm.bp_hitratio” >> $REPORT_FILE
echo “” >> $REPORT_FILE

echo “2. Top 10 Slow Queries” >> $REPORT_FILE
db2 “SELECT substr(stmt_text,1,100), total_exec_time
FROM sysibmadm.top_dynamic_sql
ORDER BY total_exec_time DESC FETCH FIRST 10 ROWS ONLY” >> $REPORT_FILE
echo “” >> $REPORT_FILE

echo “3. Lock Waits” >> $REPORT_FILE
db2 “SELECT COUNT(*) FROM sysibmadm.lockwaits” >> $REPORT_FILE
echo “” >> $REPORT_FILE

echo “4. Connection Count” >> $REPORT_FILE
db2 “SELECT COUNT(*) FROM sysibmadm.applications” >> $REPORT_FILE
echo “” >> $REPORT_FILE

echo “5. Tablespace Usage” >> $REPORT_FILE
db2 “SELECT tbsp_name, round(tbsp_used_pages*100.0/tbsp_total_pages,2)
FROM sysibmadm.tbsp_utilization” >> $REPORT_FILE
echo “” >> $REPORT_FILE

echo “6. Performance Alerts” >> $REPORT_FILE
db2 “SELECT * FROM performance_alerts
WHERE alert_time >= CURRENT DATE – 1 DAY” >> $REPORT_FILE

db2 connect reset

echo “Performance report generated: $REPORT_FILE”