# 创建存储容量统计表
CREATE TABLE STORAGE_CAPACITY (
CAPACITY_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
STORAGE_TYPE VARCHAR(50) NOT NULL,
TOTAL_SIZE_GB DECIMAL(18, 2) NOT NULL,
USED_SIZE_GB DECIMAL(18, 2) NOT NULL,
FREE_SIZE_GB DECIMAL(18, 2) NOT NULL,
USAGE_PERCENT DECIMAL(5, 2) NOT NULL,
COLLECT_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_STORAGE_CAPACITY PRIMARY KEY (CAPACITY_ID)
);

# 采集存储容量数据
CREATE OR REPLACE PROCEDURE SP_COLLECT_STORAGE_CAPACITY()
LANGUAGE SQL
BEGIN
DECLARE v_total_gb DECIMAL(18, 2);
DECLARE v_used_gb DECIMAL(18, 2);
DECLARE v_free_gb DECIMAL(18, 2);
DECLARE v_usage_percent DECIMAL(5, 2);

— 表空间容量
FOR tbsp_cursor AS
SELECT
TBSP_NAME,
TBSP_TOTAL_PAGES * 4.0 / 1024 / 1024 AS TOTAL_GB,
TBSP_USED_PAGES * 4.0 / 1024 / 1024 AS USED_GB
FROM SYSIBMADM.TBSP_UTILIZATION
DO
SET v_total_gb = tbsp_cursor.TOTAL_GB;
SET v_used_gb = tbsp_cursor.USED_GB;
SET v_free_gb = v_total_gb – v_used_gb;
SET v_usage_percent = v_used_gb / v_total_gb * 100;

INSERT INTO STORAGE_CAPACITY (
STORAGE_TYPE, TOTAL_SIZE_GB, USED_SIZE_GB,
FREE_SIZE_GB, USAGE_PERCENT
) VALUES (
‘TABLESPACE_’ || tbsp_cursor.TBSP_NAME,
v_total_gb, v_used_gb, v_free_gb, v_usage_percent
);
END FOR;
END;

# 执行采集
CALL SP_COLLECT_STORAGE_CAPACITY();

# 创建存储增长记录表
CREATE TABLE STORAGE_GROWTH (
GROWTH_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
STORAGE_TYPE VARCHAR(50) NOT NULL,
SIZE_GB DECIMAL(18, 2) NOT NULL,
GROWTH_RATE DECIMAL(5, 2),
RECORD_DATE DATE NOT NULL,
CONSTRAINT PK_STORAGE_GROWTH PRIMARY KEY (GROWTH_ID)
);

# 计算存储增长率
SELECT
STORAGE_TYPE,
SIZE_GB,
LAG(SIZE_GB) OVER (PARTITION BY STORAGE_TYPE ORDER BY RECORD_DATE) AS PREV_SIZE_GB,
(SIZE_GB – LAG(SIZE_GB) OVER (PARTITION BY STORAGE_TYPE ORDER BY RECORD_DATE)) /
LAG(SIZE_GB) OVER (PARTITION BY STORAGE_TYPE ORDER BY RECORD_DATE) * 100 AS DAILY_GROWTH_RATE
FROM STORAGE_GROWTH
WHERE RECORD_DATE >= CURRENT DATE – 30 DAYS;

# 预测未来存储需求
SELECT
STORAGE_TYPE,
SIZE_GB AS CURRENT_SIZE,
SIZE_GB * (1 + AVG_GROWTH_RATE / 100 * 30) AS PREDICTED_SIZE_30D,
SIZE_GB * (1 + AVG_GROWTH_RATE / 100 * 90) AS PREDICTED_SIZE_90D,
SIZE_GB * (1 + AVG_GROWTH_RATE / 100 * 180) AS PREDICTED_SIZE_180D
FROM (
SELECT
STORAGE_TYPE,
SIZE_GB,
AVG((SIZE_GB – LAG(SIZE_GB) OVER (PARTITION BY STORAGE_TYPE ORDER BY RECORD_DATE)) /
LAG(SIZE_GB) OVER (PARTITION BY STORAGE_TYPE ORDER BY RECORD_DATE) * 100)
OVER (PARTITION BY STORAGE_TYPE) AS AVG_GROWTH_RATE
FROM STORAGE_GROWTH
WHERE RECORD_DATE >= CURRENT DATE – 30 DAYS
) T;

# 创建CPU使用统计表
CREATE TABLE CPU_USAGE (
USAGE_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
CPU_USAGE_PERCENT DECIMAL(5, 2) NOT NULL,
CPU_IDLE_PERCENT DECIMAL(5, 2) NOT NULL,
CPU_SYSTEM_PERCENT DECIMAL(5, 2) NOT NULL,
CPU_USER_PERCENT DECIMAL(5, 2) NOT NULL,
COLLECT_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CPU_USAGE PRIMARY KEY (USAGE_ID)
);

# 采集CPU使用数据
#!/bin/bash
# collect_cpu_usage.sh

DBNAME=FGEDB

while true; do
CPU_USAGE=$(top -bn1 | grep “Cpu(s)” | awk ‘{print $2}’ | cut -d% -f1)
CPU_IDLE=$(top -bn1 | grep “Cpu(s)” | awk ‘{print $5}’ | cut -d% -f1)
CPU_SYSTEM=$(top -bn1 | grep “Cpu(s)” | awk ‘{print $4}’ | cut -d% -f1)
CPU_USER=$(top -bn1 | grep “Cpu(s)” | awk ‘{print $2}’ | cut -d% -f1)

db2 connect to $DBNAME
db2 “INSERT INTO CPU_USAGE (CPU_USAGE_PERCENT, CPU_IDLE_PERCENT, CPU_SYSTEM_PERCENT, CPU_USER_PERCENT)
VALUES ($CPU_USAGE, $CPU_IDLE, $CPU_SYSTEM, $CPU_USER)”
db2 connect reset

sleep 60
done

# 分析CPU使用趋势
SELECT
DATE(COLLECT_TIME) AS USAGE_DATE,
AVG(CPU_USAGE_PERCENT) AS AVG_CPU_USAGE,
MAX(CPU_USAGE_PERCENT) AS MAX_CPU_USAGE,
MIN(CPU_USAGE_PERCENT) AS MIN_CPU_USAGE
FROM CPU_USAGE
WHERE COLLECT_TIME >= CURRENT DATE – 30 DAYS
GROUP BY DATE(COLLECT_TIME)
ORDER BY USAGE_DATE;

# 创建内存使用统计表
CREATE TABLE MEMORY_USAGE (
USAGE_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
TOTAL_MEMORY_GB DECIMAL(18, 2) NOT NULL,
USED_MEMORY_GB DECIMAL(18, 2) NOT NULL,
FREE_MEMORY_GB DECIMAL(18, 2) NOT NULL,
BUFFERPOOL_MEMORY_GB DECIMAL(18, 2) NOT NULL,
SORT_MEMORY_GB DECIMAL(18, 2) NOT NULL,
COLLECT_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MEMORY_USAGE PRIMARY KEY (USAGE_ID)
);

# 采集内存使用数据
#!/bin/bash
# collect_memory_usage.sh

DBNAME=FGEDB

while true; do
TOTAL_MEM=$(free -g | grep Mem | awk ‘{print $2}’)
USED_MEM=$(free -g | grep Mem | awk ‘{print $3}’)
FREE_MEM=$(free -g | grep Mem | awk ‘{print $4}’)

db2 connect to $DBNAME

# 获取缓冲池内存
BP_MEM=$(db2 “SELECT SUM(bpool_size * 4.0 / 1024 / 1024) FROM sysibmadm.bp_hitratio” | tail -1 | awk ‘{print $1}’)

# 获取排序内存
SORT_MEM=$(db2 “SELECT SUM(total_sorts * sort_heap_size * 4.0 / 1024 / 1024) FROM sysibmadm.snapdb” | tail -1 | awk ‘{print $1}’)

db2 “INSERT INTO MEMORY_USAGE (TOTAL_MEMORY_GB, USED_MEMORY_GB, FREE_MEMORY_GB, BUFFERPOOL_MEMORY_GB, SORT_MEMORY_GB)
VALUES ($TOTAL_MEM, $USED_MEM, $FREE_MEM, $BP_MEM, $SORT_MEM)”

db2 connect reset

sleep 60
done

# 容量监控脚本
#!/bin/bash
# capacity_monitor.sh

DBNAME=FGEDB
ALERT_THRESHOLD=85

db2 connect to $DBNAME

# 监控表空间使用率
db2 “SELECT tbsp_name, round(tbsp_used_pages*100.0/tbsp_total_pages,2) as usage_percent
FROM sysibmadm.tbsp_utilization
WHERE tbsp_used_pages*100.0/tbsp_total_pages > $ALERT_THRESHOLD” > /tmp/tablespace_alert.txt

if [ -s /tmp/tablespace_alert.txt ]; then
mail -s “DB2 Tablespace Capacity Alert” admin@example.com < /tmp/tablespace_alert.txt fi # 监控磁盘使用率 df -h | grep -E '^/dev' | awk '{print $6, $5}' | while read mount usage; do usage_num=$(echo $usage | cut -d% -f1) if [ $usage_num -gt $ALERT_THRESHOLD ]; then echo "Disk $mount usage: $usage" | mail -s "DB2 Disk Capacity Alert" admin@example.com fi done db2 connect reset

# 表空间扩容
ALTER TABLESPACE USERSPACE1 EXTEND (FILE ‘/db2data/userspace1.dms’ 10G);

# 添加新表空间容器
ALTER TABLESPACE USERSPACE1 ADD (FILE ‘/db2data/userspace1_new.dms’ 20G);

# 缓冲池扩容
ALTER BUFFERPOOL BP_DEFAULT SIZE 200000;

# 增加排序堆
UPDATE DATABASE CONFIGURATION USING SORTHEAP 16384 IMMEDIATE;

# 增加锁列表
UPDATE DATABASE CONFIGURATION USING LOCKLIST 8192 IMMEDIATE;

# 容量扩展脚本
#!/bin/bash
# capacity_expand.sh

DBNAME=FGEDB
TABLESPACE=$1
SIZE_GB=$2

db2 connect to $DBNAME

# 检查表空间使用率
USAGE=$(db2 “SELECT round(tbsp_used_pages*100.0/tbsp_total_pages,2)
FROM sysibmadm.tbsp_utilization
WHERE tbsp_name = ‘$TABLESPACE'” | tail -1 | awk ‘{print $1}’)

if [ $(echo “$USAGE > 80” | bc) -eq 1 ]; then
echo “Expanding tablespace $TABLESPACE by $SIZE_GB GB”

# 扩容表空间
db2 “ALTER TABLESPACE $TABLESPACE EXTEND (FILE ‘/db2data/${TABLESPACE}.dms’ ${SIZE_GB}G)”

# 记录扩容日志
db2 “INSERT INTO CAPACITY_EXPANSION_LOG (TABLESPACE_NAME, EXPANSION_SIZE_GB, EXPANSION_TIME)
VALUES (‘$TABLESPACE’, $SIZE_GB, CURRENT TIMESTAMP)”

echo “Tablespace expansion completed”
else
echo “Tablespace usage is below 80%, no expansion needed”
fi

db2 connect reset