# 订单表设计
CREATE TABLE ORDERS (
ORDER_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32) NOT NULL,
ORDER_STATUS VARCHAR(20) NOT NULL,
TOTAL_AMOUNT DECIMAL(18, 2) NOT NULL,
PAYMENT_STATUS VARCHAR(20) 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_CUSTOMER ON ORDERS(CUSTOMER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_ORDERS_STATUS ON ORDERS(ORDER_STATUS, CREATE_TIME DESC);

# 订单明细表
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);

# 创建订单存储过程
CREATE OR REPLACE PROCEDURE SP_CREATE_ORDER(
IN p_customer_id VARCHAR(32),
IN p_product_list VARCHAR(1000),
OUT p_order_id VARCHAR(32),
OUT p_total_amount DECIMAL(18, 2)
)
LANGUAGE SQL
BEGIN
DECLARE v_order_id VARCHAR(32);
DECLARE v_total_amount DECIMAL(18, 2) DEFAULT 0;

SET v_order_id = ‘O’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISS’) ||
SUBSTR(TO_CHAR(RAND() * 10000), 1, 4);

INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_STATUS, TOTAL_AMOUNT, PAYMENT_STATUS)
VALUES (v_order_id, p_customer_id, ‘PENDING’, 0, ‘UNPAID’);

SET p_order_id = v_order_id;
SET p_total_amount = v_total_amount;
END;

# 库存表设计
CREATE TABLE INVENTORY (
INVENTORY_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
WAREHOUSE_ID VARCHAR(32) NOT NULL,
SKU_ID VARCHAR(32) NOT NULL,
QUANTITY INTEGER NOT NULL,
AVAILABLE_QUANTITY INTEGER NOT NULL,
LOCKED_QUANTITY INTEGER NOT NULL DEFAULT 0,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_INVENTORY PRIMARY KEY (INVENTORY_ID),
CONSTRAINT UK_INVENTORY UNIQUE (WAREHOUSE_ID, SKU_ID)
);

CREATE INDEX IDX_INVENTORY_SKU ON INVENTORY(SKU_ID);

# 库存扣减存储过程
CREATE OR REPLACE PROCEDURE SP_DEDUCT_INVENTORY(
IN p_warehouse_id VARCHAR(32),
IN p_sku_id VARCHAR(32),
IN p_quantity INTEGER,
IN p_order_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_available_quantity INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘库存扣减成功’;

SELECT AVAILABLE_QUANTITY INTO v_available_quantity
FROM INVENTORY
WHERE WAREHOUSE_ID = p_warehouse_id AND SKU_ID = p_sku_id
WITH RS USE AND KEEP UPDATE LOCKS;

IF v_available_quantity < p_quantity THEN SET p_result_code = 'FAIL'; SET p_result_msg = '库存不足'; RETURN; END IF; UPDATE INVENTORY SET AVAILABLE_QUANTITY = AVAILABLE_QUANTITY - p_quantity, LOCKED_QUANTITY = LOCKED_QUANTITY + p_quantity, UPDATE_TIME = CURRENT TIMESTAMP WHERE WAREHOUSE_ID = p_warehouse_id AND SKU_ID = p_sku_id; INSERT INTO INVENTORY_LOG ( WAREHOUSE_ID, SKU_ID, ORDER_ID, LOG_TYPE, QUANTITY_CHANGE, QUANTITY_AFTER ) VALUES ( p_warehouse_id, p_sku_id, p_order_id, 'DEDUCT', -p_quantity, v_available_quantity - p_quantity ); END;

# 账户表设计
CREATE TABLE ACCOUNT (
ACCOUNT_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32) NOT NULL,
ACCOUNT_TYPE VARCHAR(20) NOT NULL,
BALANCE DECIMAL(18, 2) NOT NULL DEFAULT 0,
FROZEN_AMOUNT DECIMAL(18, 2) NOT NULL DEFAULT 0,
ACCOUNT_STATUS VARCHAR(20) NOT NULL DEFAULT ‘ACTIVE’,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ACCOUNT PRIMARY KEY (ACCOUNT_ID)
);

CREATE INDEX IDX_ACCOUNT_CUSTOMER ON ACCOUNT(CUSTOMER_ID);

# 交易流水表
CREATE TABLE TRANSACTION_LOG (
TRANSACTION_ID VARCHAR(32) NOT NULL,
FROM_ACCOUNT VARCHAR(32),
TO_ACCOUNT VARCHAR(32),
TRANSACTION_TYPE VARCHAR(20) NOT NULL,
AMOUNT DECIMAL(18, 2) NOT NULL,
BALANCE_BEFORE DECIMAL(18, 2) NOT NULL,
BALANCE_AFTER DECIMAL(18, 2) NOT NULL,
TRANSACTION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TRANSACTION_LOG PRIMARY KEY (TRANSACTION_ID)
) PARTITION BY RANGE (TRANSACTION_TIME)
(
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’
);

CREATE INDEX IDX_TRANSACTION_ACCOUNT ON TRANSACTION_LOG(FROM_ACCOUNT, TRANSACTION_TIME DESC);
CREATE INDEX IDX_TRANSACTION_TO_ACCOUNT ON TRANSACTION_LOG(TO_ACCOUNT, TRANSACTION_TIME DESC);

# 转账存储过程
CREATE OR REPLACE PROCEDURE SP_TRANSFER(
IN p_from_account VARCHAR(32),
IN p_to_account VARCHAR(32),
IN p_amount DECIMAL(18, 2),
OUT p_transaction_id VARCHAR(32),
OUT p_result_code VARCHAR(10)
)
LANGUAGE SQL
BEGIN
DECLARE v_from_balance DECIMAL(18, 2);
DECLARE v_to_balance DECIMAL(18, 2);

SET p_result_code = ‘SUCCESS’;
SET p_transaction_id = ‘T’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISS’) ||
SUBSTR(TO_CHAR(RAND() * 10000), 1, 4);

START TRANSACTION;

SELECT BALANCE INTO v_from_balance
FROM ACCOUNT
WHERE ACCOUNT_ID = p_from_account
WITH RS USE AND KEEP UPDATE LOCKS;

IF v_from_balance < p_amount THEN SET p_result_code = 'FAIL'; ROLLBACK; RETURN; END IF; UPDATE ACCOUNT SET BALANCE = BALANCE - p_amount, UPDATE_TIME = CURRENT TIMESTAMP WHERE ACCOUNT_ID = p_from_account; UPDATE ACCOUNT SET BALANCE = BALANCE + p_amount, UPDATE_TIME = CURRENT TIMESTAMP WHERE ACCOUNT_ID = p_to_account; INSERT INTO TRANSACTION_LOG ( TRANSACTION_ID, FROM_ACCOUNT, TO_ACCOUNT, TRANSACTION_TYPE, AMOUNT, BALANCE_BEFORE, BALANCE_AFTER ) VALUES ( p_transaction_id, p_from_account, p_to_account, 'TRANSFER', p_amount, v_from_balance, v_from_balance - p_amount ); COMMIT; END;

# 1. 使用分区表
CREATE TABLE LARGE_TABLE (
ID BIGINT NOT NULL,
DATA_DATE DATE NOT NULL,
DATA_VALUE VARCHAR(500),
CONSTRAINT PK_LARGE_TABLE PRIMARY KEY (ID, DATA_DATE)
) PARTITION BY RANGE (DATA_DATE)
(
STARTING ‘2026-01-01’ ENDING ‘2026-12-31’ EVERY 1 MONTH
);

# 2. 使用MQT预计算
CREATE TABLE MQT_DAILY_SUMMARY AS (
SELECT
DATA_DATE,
COUNT(*) AS RECORD_COUNT,
SUM(CAST(DATA_VALUE AS DECIMAL(18,2))) AS TOTAL_VALUE
FROM LARGE_TABLE
GROUP BY DATA_DATE
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE MQT_DAILY_SUMMARY;

# 3. 使用索引优化查询
CREATE INDEX IDX_LARGE_TABLE_DATE ON LARGE_TABLE(DATA_DATE) INCLUDE (DATA_VALUE);

# 4. 使用存储过程封装业务逻辑
CREATE OR REPLACE PROCEDURE SP_PROCESS_ORDER(
IN p_order_id VARCHAR(32)
)
LANGUAGE SQL
BEGIN
DECLARE v_status VARCHAR(20);

SELECT ORDER_STATUS INTO v_status
FROM ORDERS
WHERE ORDER_ID = p_order_id;

IF v_status = ‘PENDING’ THEN
UPDATE ORDERS
SET ORDER_STATUS = ‘PROCESSING’,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE ORDER_ID = p_order_id;

CALL SP_SEND_NOTIFICATION(p_order_id, ‘ORDER_PROCESSING’);
END IF;
END;

# 1. 配置HADR
UPDATE DATABASE CONFIGURATION FOR FGEDB USING
HADR_LOCAL_HOST 192.168.1.10
HADR_LOCAL_SVC 55001
HADR_REMOTE_HOST 192.168.1.11
HADR_REMOTE_SVC 55002
HADR_SYNCMODE SYNC;

START HADR ON DATABASE FGEDB AS PRIMARY;

# 2. 配置自动备份
#!/bin/bash
# auto_backup.sh

BACKUP_DIR=/db2backup
DATE=$(date +%Y%m%d)

db2 connect to FGEDB
db2 backup database FGEDB to $BACKUP_DIR/$DATE
db2 connect reset

# 保留最近7天备份
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

# 3. 配置监控告警
#!/bin/bash
# monitor_db2.sh

db2 connect to FGEDB

# 检查表空间使用率
db2 “SELECT tbsp_name, round(tbsp_used_pages*100.0/tbsp_total_pages,2)
FROM sysibmadm.tbsp_utilization
WHERE tbsp_used_pages*100.0/tbsp_total_pages > 80” > /tmp/tablespace_alert.txt

if [ -s /tmp/tablespace_alert.txt ]; then
mail -s “DB2 Tablespace Alert” admin@example.com < /tmp/tablespace_alert.txt fi db2 connect reset