# 对账主表
CREATE TABLE RECONCILIATION_MASTER (
RECON_ID VARCHAR(32) NOT NULL,
RECON_DATE DATE NOT NULL,
RECON_TYPE VARCHAR(20) NOT NULL,
TOTAL_COUNT INTEGER NOT NULL,
SUCCESS_COUNT INTEGER NOT NULL,
FAIL_COUNT INTEGER NOT NULL,
RECON_STATUS VARCHAR(20) NOT NULL,
START_TIME TIMESTAMP NOT NULL,
END_TIME TIMESTAMP,
CONSTRAINT PK_RECONCILIATION_MASTER PRIMARY KEY (RECON_ID)
);

CREATE INDEX IDX_RECON_DATE ON RECONCILIATION_MASTER(RECON_DATE, RECON_TYPE);

# 对账明细表
CREATE TABLE RECONCILIATION_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
RECON_ID VARCHAR(32) NOT NULL,
TRANSACTION_ID VARCHAR(32) NOT NULL,
INTERNAL_AMOUNT DECIMAL(18, 2) NOT NULL,
EXTERNAL_AMOUNT DECIMAL(18, 2),
DIFF_AMOUNT DECIMAL(18, 2),
RECON_RESULT VARCHAR(20) NOT NULL,
ERROR_CODE VARCHAR(20),
ERROR_DESC VARCHAR(500),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_RECONCILIATION_DETAIL PRIMARY KEY (DETAIL_ID)
);

CREATE INDEX IDX_RECON_TRANSACTION ON RECONCILIATION_DETAIL(TRANSACTION_ID);
CREATE INDEX IDX_RECON_RESULT ON RECONCILIATION_DETAIL(RECON_ID, RECON_RESULT);

# 对账差异表
CREATE TABLE RECONCILIATION_DIFF (
DIFF_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
RECON_ID VARCHAR(32) NOT NULL,
TRANSACTION_ID VARCHAR(32) NOT NULL,
DIFF_TYPE VARCHAR(20) NOT NULL,
DIFF_AMOUNT DECIMAL(18, 2) NOT NULL,
INTERNAL_DATA VARCHAR(1000),
EXTERNAL_DATA VARCHAR(1000),
HANDLE_STATUS VARCHAR(20) NOT NULL DEFAULT ‘PENDING’,
HANDLE_TIME TIMESTAMP,
HANDLE_USER VARCHAR(50),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_RECONCILIATION_DIFF PRIMARY KEY (DIFF_ID)
);

CREATE INDEX IDX_DIFF_STATUS ON RECONCILIATION_DIFF(HANDLE_STATUS, CREATE_TIME);

# 内部交易流水表
CREATE TABLE INTERNAL_TRANSACTION (
TRANS_ID VARCHAR(32) NOT NULL,
ACCOUNT_ID VARCHAR(32) NOT NULL,
TRANS_TYPE VARCHAR(20) NOT NULL,
TRANS_AMOUNT DECIMAL(18, 2) NOT NULL,
BALANCE_BEFORE DECIMAL(18, 2) NOT NULL,
BALANCE_AFTER DECIMAL(18, 2) NOT NULL,
TRANS_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
TRANS_STATUS VARCHAR(20) NOT NULL,
CONSTRAINT PK_INTERNAL_TRANSACTION PRIMARY KEY (TRANS_ID)
) PARTITION BY RANGE (TRANS_TIME)
(
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’
);

CREATE INDEX IDX_TRANS_ACCOUNT ON INTERNAL_TRANSACTION(ACCOUNT_ID, TRANS_TIME DESC);

# 外部交易流水表
CREATE TABLE EXTERNAL_TRANSACTION (
TRANS_ID VARCHAR(32) NOT NULL,
EXTERNAL_TRANS_ID VARCHAR(32),
ACCOUNT_ID VARCHAR(32) NOT NULL,
TRANS_TYPE VARCHAR(20) NOT NULL,
TRANS_AMOUNT DECIMAL(18, 2) NOT NULL,
TRANS_TIME TIMESTAMP NOT NULL,
TRANS_STATUS VARCHAR(20) NOT NULL,
CONSTRAINT PK_EXTERNAL_TRANSACTION PRIMARY KEY (TRANS_ID)
);

CREATE INDEX IDX_EXTERNAL_TRANS_TIME ON EXTERNAL_TRANSACTION(TRANS_TIME);

# 对账主存储过程
CREATE OR REPLACE PROCEDURE SP_RECONCILIATION(
IN p_recon_date DATE,
IN p_recon_type VARCHAR(20),
OUT p_recon_id VARCHAR(32),
OUT p_result_code VARCHAR(10)
)
LANGUAGE SQL
BEGIN
DECLARE v_total_count INTEGER DEFAULT 0;
DECLARE v_success_count INTEGER DEFAULT 0;
DECLARE v_fail_count INTEGER DEFAULT 0;

SET p_result_code = ‘SUCCESS’;
SET p_recon_id = ‘R’ || TO_CHAR(p_recon_date, ‘YYYYMMDD’) ||
SUBSTR(TO_CHAR(RAND() * 10000), 1, 4);

— 创建对账主记录
INSERT INTO RECONCILIATION_MASTER (
RECON_ID, RECON_DATE, RECON_TYPE,
TOTAL_COUNT, SUCCESS_COUNT, FAIL_COUNT,
RECON_STATUS, START_TIME
) VALUES (
p_recon_id, p_recon_date, p_recon_type,
0, 0, 0, ‘PROCESSING’, CURRENT TIMESTAMP
);

— 执行对账
INSERT INTO RECONCILIATION_DETAIL (
RECON_ID, TRANSACTION_ID, INTERNAL_AMOUNT,
EXTERNAL_AMOUNT, DIFF_AMOUNT, RECON_RESULT
)
SELECT
p_recon_id,
I.TRANS_ID,
I.TRANS_AMOUNT,
E.TRANS_AMOUNT,
I.TRANS_AMOUNT – E.TRANS_AMOUNT,
CASE
WHEN I.TRANS_AMOUNT = E.TRANS_AMOUNT THEN ‘SUCCESS’
ELSE ‘FAIL’
END
FROM INTERNAL_TRANSACTION I
LEFT JOIN EXTERNAL_TRANSACTION E ON I.TRANS_ID = E.EXTERNAL_TRANS_ID
WHERE DATE(I.TRANS_TIME) = p_recon_date;

— 统计对账结果
SELECT
COUNT(*),
SUM(CASE WHEN RECON_RESULT = ‘SUCCESS’ THEN 1 ELSE 0 END),
SUM(CASE WHEN RECON_RESULT = ‘FAIL’ THEN 1 ELSE 0 END)
INTO v_total_count, v_success_count, v_fail_count
FROM RECONCILIATION_DETAIL
WHERE RECON_ID = p_recon_id;

— 更新对账主记录
UPDATE RECONCILIATION_MASTER
SET TOTAL_COUNT = v_total_count,
SUCCESS_COUNT = v_success_count,
FAIL_COUNT = v_fail_count,
RECON_STATUS = ‘COMPLETED’,
END_TIME = CURRENT TIMESTAMP
WHERE RECON_ID = p_recon_id;

— 插入差异记录
INSERT INTO RECONCILIATION_DIFF (
RECON_ID, TRANSACTION_ID, DIFF_TYPE, DIFF_AMOUNT,
INTERNAL_DATA, EXTERNAL_DATA
)
SELECT
RECON_ID,
TRANSACTION_ID,
‘AMOUNT_DIFF’,
DIFF_AMOUNT,
CAST(INTERNAL_AMOUNT AS VARCHAR(100)),
CAST(EXTERNAL_AMOUNT AS VARCHAR(100))
FROM RECONCILIATION_DETAIL
WHERE RECON_ID = p_recon_id
AND RECON_RESULT = ‘FAIL’;
END;

#!/bin/bash
# reconciliation.sh

DBNAME=FGEDB
RECON_DATE=$1
RECON_TYPE=$2

if [ -z “$RECON_DATE” ]; then
RECON_DATE=$(date -d “yesterday” +%Y-%m-%d)
fi

if [ -z “$RECON_TYPE” ]; then
RECON_TYPE=”DAILY”
fi

echo “=== Reconciliation Process ===”
echo “Date: $(date)”
echo “Recon Date: $RECON_DATE”
echo “Recon Type: $RECON_TYPE”

db2 connect to $DBNAME

# 执行对账
db2 “CALL SP_RECONCILIATION(‘$RECON_DATE’, ‘$RECON_TYPE’, ?, ?)”

# 查询对账结果
db2 “SELECT RECON_ID, TOTAL_COUNT, SUCCESS_COUNT, FAIL_COUNT, RECON_STATUS
FROM RECONCILIATION_MASTER
WHERE RECON_DATE = ‘$RECON_DATE’ AND RECON_TYPE = ‘$RECON_TYPE'”

# 查询差异记录
db2 “SELECT COUNT(*) AS DIFF_COUNT
FROM RECONCILIATION_DIFF
WHERE RECON_ID IN (
SELECT RECON_ID FROM RECONCILIATION_MASTER
WHERE RECON_DATE = ‘$RECON_DATE’ AND RECON_TYPE = ‘$RECON_TYPE’
) AND HANDLE_STATUS = ‘PENDING'”

db2 connect reset

echo “=== Reconciliation Complete ===”

# 差异处理存储过程
CREATE OR REPLACE PROCEDURE SP_HANDLE_DIFF(
IN p_diff_id BIGINT,
IN p_handle_status VARCHAR(20),
IN p_handle_user VARCHAR(50),
OUT p_result_code VARCHAR(10)
)
LANGUAGE SQL
BEGIN
SET p_result_code = ‘SUCCESS’;

— 更新差异状态
UPDATE RECONCILIATION_DIFF
SET HANDLE_STATUS = p_handle_status,
HANDLE_TIME = CURRENT TIMESTAMP,
HANDLE_USER = p_handle_user
WHERE DIFF_ID = p_diff_id;

— 记录处理日志
INSERT INTO RECONCILIATION_LOG (
DIFF_ID, HANDLE_STATUS, HANDLE_USER, HANDLE_TIME
) VALUES (
p_diff_id, p_handle_status, p_handle_user, CURRENT TIMESTAMP
);
END;

# 差异处理脚本
#!/bin/bash
# handle_diff.sh

DBNAME=FGEDB
DIFF_ID=$1
HANDLE_STATUS=$2
HANDLE_USER=$3

if [ -z “$DIFF_ID” ] || [ -z “$HANDLE_STATUS” ] || [ -z “$HANDLE_USER” ]; then
echo “Usage: $0 ”
exit 1
fi

db2 connect to $DBNAME

db2 “CALL SP_HANDLE_DIFF($DIFF_ID, ‘$HANDLE_STATUS’, ‘$HANDLE_USER’, ?)”

db2 connect reset

echo “Diff handled successfully”

# 自动调账存储过程
CREATE OR REPLACE PROCEDURE SP_AUTO_ADJUST(
IN p_diff_id BIGINT,
OUT p_result_code VARCHAR(10)
)
LANGUAGE SQL
BEGIN
DECLARE v_transaction_id VARCHAR(32);
DECLARE v_diff_amount DECIMAL(18, 2);
DECLARE v_account_id VARCHAR(32);

SET p_result_code = ‘SUCCESS’;

— 获取差异信息
SELECT TRANSACTION_ID, DIFF_AMOUNT
INTO v_transaction_id, v_diff_amount
FROM RECONCILIATION_DIFF
WHERE DIFF_ID = p_diff_id;

— 获取账户信息
SELECT ACCOUNT_ID INTO v_account_id
FROM INTERNAL_TRANSACTION
WHERE TRANS_ID = v_transaction_id;

— 执行调账
INSERT INTO ADJUSTMENT_LOG (
ADJUSTMENT_ID, ACCOUNT_ID, TRANSACTION_ID,
ADJUSTMENT_AMOUNT, ADJUSTMENT_TIME
) VALUES (
‘A’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISS’),
v_account_id, v_transaction_id,
v_diff_amount, CURRENT TIMESTAMP
);

— 更新差异状态
UPDATE RECONCILIATION_DIFF
SET HANDLE_STATUS = ‘AUTO_ADJUSTED’,
HANDLE_TIME = CURRENT TIMESTAMP,
HANDLE_USER = ‘SYSTEM’
WHERE DIFF_ID = p_diff_id;
END;