# 创建分区表
CREATE TABLE ORDERS (
ORDER_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
CREATE_TIME TIMESTAMP NOT NULL,
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID, CREATE_TIME)
) PARTITION BY RANGE (CREATE_TIME)
(
PARTITION P202601 STARTING ‘2026-01-01’ ENDING ‘2026-01-31’,
PARTITION P202602 STARTING ‘2026-02-01’ ENDING ‘2026-02-28’,
PARTITION P202603 STARTING ‘2026-03-01’ ENDING ‘2026-03-31’,
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’
);

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

# 导出归档数据
EXPORT TO /archive/orders_202601.ixf OF IXF
SELECT * FROM ORDERS_P202601;

# 压缩归档文件
gzip /archive/orders_202601.ixf;

# 删除归档表
DROP TABLE ORDERS_P202601;

# 添加新分区
ALTER TABLE ORDERS
ADD PARTITION P202605 STARTING ‘2026-05-01’ ENDING ‘2026-05-31’;

# 创建归档表
CREATE TABLE ORDERS_ARCHIVE (
ORDER_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
CREATE_TIME TIMESTAMP NOT NULL,
ARCHIVE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ORDERS_ARCHIVE PRIMARY KEY (ORDER_ID, CREATE_TIME)
) IN ARCHIVE_TS;

# 归档数据
INSERT INTO ORDERS_ARCHIVE
SELECT *, CURRENT TIMESTAMP
FROM ORDERS
WHERE CREATE_TIME < CURRENT DATE - 90 DAYS; # 删除已归档数据 DELETE FROM ORDERS WHERE CREATE_TIME < CURRENT DATE - 90 DAYS; # 创建归档存储过程 CREATE OR REPLACE PROCEDURE SP_ARCHIVE_ORDERS( IN p_days INTEGER ) LANGUAGE SQL BEGIN DECLARE v_count INTEGER; SET v_count = 0; FOR i AS cur CURSOR FOR SELECT ORDER_ID, CUSTOMER_ID, TOTAL_AMOUNT, CREATE_TIME FROM ORDERS WHERE CREATE_TIME < CURRENT DATE - p_days DAYS DO INSERT INTO ORDERS_ARCHIVE ( ORDER_ID, CUSTOMER_ID, TOTAL_AMOUNT, CREATE_TIME ) VALUES ( i.ORDER_ID, i.CUSTOMER_ID, i.TOTAL_AMOUNT, i.CREATE_TIME ); DELETE FROM ORDERS WHERE ORDER_ID = i.ORDER_ID; SET v_count = v_count + 1; IF MOD(v_count, 1000) = 0 THEN COMMIT; END IF; END FOR; COMMIT; END;

# 清理过期数据
DELETE FROM LOGS
WHERE LOG_TIME < CURRENT TIMESTAMP - 30 DAYS; # 批量删除 CREATE OR REPLACE PROCEDURE SP_CLEAN_LOGS( IN p_days INTEGER ) LANGUAGE SQL BEGIN DECLARE v_count INTEGER DEFAULT 0; DECLARE v_total INTEGER DEFAULT 0; DELETE_LOOP: LOOP DELETE FROM LOGS WHERE LOG_TIME < CURRENT TIMESTAMP - p_days DAYS FETCH FIRST 10000 ROWS ONLY; GET DIAGNOSTICS v_count = ROW_COUNT; SET v_total = v_total + v_count; IF v_count = 0 THEN LEAVE DELETE_LOOP; END IF; COMMIT; END LOOP; COMMIT; END; # 清理临时数据 DELETE FROM TEMP_DATA WHERE CREATE_TIME < CURRENT TIMESTAMP - 1 HOUR; # 清理测试数据 DELETE FROM TEST_TABLE WHERE TEST_FLAG = 'Y';

# 创建数据保留策略表
CREATE TABLE DATA_RETENTION_POLICY (
TABLE_NAME VARCHAR(100) NOT NULL,
RETENTION_DAYS INTEGER NOT NULL,
ARCHIVE_FLAG VARCHAR(1) DEFAULT ‘Y’,
DELETE_FLAG VARCHAR(1) DEFAULT ‘Y’,
CONSTRAINT PK_RETENTION_POLICY PRIMARY KEY (TABLE_NAME)
);

INSERT INTO DATA_RETENTION_POLICY VALUES
(‘ORDERS’, 365, ‘Y’, ‘Y’),
(‘LOGS’, 30, ‘N’, ‘Y’),
(‘TRANSACTIONS’, 180, ‘Y’, ‘Y’);

# 自动化清理脚本
CREATE OR REPLACE PROCEDURE SP_AUTO_CLEAN_DATA()
LANGUAGE SQL
BEGIN
DECLARE v_table_name VARCHAR(100);
DECLARE v_retention_days INTEGER;
DECLARE v_archive_flag VARCHAR(1);
DECLARE v_delete_flag VARCHAR(1);

FOR policy AS cur CURSOR FOR
SELECT TABLE_NAME, RETENTION_DAYS, ARCHIVE_FLAG, DELETE_FLAG
FROM DATA_RETENTION_POLICY
DO
SET v_table_name = policy.TABLE_NAME;
SET v_retention_days = policy.RETENTION_DAYS;
SET v_archive_flag = policy.ARCHIVE_FLAG;
SET v_delete_flag = policy.DELETE_FLAG;

IF v_archive_flag = ‘Y’ THEN
CALL SP_ARCHIVE_TABLE(v_table_name, v_retention_days);
END IF;

IF v_delete_flag = ‘Y’ THEN
CALL SP_CLEAN_TABLE(v_table_name, v_retention_days);
END IF;
END FOR;
END;

# 创建定时任务表
CREATE TABLE SCHEDULED_TASKS (
TASK_ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
TASK_NAME VARCHAR(100) NOT NULL,
TASK_TYPE VARCHAR(50) NOT NULL,
TASK_SCHEDULE VARCHAR(100) NOT NULL,
TASK_STATUS VARCHAR(20) DEFAULT ‘ENABLED’,
LAST_RUN_TIME TIMESTAMP,
NEXT_RUN_TIME TIMESTAMP
);

INSERT INTO SCHEDULED_TASKS VALUES
(1, ‘ARCHIVE_ORDERS’, ‘ARCHIVE’, ‘0 2 * * *’, ‘ENABLED’, NULL, NULL),
(2, ‘CLEAN_LOGS’, ‘CLEAN’, ‘0 3 * * *’, ‘ENABLED’, NULL, NULL),
(3, ‘BACKUP_DATABASE’, ‘BACKUP’, ‘0 1 * * *’, ‘ENABLED’, NULL, NULL);

# 使用cron调度
# 编辑crontab
crontab -e

# 添加定时任务
0 2 * * * /db2/scripts/archive_orders.sh
0 3 * * * /db2/scripts/clean_logs.sh
0 1 * * * /db2/scripts/backup_database.sh

# 归档脚本示例
#!/bin/bash
# archive_orders.sh

db2 connect to FGEDB
db2 “CALL SP_ARCHIVE_ORDERS(90)”
db2 connect reset

echo “Archive completed at $(date)” >> /db2/logs/archive.log

# 监控表空间使用
SELECT
TBSP_NAME,
TBSP_USED_PAGES,
TBSP_FREE_PAGES,
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 > 80;

# 监控数据增长
SELECT
TABNAME,
CARD,
NPAGES,
FPAGES
FROM SYSCAT.TABLES
WHERE TABSCHEMA = ‘FGEDB’
ORDER BY NPAGES DESC;

# 创建告警存储过程
CREATE OR REPLACE PROCEDURE SP_CHECK_TABLESPACE()
LANGUAGE SQL
BEGIN
DECLARE v_usage DECIMAL(5, 2);

SELECT MAX(TBSP_USED_PAGES * 100.0 / TBSP_TOTAL_PAGES)
INTO v_usage
FROM SYSIBMADM.TBSP_UTILIZATION;

IF v_usage > 80 THEN
INSERT INTO ALERT_LOG (
ALERT_TIME, ALERT_TYPE, ALERT_MESSAGE
) VALUES (
CURRENT TIMESTAMP, ‘TABLESPACE’, ‘Tablespace usage exceeds 80%’
);
END IF;
END;

# 定期执行监控
CALL SP_CHECK_TABLESPACE();