风哥教程参考DB2官方文档Manufacturing Solutions、Time Series Data等内容，详细介绍制造系统库在DB2上的设计、生产计划、物料管理、质量追溯。更多视频教程www.fgedu.net.cn
目录大纲
Part01-制造系统特点
1.1 制造业务特点
制造系统具有以下特点：
- 数据量大：物料、生产、质检数据量大
- 时序数据：设备数据、生产数据按时间序列
- 追溯要求：产品全生命周期追溯
- 复杂关联：BOM、工艺、生产多层关联
- 实时性：设备监控、生产调度要求实时
- 分析需求：产能分析、质量分析、成本分析
Part02-核心表结构设计
2.1 物料表设计
CREATE TABLE MATERIAL (
MATERIAL_ID VARCHAR(32) NOT NULL,
MATERIAL_CODE VARCHAR(50) NOT NULL,
MATERIAL_NAME VARCHAR(200) NOT NULL,
MATERIAL_TYPE VARCHAR(20) NOT NULL,
MATERIAL_SPEC VARCHAR(200),
UNIT VARCHAR(20),
MATERIAL_GROUP VARCHAR(50),
SAFETY_STOCK DECIMAL(10, 2),
MIN_ORDER_QTY DECIMAL(10, 2),
LEAD_TIME INTEGER,
MATERIAL_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MATERIAL PRIMARY KEY (MATERIAL_ID)
) ORGANIZE BY ROW;
# BOM表
CREATE TABLE BOM (
BOM_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
BOM_VERSION VARCHAR(20),
BOM_STATUS VARCHAR(10) NOT NULL,
EFFECTIVE_DATE DATE,
EXPIRY_DATE DATE,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_BOM PRIMARY KEY (BOM_ID)
) ORGANIZE BY ROW;
# BOM明细表
CREATE TABLE BOM_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
BOM_ID VARCHAR(32) NOT NULL,
MATERIAL_ID VARCHAR(32) NOT NULL,
QUANTITY DECIMAL(10, 4) NOT NULL,
UNIT VARCHAR(20),
SORT_ORDER INTEGER,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_BOM_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE UNIQUE INDEX IDX_MATERIAL_CODE ON MATERIAL(MATERIAL_CODE);
CREATE INDEX IDX_MATERIAL_TYPE ON MATERIAL(MATERIAL_TYPE, MATERIAL_STATUS);
CREATE INDEX IDX_BOM_PRODUCT ON BOM(PRODUCT_ID, BOM_STATUS);
CREATE INDEX IDX_BOM_DETAIL_BOM ON BOM_DETAIL(BOM_ID);
2.2 生产订单表
CREATE TABLE PRODUCTION_ORDER (
ORDER_ID VARCHAR(32) NOT NULL,
ORDER_NO VARCHAR(50) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
BOM_ID VARCHAR(32),
PLANNED_QTY DECIMAL(10, 2) NOT NULL,
ACTUAL_QTY DECIMAL(10, 2) DEFAULT 0,
START_DATE DATE,
END_DATE DATE,
ACTUAL_START_DATE DATE,
ACTUAL_END_DATE DATE,
WORKSHOP_ID VARCHAR(20),
PRODUCTION_LINE VARCHAR(50),
ORDER_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCTION_ORDER PRIMARY KEY (ORDER_ID)
) ORGANIZE BY ROW;
# 生产订单明细表
CREATE TABLE PRODUCTION_ORDER_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
ORDER_ID VARCHAR(32) NOT NULL,
MATERIAL_ID VARCHAR(32) NOT NULL,
PLANNED_QTY DECIMAL(10, 2) NOT NULL,
ACTUAL_QTY DECIMAL(10, 2) DEFAULT 0,
WASTAGE_QTY DECIMAL(10, 2) DEFAULT 0,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCTION_ORDER_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE UNIQUE INDEX IDX_PRODUCTION_ORDER_NO ON PRODUCTION_ORDER(ORDER_NO);
CREATE INDEX IDX_PRODUCTION_ORDER_PRODUCT ON PRODUCTION_ORDER(PRODUCT_ID, ORDER_STATUS);
CREATE INDEX IDX_PRODUCTION_ORDER_DATE ON PRODUCTION_ORDER(START_DATE, END_DATE);
CREATE INDEX IDX_PRODUCTION_ORDER_DETAIL_ORDER ON PRODUCTION_ORDER_DETAIL(ORDER_ID);
Part03-生产计划与排程
3.1 生产计划
CREATE TABLE PRODUCTION_PLAN (
PLAN_ID VARCHAR(32) NOT NULL,
PLAN_NO VARCHAR(50) NOT NULL,
PLAN_TYPE VARCHAR(20) NOT NULL,
PLAN_DATE DATE NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
PLANNED_QTY DECIMAL(10, 2) NOT NULL,
WORKSHOP_ID VARCHAR(20),
PLAN_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCTION_PLAN PRIMARY KEY (PLAN_ID)
) ORGANIZE BY ROW;
# 创建设备表
CREATE TABLE EQUIPMENT (
EQUIPMENT_ID VARCHAR(32) NOT NULL,
EQUIPMENT_CODE VARCHAR(50) NOT NULL,
EQUIPMENT_NAME VARCHAR(100) NOT NULL,
EQUIPMENT_TYPE VARCHAR(20),
WORKSHOP_ID VARCHAR(20),
PRODUCTION_LINE VARCHAR(50),
EQUIPMENT_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_EQUIPMENT PRIMARY KEY (EQUIPMENT_ID)
) ORGANIZE BY ROW;
# 设备状态记录表
CREATE TABLE EQUIPMENT_STATUS_LOG (
LOG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
EQUIPMENT_ID VARCHAR(32) NOT NULL,
STATUS VARCHAR(20) NOT NULL,
STATUS_DESC VARCHAR(200),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_EQUIPMENT_STATUS_LOG PRIMARY KEY (LOG_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 DAY
);
# 创建索引
CREATE INDEX IDX_EQUIPMENT_WORKSHOP ON EQUIPMENT(WORKSHOP_ID, EQUIPMENT_STATUS);
CREATE INDEX IDX_EQUIPMENT_STATUS_LOG_EQUIPMENT ON EQUIPMENT_STATUS_LOG(EQUIPMENT_ID, CREATE_TIME DESC) PARTITIONED;
Part04-物料管理与追溯
4.1 库存管理
CREATE TABLE MATERIAL_INVENTORY (
INVENTORY_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
WAREHOUSE_ID VARCHAR(20) NOT NULL,
MATERIAL_ID VARCHAR(32) NOT NULL,
QUANTITY DECIMAL(10, 2) NOT NULL DEFAULT 0,
AVAILABLE_QUANTITY DECIMAL(10, 2) NOT NULL DEFAULT 0,
LOCKED_QUANTITY DECIMAL(10, 2) NOT NULL DEFAULT 0,
LAST_IN_TIME TIMESTAMP,
LAST_OUT_TIME TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MATERIAL_INVENTORY PRIMARY KEY (INVENTORY_ID)
) ORGANIZE BY ROW;
# 库存流水表
CREATE TABLE MATERIAL_INVENTORY_LOG (
LOG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
WAREHOUSE_ID VARCHAR(20) NOT NULL,
MATERIAL_ID VARCHAR(32) NOT NULL,
LOG_TYPE VARCHAR(20) NOT NULL,
QUANTITY_BEFORE DECIMAL(10, 2) NOT NULL,
QUANTITY_CHANGE DECIMAL(10, 2) NOT NULL,
QUANTITY_AFTER DECIMAL(10, 2) NOT NULL,
RELATED_ID VARCHAR(32),
RELATED_TYPE VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MATERIAL_INVENTORY_LOG PRIMARY KEY (LOG_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 MONTH
);
# 产品追溯表
CREATE TABLE PRODUCT_TRACE (
TRACE_ID VARCHAR(32) NOT NULL,
PRODUCT_SN VARCHAR(50) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
PRODUCTION_ORDER_ID VARCHAR(32),
PRODUCTION_DATE DATE,
WORKSHOP_ID VARCHAR(20),
PRODUCTION_LINE VARCHAR(50),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCT_TRACE PRIMARY KEY (TRACE_ID)
) ORGANIZE BY ROW;
# 产品追溯明细表
CREATE TABLE PRODUCT_TRACE_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
TRACE_ID VARCHAR(32) NOT NULL,
MATERIAL_ID VARCHAR(32) NOT NULL,
MATERIAL_LOT VARCHAR(50),
QUANTITY DECIMAL(10, 2),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCT_TRACE_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;
# 创建索引
CREATE UNIQUE INDEX IDX_MATERIAL_INVENTORY_WH_MAT ON MATERIAL_INVENTORY(WAREHOUSE_ID, MATERIAL_ID);
CREATE UNIQUE INDEX IDX_PRODUCT_TRACE_SN ON PRODUCT_TRACE(PRODUCT_SN);
CREATE INDEX IDX_PRODUCT_TRACE_ORDER ON PRODUCT_TRACE(PRODUCTION_ORDER_ID);
CREATE INDEX IDX_PRODUCT_TRACE_DETAIL_TRACE ON PRODUCT_TRACE_DETAIL(TRACE_ID);
Part05-风哥经验总结与分享
5.1 制造库设计要点
- BOM结构设计要合理，支持多版本
- 产品追溯要有完整的链路记录
- 设备状态按时间序列存储
- 库存操作要有完整的流水记录
- 生产计划与订单要关联
- 质检数据要可追溯
5.2 性能优化建议
| 场景 | 优化方案 |
|---|---|
| BOM查询 | 递归查询优化，适当冗余 |
| 设备数据 | 按时间分区，时序数据库 |
| 追溯查询 | 产品SN索引，关联查询优化 |
| 库存流水 | 按时间分区，定期归档 |
5.3 运维要点
- 定期归档历史生产数据
- 监控表空间使用情况
- 定期RUNSTATS和REORG
- 完善的备份和恢复方案
- 设备数据要及时归档
- 追溯数据要长期保存
学习交流加群风哥微信: itpux-com
风哥Oracle/MySQL/PostgreSQL/Greenplum/DB2/Redis等数据库培训课程，10年一线实战经验，企业级培训，真正掌握数据库核心技术！
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
