# 客户主表
CREATE TABLE CUSTOMER (
CUST_ID VARCHAR(32) NOT NULL,
CUST_NAME VARCHAR(100) NOT NULL,
ID_TYPE VARCHAR(10) NOT NULL,
ID_NO VARCHAR(50) NOT NULL,
CUST_LEVEL VARCHAR(10) NOT NULL,
CUST_STATUS VARCHAR(10) NOT NULL,
REGION_ID VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUST_ID)
) ORGANIZE BY ROW;

# 账户表
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,
BALANCE DECIMAL(18, 2) NOT NULL DEFAULT 0,
CREDIT_LIMIT DECIMAL(18, 2) NOT NULL DEFAULT 0,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ACCOUNT PRIMARY KEY (ACCT_ID)
) ORGANIZE BY ROW;

# 用户产品订购表
CREATE TABLE SUBSCRIPTION (
SUB_ID VARCHAR(32) NOT NULL,
CUST_ID VARCHAR(32) NOT NULL,
ACCT_ID VARCHAR(32) NOT NULL,
PROD_ID VARCHAR(32) NOT NULL,
MSISDN VARCHAR(20) NOT NULL,
SUB_STATUS VARCHAR(10) NOT NULL,
EFF_DATE DATE NOT NULL,
EXP_DATE DATE,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SUBSCRIPTION PRIMARY KEY (SUB_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_SUBSCRIPTION_MSISDN ON SUBSCRIPTION(MSISDN) CLUSTER;
CREATE INDEX IDX_SUBSCRIPTION_CUST ON SUBSCRIPTION(CUST_ID, SUB_STATUS);
CREATE INDEX IDX_SUBSCRIPTION_PROD ON SUBSCRIPTION(PROD_ID);

# 通话详单表 – 按时间分区
CREATE TABLE CDR_VOICE (
CDR_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
MSISDN VARCHAR(20) NOT NULL,
OTHER_PARTY VARCHAR(20),
CALL_TYPE VARCHAR(10) NOT NULL,
CALL_TIME TIMESTAMP NOT NULL,
DURATION INTEGER NOT NULL,
ROAMING_TYPE VARCHAR(10),
CHARGE DECIMAL(10, 2) NOT NULL,
REGION_ID VARCHAR(10) NOT NULL,
FILE_NAME VARCHAR(100),
RECORD_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CDR_VOICE PRIMARY KEY (CDR_ID)
) PARTITION BY RANGE (CALL_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 DAY
);

# 创建索引
CREATE INDEX IDX_CDR_VOICE_MSISDN ON CDR_VOICE(MSISDN, CALL_TIME DESC) PARTITIONED;
CREATE INDEX IDX_CDR_VOICE_TIME ON CDR_VOICE(CALL_TIME) PARTITIONED;
CREATE INDEX IDX_CDR_VOICE_REGION ON CDR_VOICE(REGION_ID, CALL_TIME) PARTITIONED;

# 短信详单表
CREATE TABLE CDR_SMS (
CDR_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
MSISDN VARCHAR(20) NOT NULL,
OTHER_PARTY VARCHAR(20),
SMS_TYPE VARCHAR(10) NOT NULL,
SEND_TIME TIMESTAMP NOT NULL,
CHARGE DECIMAL(10, 2) NOT NULL,
REGION_ID VARCHAR(10) NOT NULL,
FILE_NAME VARCHAR(100),
RECORD_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CDR_SMS PRIMARY KEY (CDR_ID)
) PARTITION BY RANGE (SEND_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 DAY
);

# 流量详单表
CREATE TABLE CDR_DATA (
CDR_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
MSISDN VARCHAR(20) NOT NULL,
SESSION_ID VARCHAR(50),
START_TIME TIMESTAMP NOT NULL,
END_TIME TIMESTAMP NOT NULL,
DATA_UP BIGINT NOT NULL,
DATA_DOWN BIGINT NOT NULL,
TOTAL_DATA BIGINT NOT NULL GENERATED ALWAYS AS (DATA_UP + DATA_DOWN),
CHARGE DECIMAL(10, 2) NOT NULL,
REGION_ID VARCHAR(10) NOT NULL,
FILE_NAME VARCHAR(100),
RECORD_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CDR_DATA PRIMARY KEY (CDR_ID)
) PARTITION BY RANGE (START_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 DAY
);

# 账单主表
CREATE TABLE BILL (
BILL_ID VARCHAR(32) NOT NULL,
ACCT_ID VARCHAR(32) NOT NULL,
CUST_ID VARCHAR(32) NOT NULL,
BILL_CYCLE VARCHAR(6) NOT NULL,
TOTAL_CHARGE DECIMAL(18, 2) NOT NULL,
DISCOUNT_AMOUNT DECIMAL(18, 2) NOT NULL DEFAULT 0,
ACTUAL_CHARGE DECIMAL(18, 2) NOT NULL,
PAID_AMOUNT DECIMAL(18, 2) NOT NULL DEFAULT 0,
BILL_STATUS VARCHAR(10) NOT NULL,
BILL_DATE DATE NOT NULL,
DUE_DATE DATE NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_BILL PRIMARY KEY (BILL_ID)
) PARTITION BY RANGE (BILL_CYCLE)
(
STARTING ‘202601’ ENDING ‘202612’ EVERY 1 MONTH
);

# 账单明细表
CREATE TABLE BILL_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
BILL_ID VARCHAR(32) NOT NULL,
CHARGE_TYPE VARCHAR(20) NOT NULL,
CHARGE_NAME VARCHAR(100) NOT NULL,
CHARGE_AMOUNT DECIMAL(18, 2) NOT NULL,
DISCOUNT_AMOUNT DECIMAL(18, 2) NOT NULL DEFAULT 0,
ACTUAL_AMOUNT DECIMAL(18, 2) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_BILL_DETAIL PRIMARY KEY (DETAIL_ID)
) PARTITION BY REFERENCE (FK_BILL_DETAIL_BILL);

# 创建索引
CREATE INDEX IDX_BILL_ACCT_CYCLE ON BILL(ACCT_ID, BILL_CYCLE) PARTITIONED;
CREATE INDEX IDX_BILL_CUST_CYCLE ON BILL(CUST_ID, BILL_CYCLE) PARTITIONED;
CREATE INDEX IDX_BILL_STATUS ON BILL(BILL_STATUS, BILL_CYCLE) PARTITIONED;

# 查看分区信息
SELECT
TABSCHEMA,
TABNAME,
DATAPARTITIONNAME,
DATAPARTITIONID,
LOWVALUE,
HIGHVALUE
FROM SYSCAT.DATAPARTITIONS
WHERE TABNAME = ‘CDR_VOICE’
ORDER BY DATAPARTITIONID;

# 添加新分区
ALTER TABLE CDR_VOICE ADD PARTITION
STARTING ‘2027-01-01-00.00.00.000000’
ENDING ‘2027-01-31-23.59.59.999999’;

# 分离旧分区
ALTER TABLE CDR_VOICE DETACH PARTITION PART0 INTO TABLE CDR_VOICE_ARCHIVE_PART0;

# 附加分区
ALTER TABLE CDR_VOICE ATTACH PARTITION
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-01-31-23.59.59.999999’
FROM TABLE CDR_VOICE_ARCHIVE_PART0;

# 删除旧分区
ALTER TABLE CDR_VOICE DROP PARTITION PART0;

# 重组表
REORG TABLE CDR_VOICE;
RUNSTATS ON TABLE CDR_VOICE AND INDEXES ALL;

# 创建MDC表
CREATE TABLE CDR_VOICE_MDC (
CDR_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
MSISDN VARCHAR(20) NOT NULL,
CALL_TYPE VARCHAR(10) NOT NULL,
CALL_DATE DATE NOT NULL,
CALL_TIME TIMESTAMP NOT NULL,
DURATION INTEGER NOT NULL,
CHARGE DECIMAL(10, 2) NOT NULL,
REGION_ID VARCHAR(10) NOT NULL,
CONSTRAINT PK_CDR_VOICE_MDC PRIMARY KEY (CDR_ID)
) ORGANIZE BY DIMENSIONS (
(MSISDN),
(CALL_DATE),
(REGION_ID)
);

# 查看MDC信息
SELECT
TABSCHEMA,
TABNAME,
COLNAME,
DIMENSION
FROM SYSCAT.MDCDIMENSIONS
WHERE TABNAME = ‘CDR_VOICE_MDC’;

# 分析MDC块使用情况
SELECT
TABSCHEMA,
TABNAME,
TOTAL_BLOCKS,
EMPTY_BLOCKS,
USED_BLOCKS,
BLOCK_INDEX_ENTRIES
FROM TABLE(MON_GET_MDC_BLOCK_STATS(NULL, NULL, -2)) AS T;

# 使用LOAD导入话单
LOAD FROM /data/cdr/voice_20260408.del OF DEL
MODIFIED BY COLDEL, CHARDEL” DATEFORMAT=”YYYY-MM-DD” TIMEFORMAT=”HH:MM:SS”
INSERT INTO CDR_VOICE (MSISDN, OTHER_PARTY, CALL_TYPE, CALL_TIME, DURATION, ROAMING_TYPE, CHARGE, REGION_ID, FILE_NAME)
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
NONRECOVERABLE;

# 使用IMPORT导入
IMPORT FROM /data/cdr/voice_20260408.del OF DEL
MODIFIED BY COLDEL, CHARDEL”
COMMITCOUNT 10000
INSERT INTO CDR_VOICE;

# db2move批量导入
db2move fgedb load -sn fgedu -tn CDR_VOICE -l /data/load -u db2inst1 -p password;

# 使用存储过程批量插入
CREATE OR REPLACE PROCEDURE SP_BATCH_INSERT_CDR(
IN p_start_date DATE,
IN p_end_date DATE,
OUT p_total_count INTEGER,
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_count INTEGER DEFAULT 0;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘批量插入成功’;

INSERT INTO CDR_VOICE (MSISDN, OTHER_PARTY, CALL_TYPE, CALL_TIME, DURATION, CHARGE, REGION_ID)
SELECT
MSISDN,
OTHER_PARTY,
CALL_TYPE,
CALL_TIME,
DURATION,
CHARGE,
REGION_ID
FROM CDR_VOICE_STAGING
WHERE CALL_DATE BETWEEN p_start_date AND p_end_date;

GET DIAGNOSTICS v_count = ROW_COUNT;
SET p_total_count = v_count;

END;

# 计费存储过程
CREATE OR REPLACE PROCEDURE SP_RATING(
IN p_bill_cycle VARCHAR(6),
OUT p_total_calls INTEGER,
OUT p_total_charge DECIMAL(18, 2),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_start_date DATE;
DECLARE v_end_date DATE;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘计费成功’;

SET v_start_date = TO_DATE(p_bill_cycle || ’01’, ‘YYYYMMDD’);
SET v_end_date = LAST_DAY(v_start_date);

INSERT INTO BILL_DETAIL (BILL_ID, CHARGE_TYPE, CHARGE_NAME, CHARGE_AMOUNT, ACTUAL_AMOUNT)
SELECT
b.BILL_ID,
‘VOICE’,
‘通话费用’,
SUM(c.CHARGE),
SUM(c.CHARGE)
FROM CDR_VOICE c
INNER JOIN SUBSCRIPTION s ON c.MSISDN = s.MSISDN
INNER JOIN ACCOUNT a ON s.ACCT_ID = a.ACCT_ID
INNER JOIN BILL b ON a.ACCT_ID = b.ACCT_ID AND b.BILL_CYCLE = p_bill_cycle
WHERE c.CALL_TIME BETWEEN v_start_date AND (v_end_date + 1 DAY – 1 MICROSECOND)
GROUP BY b.BILL_ID;

GET DIAGNOSTICS p_total_calls = ROW_COUNT;

SELECT SUM(CHARGE_AMOUNT) INTO p_total_charge
FROM BILL_DETAIL
WHERE BILL_ID IN (
SELECT BILL_ID FROM BILL WHERE BILL_CYCLE = p_bill_cycle
);

END;

# 调用计费存储过程
CALL SP_RATING(‘202604’, ?, ?, ?, ?);

# 数据库配置优化
UPDATE DATABASE CONFIGURATION FOR fgedb USING
DATABASE_MEMORY AUTOMATIC
SELF_TUNING_MEM ON
BUFFPAGE -1
PCKCACHESZ -1
CATALOGCACHE_SZ -1
LOGBUFSZ 1024
LOGFILSIZ 2048
LOGPRIMARY 20
LOGSECOND 100
SOFTMAX 100
CHNGPGS_THRESH 30
MINCOMMIT 1
LOCKLIST 20000
MAXLOCKS 50
LOCKTIMEOUT 60
DLCHKTIME 5000;

# DB2注册表变量
db2set DB2_PARALLEL_IO=*
db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON
db2set DB2_SKIPINSERTED=ON
db2set DB2_EVALUNCOMMITTED=ON
db2set DB2_SKIPDELETED=ON
db2set DB2_INLIST_TO_NLJN=YES
db2set DB2_REDUCED_OPTIMIZATION=YES;

# LOAD优化
db2set DB2_LOAD_COPY_NO_OVERRIDE=COPY YES
db2set DB2_LOAD_READ_AHEAD=AUTOMATIC
db2set DB2_SORT_HEAP_SIZE=AUTOMATIC;