# 简单分批删除
DELETE FROM ORDERS
WHERE CREATE_TIME < '2026-01-01' FETCH FIRST 10000 ROWS ONLY; # 循环分批删除 CREATE OR REPLACE PROCEDURE SP_BATCH_DELETE( IN p_table_name VARCHAR(100), IN p_where_clause VARCHAR(500), IN p_batch_size INTEGER ) LANGUAGE SQL BEGIN DECLARE v_count INTEGER DEFAULT 1; DECLARE v_total INTEGER DEFAULT 0; DECLARE v_sql VARCHAR(1000); WHILE v_count > 0 DO
SET v_sql = ‘DELETE FROM ‘ || p_table_name ||
‘ WHERE ‘ || p_where_clause ||
‘ FETCH FIRST ‘ || CHAR(p_batch_size) || ‘ ROWS ONLY’;

EXECUTE IMMEDIATE v_sql;

GET DIAGNOSTICS v_count = ROW_COUNT;
SET v_total = v_total + v_count;

COMMIT;

CALL DBMS_ALERT.SLEEP(1);
END WHILE;

INSERT INTO CLEAN_LOG (TABLE_NAME, DELETE_COUNT, CLEAN_TIME)
VALUES (p_table_name, v_total, CURRENT TIMESTAMP);
END;

# 调用存储过程
CALL SP_BATCH_DELETE(‘ORDERS’, ‘CREATE_TIME < ''2026-01-01''', 10000);

# 分离分区
ALTER TABLE ORDERS
DETACH PARTITION P202601 INTO TABLE ORDERS_P202601;

# 删除分离的表
DROP TABLE ORDERS_P202601;

# 查看分区信息
SELECT
TABNAME,
DATAPARTITIONNAME,
LOWVALUE,
HIGHVALUE
FROM SYSCAT.DATAPARTITIONS
WHERE TABNAME = ‘ORDERS’;

# 自动化分区清理
CREATE OR REPLACE PROCEDURE SP_CLEAN_OLD_PARTITIONS(
IN p_table_name VARCHAR(100),
IN p_keep_months INTEGER
)
LANGUAGE SQL
BEGIN
DECLARE v_partition_name VARCHAR(100);
DECLARE v_high_value DATE;

FOR part AS cur CURSOR FOR
SELECT DATAPARTITIONNAME, HIGHVALUE
FROM SYSCAT.DATAPARTITIONS
WHERE TABNAME = UPPER(p_table_name)
DO
SET v_partition_name = part.DATAPARTITIONNAME;
SET v_high_value = DATE(part.HIGHVALUE);

IF v_high_value < CURRENT DATE - p_keep_months MONTHS THEN EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DETACH PARTITION ' || v_partition_name || ' INTO TABLE ' || p_table_name || '_' || v_partition_name; EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name || '_' || v_partition_name; END IF; END FOR; END;

# 离线重组
REORG TABLE ORDERS;

# 在线重组
REORG TABLE ORDERS INPLACE;

# 查看重组进度
SELECT
TABNAME,
REORG_PHASE,
REORG_STATUS,
REORG_COMPLETION
FROM SYSIBMADM.SNAPREORG;

# 重组索引
REORG INDEX ALL FOR TABLE ORDERS;

# 重组表空间
REORG TABLESPACE USERSPACE1;

# 查看表碎片
SELECT
TABNAME,
NPAGES,
FPAGES,
OVERFLOW,
FPAGES – NPAGES AS FRAGMENTED_PAGES
FROM SYSCAT.TABLES
WHERE TABSCHEMA = ‘FGEDB’
ORDER BY FRAGMENTED_PAGES DESC;

# 降低高水位
ALTER TABLE ORDERS REDUCE MAX;

# 回收表空间空间
ALTER TABLESPACE USERSPACE1 REDUCE MAX;

# 查看表空间使用
SELECT
TBSP_NAME,
TBSP_USED_PAGES,
TBSP_FREE_PAGES,
TBSP_HIGH_WATER_MARK
FROM SYSIBMADM.TBSP_UTILIZATION;

# 清理临时表空间
# 重启数据库自动清理

# 查看表大小
SELECT
TABNAME,
CARD,
NPAGES * 8 / 1024 AS SIZE_MB
FROM SYSCAT.TABLES
WHERE TABSCHEMA = ‘FGEDB’
ORDER BY NPAGES DESC;

# 清理后更新统计信息
RUNSTATS ON TABLE ORDERS WITH DISTRIBUTION AND INDEXES ALL;

# 创建清理脚本
#!/bin/bash
# clean_historical_data.sh

DBNAME=FGEDB
DBUSER=db2inst1

db2 connect to $DBNAME user $DBUSER

echo “Starting historical data cleanup at $(date)”

# 清理订单数据
echo “Cleaning orders older than 90 days…”
db2 “CALL SP_BATCH_DELETE(‘ORDERS’, ‘CREATE_TIME < CURRENT DATE - 90 DAYS', 10000)" # 清理日志数据 echo "Cleaning logs older than 30 days..." db2 "CALL SP_BATCH_DELETE('LOGS', 'LOG_TIME < CURRENT TIMESTAMP - 30 DAYS', 10000)" # 清理临时数据 echo "Cleaning temporary data..." db2 "DELETE FROM TEMP_DATA WHERE CREATE_TIME < CURRENT TIMESTAMP - 1 HOUR" # 重组表 echo "Reorganizing tables..." db2 "REORG TABLE ORDERS INPLACE" db2 "REORG TABLE LOGS INPLACE" # 更新统计信息 echo "Updating statistics..." db2 "RUNSTATS ON TABLE ORDERS WITH DISTRIBUTION AND INDEXES ALL" db2 "RUNSTATS ON TABLE LOGS WITH DISTRIBUTION AND INDEXES ALL" db2 connect reset echo "Cleanup completed at $(date)" # 添加到crontab # 0 2 * * * /db2/scripts/clean_historical_data.sh >> /db2/logs/cleanup.log 2>&1

# 创建清理日志表
CREATE TABLE CLEAN_LOG (
LOG_ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
TABLE_NAME VARCHAR(100) NOT NULL,
DELETE_COUNT INTEGER NOT NULL,
CLEAN_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
DURATION_SECONDS INTEGER
);

# 监控清理进度
SELECT
TABLE_NAME,
DELETE_COUNT,
CLEAN_TIME,
DURATION_SECONDS
FROM CLEAN_LOG
ORDER BY CLEAN_TIME DESC;

# 监控表大小变化
SELECT
TABNAME,
CARD,
NPAGES,
CARD – LAG(CARD, 1) OVER (ORDER BY STATISTICS_TIME) AS CARD_CHANGE,
NPAGES – LAG(NPAGES, 1) OVER (ORDER BY STATISTICS_TIME) AS PAGES_CHANGE,
STATISTICS_TIME
FROM (
SELECT
TABNAME,
CARD,
NPAGES,
STATISTICS_TIME
FROM SYSCAT.TABLES
WHERE TABSCHEMA = ‘FGEDB’
UNION ALL
SELECT
TABNAME,
CARD,
NPAGES,
STATISTICS_TIME
FROM SYSCAT.HIST_TABLES
WHERE TABSCHEMA = ‘FGEDB’
)
ORDER BY STATISTICS_TIME DESC;

# 创建清理告警
CREATE OR REPLACE PROCEDURE SP_CHECK_CLEAN_PROGRESS()
LANGUAGE SQL
BEGIN
DECLARE v_last_clean_time TIMESTAMP;

SELECT MAX(CLEAN_TIME) INTO v_last_clean_time
FROM CLEAN_LOG
WHERE TABLE_NAME = ‘ORDERS’;

IF v_last_clean_time < CURRENT TIMESTAMP - 7 DAYS THEN INSERT INTO ALERT_LOG ( ALERT_TIME, ALERT_TYPE, ALERT_MESSAGE ) VALUES ( CURRENT TIMESTAMP, 'CLEAN', 'Orders cleanup not run in 7 days' ); END IF; END;