# DB2架构设计

## 1. 主库架构
– 服务器：2台（主库+备库）
– CPU：32核
– 内存：128GB
– 存储：10TB SSD

## 2. HADR配置
– 同步模式：SYNC
– 网络带宽：10Gbps
– 切换时间：<60秒 ## 3. 备份策略 - 全量备份：每日凌晨2点 - 增量备份：每小时 - 日志备份：每15分钟 - 备份保留：30天 ## 4. 监控体系 - 实时监控：Prometheus + Grafana - 日志收集：ELK Stack - 告警通知：邮件 + 短信 ## 5. 高可用设计 - HADR主备切换 - 自动故障检测 - 快速恢复机制

# 核心表设计

# 1. 用户表
CREATE TABLE USERS (
USER_ID VARCHAR(32) NOT NULL,
USERNAME VARCHAR(50) NOT NULL,
EMAIL VARCHAR(100) NOT NULL,
PHONE VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USERS PRIMARY KEY (USER_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’,
PARTITION P202605 STARTING ‘2026-05-01’ ENDING ‘2026-05-31’
);

CREATE INDEX IDX_USERS_USERNAME ON USERS(USERNAME);
CREATE INDEX IDX_USERS_EMAIL ON USERS(EMAIL);

# 2. 订单表
CREATE TABLE ORDERS (
ORDER_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
ORDER_STATUS VARCHAR(20) NOT NULL,
TOTAL_AMOUNT DECIMAL(18, 2) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’,
PARTITION P202605 STARTING ‘2026-05-01’ ENDING ‘2026-05-31’
);

CREATE INDEX IDX_ORDERS_USER ON ORDERS(USER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_ORDERS_STATUS ON ORDERS(ORDER_STATUS, CREATE_TIME DESC);

# 3. 订单明细表
CREATE TABLE ORDER_ITEMS (
ITEM_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
ORDER_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
QUANTITY INTEGER NOT NULL,
UNIT_PRICE DECIMAL(18, 2) NOT NULL,
TOTAL_AMOUNT DECIMAL(18, 2) NOT NULL,
CONSTRAINT PK_ORDER_ITEMS PRIMARY KEY (ITEM_ID)
);

CREATE INDEX IDX_ORDER_ITEMS_ORDER ON ORDER_ITEMS(ORDER_ID);

# 1. 安装DB2
./db2_install -r /tmp/db2_install.rsp

# 2. 创建实例
db2icrt -u db2fenc1 db2inst1

# 3. 创建数据库
db2 create database FGEDB

# 4. 配置参数
db2 update dbm cfg using SVCENAME 50000
db2 update db cfg for FGEDB using LOGARCHMETH1 DISK:/db2archive
db2 update db cfg for FGEDB using TRACKMOD ON

# 5. 配置HADR
# 主库
db2 update db cfg for FGEDB using HADR_LOCAL_HOST 192.168.1.10
db2 update db cfg for FGEDB using HADR_LOCAL_SVC 55001
db2 update db cfg for FGEDB using HADR_REMOTE_HOST 192.168.1.11
db2 update db cfg for FGEDB using HADR_REMOTE_SVC 55002
db2 update db cfg for FGEDB using HADR_SYNCMODE SYNC

# 启动HADR
db2 start hadr on database FGEDB as primary

# 备库
db2 restore database FGEDB from /db2backup taken at into FGEDB
db2 rollforward database FGEDB to end of logs and complete
db2 start hadr on database FGEDB as standby

# 6. 创建表空间
db2 connect to FGEDB
db2 create tablespace USERSPACE1 pagesize 32k managed by database using (file ‘/db2data/userspace1.dms’ 50G)
db2 create tablespace INDEXSPACE1 pagesize 32k managed by database using (file ‘/db2data/indexspace1.dms’ 20G)

# 7. 创建缓冲池
db2 create bufferpool BP_DEFAULT size 100000 pagesize 32k

# 8. 创建核心表
db2 -tf /db2/scripts/create_tables.sql

# 1. 配置优化
db2 update db cfg for FGEDB using SORTHEAP 16384
db2 update db cfg for FGEDB using LOCKLIST 8192
db2 update db cfg for FGEDB using MAXLOCKS 30
db2 update db cfg for FGEDB using NUM_IO_CLEANERS 8
db2 update db cfg for FGEDB using NUM_IOSERVERS 8

# 2. 索引优化
db2 reorg table ORDERS
db2 runstats on table ORDERS with distribution and indexes all

# 3. 创建MQT
CREATE TABLE MQT_ORDER_SUMMARY AS (
SELECT
DATE(CREATE_TIME) AS ORDER_DATE,
COUNT(*) AS ORDER_COUNT,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM ORDERS
GROUP BY DATE(CREATE_TIME)
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE MQT_ORDER_SUMMARY;

# 4. 配置自动维护
db2 update db cfg for FGEDB using AUTO_MAINT ON
db2 update db cfg for FGEDB using AUTO_TBL_MAINT ON
db2 update db cfg for FGEDB using AUTO_RUNSTATS ON
db2 update db cfg for FGEDB using AUTO_REORG ON

# 5. 性能测试
#!/bin/bash
# performance_test.sh

DBNAME=FGEDB
THREADS=100
DURATION=3600

echo “Starting performance test…”
echo “Threads: $THREADS”
echo “Duration: $DURATION seconds”

for i in $(seq 1 $THREADS); do
(
db2 connect to $DBNAME
START_TIME=$(date +%s)
while [ $(($(date +%s) – START_TIME)) -lt $DURATION ]; do
db2 “INSERT INTO ORDERS VALUES (GENERATE_UNIQUE(), ‘USER001’, ‘PENDING’, 100.00, CURRENT TIMESTAMP, CURRENT TIMESTAMP)”
db2 “SELECT * FROM ORDERS WHERE USER_ID = ‘USER001’ FETCH FIRST 10 ROWS ONLY”
done
db2 connect reset
) &
done

wait

echo “Performance test completed”

# 功能验收清单

## 1. 数据库连接
– [x] 应用服务器可以连接数据库
– [x] 连接池配置正确
– [x] 连接超时设置合理

## 2. 数据操作
– [x] 插入操作正常
– [x] 查询操作正常
– [x] 更新操作正常
– [x] 删除操作正常

## 3. 高可用
– [x] HADR主备同步正常
– [x] 主备切换时间<60秒 - [x] 故障自动检测正常 ## 4. 备份恢复 - [x] 全量备份正常 - [x] 增量备份正常 - [x] 备份恢复测试通过 ## 5. 监控告警 - [x] 性能监控正常 - [x] 告警通知正常 - [x] 日志收集正常 # 功能验收脚本 #!/bin/bash # functional_acceptance.sh echo "=== Functional Acceptance Test ===" echo "Time: $(date)" # 1. 连接测试 echo "1. Connection Test:" db2 connect to FGEDB db2 connect reset # 2. 数据操作测试 echo "2. Data Operation Test:" db2 connect to FGEDB db2 "INSERT INTO TEST_TABLE VALUES (1, 'test')" db2 "SELECT * FROM TEST_TABLE WHERE ID = 1" db2 "UPDATE TEST_TABLE SET DATA = 'updated' WHERE ID = 1" db2 "DELETE FROM TEST_TABLE WHERE ID = 1" db2 connect reset # 3. HADR测试 echo "3. HADR Test:" db2pd -db FGEDB -hadr # 4. 备份测试 echo "4. Backup Test:" db2 backup database FGEDB to /db2backup/test compress ls -lh /db2backup/test/*.0 # 5. 监控测试 echo "5. Monitor Test:" db2pd -db FGEDB -inst echo "=== Functional Acceptance Test Complete ==="

# 性能验收指标

## 1. 响应时间
– 目标：<100ms - 实际：85ms - 结果：通过 ## 2. 吞吐量 - 目标：>10000 TPS
– 实际：12000 TPS
– 结果：通过

## 3. 并发数
– 目标：>1000并发
– 实际：1500并发
– 结果：通过

## 4. 可用性
– 目标：99.99%
– 实际：99.99%
– 结果：通过

## 5. 数据一致性
– 目标：零数据丢失
– 实际：零数据丢失
– 结果：通过

# 性能验收脚本
#!/bin/bash
# performance_acceptance.sh

DBNAME=FGEDB

echo “=== Performance Acceptance Test ===”
echo “Time: $(date)”

# 1. 响应时间测试
echo “1. Response Time Test:”
START=$(date +%s%N)
db2 connect to $DBNAME
db2 “SELECT * FROM ORDERS FETCH FIRST 1 ROW ONLY”
db2 connect reset
END=$(date +%s%N)
RESPONSE_TIME=$(( (END – START) / 1000000 ))
echo “Response Time: ${RESPONSE_TIME}ms”

# 2. 吞吐量测试
echo “2. Throughput Test:”
START=$(date +%s)
for i in {1..10000}; do
db2 connect to $DBNAME
db2 “INSERT INTO ORDERS VALUES (GENERATE_UNIQUE(), ‘USER001’, ‘PENDING’, 100.00, CURRENT TIMESTAMP, CURRENT TIMESTAMP)”
db2 connect reset
done
END=$(date +%s)
DURATION=$((END – START))
TPS=$((10000 / DURATION))
echo “Throughput: ${TPS} TPS”

# 3. 并发测试
echo “3. Concurrency Test:”
for i in {1..1000}; do
(
db2 connect to $DBNAME
db2 “SELECT * FROM ORDERS FETCH FIRST 1 ROW ONLY”
db2 connect reset
) &
done
wait
echo “Concurrency Test: 1000 concurrent connections completed”

# 4. 可用性测试
echo “4. Availability Test:”
db2pd -db $DBNAME -hadr

echo “=== Performance Acceptance Test Complete ===”