# 创建主表空间 – 账户表
CREATE LARGE TABLESPACE ACCOUNT_TS
PAGESIZE 32K
MANAGED BY AUTOMATIC STORAGE
USING STOGROUP IBMSTOGROUP
EXTENTSIZE 64
PREFETCHSIZE 128
BUFFERPOOL BP32K
AUTORESIZE YES
INITIALSIZE 100G
MAXSIZE 1T;

# 创建索引表空间
CREATE LARGE TABLESPACE ACCOUNT_IDX_TS
PAGESIZE 32K
MANAGED BY AUTOMATIC STORAGE
USING STOGROUP IBMSTOGROUP
EXTENTSIZE 32
PREFETCHSIZE 64
BUFFERPOOL BP32K
AUTORESIZE YES
INITIALSIZE 50G
MAXSIZE 500G;

# 创建大对象表空间
CREATE LARGE TABLESPACE ACCOUNT_LOB_TS
PAGESIZE 32K
MANAGED BY AUTOMATIC STORAGE
USING STOGROUP IBMSTOGROUP
EXTENTSIZE 64
PREFETCHSIZE 128
BUFFERPOOL BP32K
FILE SYSTEM CACHING NO;

# 创建日志表空间
CREATE LARGE TABLESPACE TRANS_LOG_TS
PAGESIZE 8K
MANAGED BY AUTOMATIC STORAGE
USING STOGROUP IBMSTOGROUP
EXTENTSIZE 32
PREFETCHSIZE 64
BUFFERPOOL BP8K;

# 账户主表
CREATE TABLE ACCOUNT (
ACCT_ID VARCHAR(32) NOT NULL,
CUST_ID VARCHAR(32) NOT NULL,
ACCT_TYPE VARCHAR(10) NOT NULL,
ACCT_STATUS VARCHAR(10) NOT NULL,
CURRENCY VARCHAR(3) NOT NULL,
BALANCE DECIMAL(18, 2) NOT NULL DEFAULT 0,
AVAILABLE_BALANCE DECIMAL(18, 2) NOT NULL DEFAULT 0,
FROZEN_AMOUNT DECIMAL(18, 2) NOT NULL DEFAULT 0,
OPEN_DATE DATE NOT NULL,
CLOSE_DATE DATE,
BRANCH_ID VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ACCOUNT PRIMARY KEY (ACCT_ID)
) IN ACCOUNT_TS
INDEX IN ACCOUNT_IDX_TS;

# 创建索引
CREATE UNIQUE INDEX IDX_ACCOUNT_CUST ON ACCOUNT(CUST_ID, ACCT_TYPE) CLUSTER;
CREATE INDEX IDX_ACCOUNT_BRANCH ON ACCOUNT(BRANCH_ID, ACCT_STATUS);

# 创建账户余额历史表
CREATE TABLE ACCOUNT_BALANCE_HIST (
HIST_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
ACCT_ID VARCHAR(32) NOT NULL,
BALANCE_BEFORE DECIMAL(18, 2) NOT NULL,
BALANCE_AFTER DECIMAL(18, 2) NOT NULL,
CHANGE_AMOUNT DECIMAL(18, 2) NOT NULL,
TRANS_ID VARCHAR(32) NOT NULL,
TRANS_TYPE VARCHAR(20) NOT NULL,
RECORD_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ACCOUNT_BALANCE_HIST PRIMARY KEY (HIST_ID)
) PARTITION BY RANGE (RECORD_TIME)
(
STARTING ‘2026-01-01’ ENDING ‘2026-12-31’ EVERY 1 MONTHS
);

# 创建索引
CREATE INDEX IDX_ACCT_BAL_HIST_ACCT ON ACCOUNT_BALANCE_HIST(ACCT_ID, RECORD_TIME DESC);

# 交易流水表
CREATE TABLE TRANSACTION (
TRANS_ID VARCHAR(32) NOT NULL,
TRANS_TYPE VARCHAR(20) NOT NULL,
ACCT_ID_FROM VARCHAR(32),
ACCT_ID_TO VARCHAR(32),
AMOUNT DECIMAL(18, 2) NOT NULL,
CURRENCY VARCHAR(3) NOT NULL,
TRANS_STATUS VARCHAR(10) NOT NULL,
FAIL_REASON VARCHAR(200),
REMARK VARCHAR(500),
CHANNEL VARCHAR(20) NOT NULL,
OPERATOR VARCHAR(32),
BRANCH_ID VARCHAR(10),
TRANS_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TRANSACTION PRIMARY KEY (TRANS_ID)
) PARTITION BY RANGE (TRANS_TIME)
(
STARTING ‘2026-01-01’ ENDING ‘2026-12-31’ EVERY 1 DAY
);

# 创建索引
CREATE INDEX IDX_TRANS_ACCT_FROM ON TRANSACTION(ACCT_ID_FROM, TRANS_TIME DESC);
CREATE INDEX IDX_TRANS_ACCT_TO ON TRANSACTION(ACCT_ID_TO, TRANS_TIME DESC);
CREATE INDEX IDX_TRANS_STATUS ON TRANSACTION(TRANS_STATUS, TRANS_TIME);

# 创建交易明细表
CREATE TABLE TRANS_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
TRANS_ID VARCHAR(32) NOT NULL,
ACCT_ID VARCHAR(32) NOT NULL,
DIRECTION VARCHAR(10) NOT NULL,
AMOUNT DECIMAL(18, 2) NOT NULL,
BALANCE_BEFORE DECIMAL(18, 2) NOT NULL,
BALANCE_AFTER DECIMAL(18, 2) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TRANS_DETAIL PRIMARY KEY (DETAIL_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
STARTING ‘2026-01-01’ ENDING ‘2026-12-31’ EVERY 1 DAY
);

# 创建索引
CREATE INDEX IDX_TRANS_DETAIL_TRANS ON TRANS_DETAIL(TRANS_ID);
CREATE INDEX IDX_TRANS_DETAIL_ACCT ON TRANS_DETAIL(ACCT_ID, CREATE_TIME DESC);

# 创建序列
CREATE SEQUENCE SEQ_TRANS_ID
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
CACHE 1000;

# 创建流水号生成函数
CREATE OR REPLACE FUNCTION FN_GENERATE_TRANS_ID()
RETURNS VARCHAR(32)
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_timestamp VARCHAR(14);
DECLARE v_seq BIGINT;
DECLARE v_trans_id VARCHAR(32);

SET v_timestamp = TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISS’);
SET v_seq = NEXT VALUE FOR SEQ_TRANS_ID;
SET v_trans_id = v_timestamp || LPAD(CAST(MOD(v_seq, 100000) AS VARCHAR(5)), 5, ‘0’);

RETURN v_trans_id;
END;

# 使用函数生成流水号
VALUES FN_GENERATE_TRANS_ID();

# 转账存储过程
CREATE OR REPLACE PROCEDURE SP_TRANSFER(
IN p_acct_from VARCHAR(32),
IN p_acct_to VARCHAR(32),
IN p_amount DECIMAL(18, 2),
IN p_currency VARCHAR(3),
IN p_remark VARCHAR(500),
IN p_channel VARCHAR(20),
OUT p_trans_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_balance_from DECIMAL(18, 2);
DECLARE v_balance_to DECIMAL(18, 2);
DECLARE v_acct_status_from VARCHAR(10);
DECLARE v_acct_status_to VARCHAR(10);
DECLARE v_sqlcode INTEGER;
DECLARE v_sqlstate CHAR(5);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1
v_sqlcode = DB2_RETURNED_SQLCODE,
v_sqlstate = RETURNED_SQLSTATE;
ROLLBACK;
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘SQL错误: ‘ || CAST(v_sqlcode AS VARCHAR(10)) || ‘, ‘ || v_sqlstate;
END;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘转账成功’;
SET p_trans_id = FN_GENERATE_TRANS_ID();

START TRANSACTION;

SELECT BALANCE, ACCT_STATUS INTO v_balance_from, v_acct_status_from
FROM ACCOUNT WHERE ACCT_ID = p_acct_from WITH RS USE AND KEEP UPDATE LOCKS;

IF v_acct_status <> ‘NORMAL’ THEN
SIGNAL SQLSTATE ‘70001’ SET MESSAGE_TEXT = ‘转出账户状态异常’;
END IF;

IF v_balance_from < p_amount THEN SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = '余额不足'; END IF; SELECT BALANCE, ACCT_STATUS INTO v_balance_to, v_acct_status_to FROM ACCOUNT WHERE ACCT_ID = p_acct_to WITH RS USE AND KEEP UPDATE LOCKS; IF v_acct_status_to <> ‘NORMAL’ THEN
SIGNAL SQLSTATE ‘70003’ SET MESSAGE_TEXT = ‘转入账户状态异常’;
END IF;

INSERT INTO TRANSACTION (
TRANS_ID, TRANS_TYPE, ACCT_ID_FROM, ACCT_ID_TO, AMOUNT, CURRENCY,
TRANS_STATUS, REMARK, CHANNEL
) VALUES (
p_trans_id, ‘TRANSFER’, p_acct_from, p_acct_to, p_amount, p_currency,
‘PROCESSING’, p_remark, p_channel
);

UPDATE ACCOUNT
SET BALANCE = BALANCE – p_amount,
AVAILABLE_BALANCE = AVAILABLE_BALANCE – p_amount,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE ACCT_ID = p_acct_from;

INSERT INTO ACCOUNT_BALANCE_HIST (
ACCT_ID, BALANCE_BEFORE, BALANCE_AFTER, CHANGE_AMOUNT,
TRANS_ID, TRANS_TYPE
) VALUES (
p_acct_from, v_balance_from, v_balance_from – p_amount, -p_amount,
p_trans_id, ‘TRANSFER_OUT’
);

UPDATE ACCOUNT
SET BALANCE = BALANCE + p_amount,
AVAILABLE_BALANCE = AVAILABLE_BALANCE + p_amount,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE ACCT_ID = p_acct_to;

INSERT INTO ACCOUNT_BALANCE_HIST (
ACCT_ID, BALANCE_BEFORE, BALANCE_AFTER, CHANGE_AMOUNT,
TRANS_ID, TRANS_TYPE
) VALUES (
p_acct_to, v_balance_to, v_balance_to + p_amount, p_amount,
p_trans_id, ‘TRANSFER_IN’
);

UPDATE TRANSACTION
SET TRANS_STATUS = ‘SUCCESS’,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE TRANS_ID = p_trans_id;

COMMIT;

END;

# 调用转账存储过程
CALL SP_TRANSFER(
‘ACCT001’, ‘ACCT002’, 1000.00, ‘CNY’, ‘测试转账’, ‘MOBILE’,
?, ?, ?
);

# 配置数据库参数
UPDATE DATABASE CONFIGURATION FOR fgedb USING
LOCKLIST 10000
MAXLOCKS 50
LOCKTIMEOUT 30
DLCHKTIME 10000;

# 设置应用隔离级别
SET CURRENT ISOLATION = CS;

# 查看锁等待
SELECT
AGENT_ID,
APPL_NAME,
LOCK_OBJECT_TYPE,
LOCK_MODE,
LOCK_STATUS,
TABNAME
FROM TABLE(MON_GET_LOCKS(”, -2)) AS T;

# 查看死锁
SELECT
EVENT_TIMESTAMP,
PARTICIPANT_NO,
LOCK_NAME,
LOCK_MODE,
TABLE_SCHEMA,
TABLE_NAME
FROM TABLE(MON_GET_DEADLOCKS(”, -2)) AS T;

# 主库配置
UPDATE DATABASE CONFIGURATION FOR fgedb USING
HADR_LOCAL_HOST primary.fgedu.net.cn
HADR_LOCAL_SVC 50000
HADR_REMOTE_HOST standby.fgedu.net.cn
HADR_REMOTE_SVC 50000
HADR_REMOTE_INST db2inst1
HADR_SYNCMODE NEARSYNC
HADR_TIMEOUT 120
HADR_PEER_WINDOW 300
LOGINDEXBUILD ON;

# 备库配置
UPDATE DATABASE CONFIGURATION FOR fgedb USING
HADR_LOCAL_HOST standby.fgedu.net.cn
HADR_LOCAL_SVC 50000
HADR_REMOTE_HOST primary.fgedu.net.cn
HADR_REMOTE_SVC 50000
HADR_REMOTE_INST db2inst1
HADR_SYNCMODE NEARSYNC
HADR_TIMEOUT 120
HADR_PEER_WINDOW 300
LOGINDEXBUILD ON;

# 启动HADR
# 备库
START HADR ON DATABASE fgedb AS STANDBY;

# 主库
START HADR ON DATABASE fgedb AS PRIMARY;

# 查看HADR状态
GET SNAPSHOT FOR HADR ON DATABASE fgedb;

# 手动切换
TAKE OVER HADR ON DATABASE fgedb;

# 全量备份脚本
#!/bin/bash
BACKUP_DIR=”/db2/backup/full”
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”$BACKUP_DIR/backup_$DATE.log”

mkdir -p $BACKUP_DIR

echo “开始全量备份: $DATE” > $LOG_FILE

db2 “BACKUP DATABASE fgedb TO $BACKUP_DIR COMPRESS” >> $LOG_FILE 2>&1

if [ $? -eq 0 ]; then
echo “全量备份成功” >> $LOG_FILE
else
echo “全量备份失败” >> $LOG_FILE
fi

# 增量备份
db2 “BACKUP DATABASE fgedb TO $BACKUP_DIR INCREMENTAL COMPRESS”

# 归档日志配置
UPDATE DATABASE CONFIGURATION FOR fgedb USING
LOGARCHMETH1 DISK:/db2/archive_logs
LOGARCHMETH2 OFF
LOGPRIMARY 50
LOGSECOND 100
LOGFILSIZ 1024
SOFTMAX 100;

# 备份保留策略
find $BACKUP_DIR -name “*.001” -mtime +7 -delete