DB2移表(表空间不足后，把数据导出来，把原来的表删除重建)

最新推荐文章于 2023-08-16 09:04:43 发布
iteye_14360
最新推荐文章于 2023-08-16 09:04:43 发布
阅读量816
点赞数
CC 4.0 BY-SA版权
分类专栏： DB2 文章标签： db2
本文链接：https://blog.youkuaiyun.com/iteye_14360/article/details/82603516
DB2 专栏收录该内容
2 篇文章
订阅专栏
本文详细介绍了如何从旧表中导出物流订单数据，删除原表，创建新表，并进行数据导入及索引建立的过程。通过一系列SQL操作，实现数据的有效迁移和结构优化，确保了数据的一致性和效率。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 1.导出数据
EXPORT TO SOL_T_LOGISTICSORDER20140822.DEL OF DEL SELECT LOGISTICSORDERID,SALEORDERNO,SALEORDERITEMNO,OMSITEMNO,SUPPLIERCODE,EXPRESSCOMPANYCODE,EXPRESSNO,SENDERNAME,SENDERREGIONCODE,SENDERCITYCODE,SENDERDISTRICTCODE,SENDERRCD,SENDERADDRESS,SENDERPHONE,SENDERMOBILE,RECEIVERNAME,RECEIVERRCD,RECEIVERADDRESS,RECEIVERPHONE,RECEIVERMOBILE,RETURNADDRESSCODE,TRANSPORTAREACODE,SENDSTARTTIME,SENDENDTIME,WEIGHT,GOODSNAME,GOODSVALUE,REMARK,SENDSTATUS,SENDTYPE,EXCEPTIONCODE,EXCEPTIONMSG,CREATETIME,LASTUPDATETIME,SENDERTOWNCODE,SENDERREGION,SENDERCITY,SENDERDISTRICT,SENDERTOWN,RECEIVERREGIONCODE,RECEIVERCITYCODE,RECEIVERDISTRICTCODE,RECEIVERTOWNCODE,RECEIVERREGION,RECEIVERCITY,RECEIVERDISTRICT,RECEIVERTOWN,ISCANCEL,EXPRESSCOMPANYNAME,SELFDELIVERYNAME,SELFDELIVERYMOBILE FROM SOP1ADM.SOL_T_LOGISTICSORDER WITH UR;

-- 2删除SOL_T_LOGISTICSORDER表
drop table SOP1ADM.SOL_T_LOGISTICSORDER;

-- 3.创建新表
CREATE TABLE "SOP1ADM "."SOL_T_LOGISTICSORDER"  (
		  "LOGISTICSORDERID" VARCHAR(32) NOT NULL , 
		  "SALEORDERNO" VARCHAR(25) , 
		  "SALEORDERITEMNO" VARCHAR(1200) , 
		  "OMSITEMNO" VARCHAR(1200) , 
		  "SUPPLIERCODE" VARCHAR(32) , 
		  "EXPRESSCOMPANYCODE" VARCHAR(64) , 
		  "EXPRESSNO" VARCHAR(25) , 
		  "SENDERNAME" VARCHAR(100) , 
		  "SENDERREGIONCODE" VARCHAR(25) , 
		  "SENDERCITYCODE" VARCHAR(25) , 
		  "SENDERDISTRICTCODE" VARCHAR(25) , 
		  "SENDERRCD" VARCHAR(400) , 
		  "SENDERADDRESS" VARCHAR(400) , 
		  "SENDERPHONE" VARCHAR(32) , 
		  "SENDERMOBILE" VARCHAR(25) , 
		  "RECEIVERNAME" VARCHAR(100) , 
		  "RECEIVERRCD" VARCHAR(400) , 
		  "RECEIVERADDRESS" VARCHAR(400) , 
		  "RECEIVERPHONE" VARCHAR(32) , 
		  "RECEIVERMOBILE" VARCHAR(25) , 
		  "RETURNADDRESSCODE" VARCHAR(32) , 
		  "TRANSPORTAREACODE" VARCHAR(25) , 
		  "SENDSTARTTIME" TIMESTAMP , 
		  "SENDENDTIME" TIMESTAMP , 
		  "WEIGHT" VARCHAR(25) , 
		  "GOODSNAME" VARCHAR(200) , 
		  "GOODSVALUE" VARCHAR(10) , 
		  "REMARK" VARCHAR(400) , 
		  "SENDSTATUS" VARCHAR(4) , 
		  "SENDTYPE" VARCHAR(1) , 
		  "EXCEPTIONCODE" VARCHAR(25) , 
		  "EXCEPTIONMSG" VARCHAR(400) , 
		  "CREATETIME" TIMESTAMP , 
		  "LASTUPDATETIME" TIMESTAMP , 
		  "SENDERTOWNCODE" VARCHAR(25) , 
		  "SENDERREGION" VARCHAR(50) , 
		  "SENDERCITY" VARCHAR(100) , 
		  "SENDERDISTRICT" VARCHAR(100) , 
		  "SENDERTOWN" VARCHAR(100) , 
		  "RECEIVERREGIONCODE" VARCHAR(25) , 
		  "RECEIVERCITYCODE" VARCHAR(25) , 
		  "RECEIVERDISTRICTCODE" VARCHAR(25) , 
		  "RECEIVERTOWNCODE" VARCHAR(25) , 
		  "RECEIVERREGION" VARCHAR(50) , 
		  "RECEIVERCITY" VARCHAR(100) , 
		  "RECEIVERDISTRICT" VARCHAR(100) , 
		  "RECEIVERTOWN" VARCHAR(100) , 
		  "ISCANCEL" VARCHAR(1) , 
		  "EXPRESSCOMPANYNAME" VARCHAR(100) , 
		  "SELFDELIVERYNAME" VARCHAR(30) , 
		  "SELFDELIVERYMOBILE" VARCHAR(11),

		  "SENDFLAG" INTEGER DEFAULT 1,
		  "RECEIVERZIPCODE" VARCHAR(16),
		  "SENDERZIPCODE" VARCHAR(16),
		  "RETURNNAME" VARCHAR(100),
		  "RETURNREGIONCODE" VARCHAR(25),
		  "RETURNCITYCODE" VARCHAR(25),
		  "RETURNDISTRICTCODE" VARCHAR(25),
		  "RETURNTOWNCODE" VARCHAR(25),
		  "RETURNRCD" VARCHAR(400),
		  "RETURNADDRESS" VARCHAR(400),
		  "RETURNZIPCODE" VARCHAR(16),
		  "RETURNMOBILE" VARCHAR(25),
		  "RETURNPHONE" VARCHAR(32),
		  "RETURNREGION" VARCHAR(50),
		  "RETURNCITY" VARCHAR(100),
		  "RETURNDISTRICT" VARCHAR(100),
		  "RETURNTOWN" VARCHAR(100)
		 )IN "TBS_16K" INDEX IN SOP1_IX; 

COMMENT ON TABLE "SOP1ADM "."SOL_T_LOGISTICSORDER" IS 'C店物流订单表';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."CREATETIME" IS '创建时间';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."EXCEPTIONCODE" IS '快递异常编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."EXCEPTIONMSG" IS '快递异常描述';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."EXPRESSCOMPANYCODE" IS '快递公司编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."EXPRESSCOMPANYNAME" IS '物流公司名称';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."EXPRESSNO" IS '运单号';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."GOODSNAME" IS '货物名称';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."GOODSVALUE" IS '货价';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."ISCANCEL" IS '取消标识';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."LASTUPDATETIME" IS '最后更新时间';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."LOGISTICSORDERID" IS '物流订单号';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."OMSITEMNO" IS 'OMS行项目号';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERADDRESS" IS '收件人地址';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERCITY" IS '收货市';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERCITYCODE" IS '收货市编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERDISTRICT" IS '收货区';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERDISTRICTCODE" IS '收货区编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERMOBILE" IS '收件人手机';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERNAME" IS '收件人姓名';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERPHONE" IS '收件人电话';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERRCD" IS '收件人省市区';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERREGION" IS '收货省';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERREGIONCODE" IS '收货省编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERTOWN" IS '收货镇';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERTOWNCODE" IS '收货镇编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."REMARK" IS '备注';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNADDRESSCODE" IS '退货地址编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SALEORDERITEMNO" IS '销售订单行项目号（逗号隔开）';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SALEORDERNO" IS '销售订单号';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SELFDELIVERYMOBILE" IS '自配送送货人电话';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SELFDELIVERYNAME" IS '自配送送货人姓名';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDENDTIME" IS '取件截止时间';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERADDRESS" IS '发件人地址';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERCITY" IS '发货市';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERCITYCODE" IS '发件人市编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERDISTRICT" IS '发货区';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERDISTRICTCODE" IS '发件人区编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERMOBILE" IS '发件人手机';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERNAME" IS '发件人姓名';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERPHONE" IS '发件人电话';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERRCD" IS '发件人省市区';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERREGION" IS '发货省';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERREGIONCODE" IS '发件人省编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERTOWN" IS '发货镇';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERTOWNCODE" IS '发货镇编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDSTARTTIME" IS '取件起始时间';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDSTATUS" IS '发送状态（发送状态0：已发送；1：接收；2：不需要传输；3：未合作 4：下单失败）';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDTYPE" IS '发货方式 0：在线下单 1：自己联系物流';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SUPPLIERCODE" IS '商户编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."TRANSPORTAREACODE" IS '运输区域编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."WEIGHT" IS '重量';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDFLAG" IS '发送标示,0代表未发送SOD,1代表已发送SOD,2代表字段校验异常';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RECEIVERZIPCODE" IS '收件人邮编';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."SENDERZIPCODE" IS '发件人邮编';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNNAME" IS '退货人姓名';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNREGIONCODE" IS '退货人省编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNCITYCODE" IS '退货人市编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNDISTRICTCODE" IS '退货人区编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNTOWNCODE" IS '退货人镇编码';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNRCD" IS '退货人省市区';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNADDRESS" IS '退货人地址';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNZIPCODE" IS '退货人邮编';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNMOBILE" IS '退货人手机';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNPHONE" IS '退货人电话';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNREGION" IS '退货人省';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNCITY" IS '退货人市';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNDISTRICT" IS '退货人区';
COMMENT ON COLUMN "SOP1ADM "."SOL_T_LOGISTICSORDER"."RETURNTOWN" IS '退货人镇';


ALTER TABLE "SOP1ADM "."SOL_T_LOGISTICSORDER" 
	ADD CONSTRAINT "PK_SOL_T_LOGISTICSORDER" PRIMARY KEY
		("LOGISTICSORDERID");

GRANT SELECT ON SOP1ADM.SOL_T_LOGISTICSORDER TO USER DBAMON;

-- 4.导入数据 
LOAD FROM SOL_T_LOGISTICSORDER20140822.DEL OF DEL  INSERT INTO SOP1ADM.SOL_T_LOGISTICSORDER(LOGISTICSORDERID,SALEORDERNO,SALEORDERITEMNO,OMSITEMNO,SUPPLIERCODE,EXPRESSCOMPANYCODE,EXPRESSNO,SENDERNAME,SENDERREGIONCODE,SENDERCITYCODE,SENDERDISTRICTCODE,SENDERRCD,SENDERADDRESS,SENDERPHONE,SENDERMOBILE,RECEIVERNAME,RECEIVERRCD,RECEIVERADDRESS,RECEIVERPHONE,RECEIVERMOBILE,RETURNADDRESSCODE,TRANSPORTAREACODE,SENDSTARTTIME,SENDENDTIME,WEIGHT,GOODSNAME,GOODSVALUE,REMARK,SENDSTATUS,SENDTYPE,EXCEPTIONCODE,EXCEPTIONMSG,CREATETIME,LASTUPDATETIME,SENDERTOWNCODE,SENDERREGION,SENDERCITY,SENDERDISTRICT,SENDERTOWN,RECEIVERREGIONCODE,RECEIVERCITYCODE,RECEIVERDISTRICTCODE,RECEIVERTOWNCODE,RECEIVERREGION,RECEIVERCITY,RECEIVERDISTRICT,RECEIVERTOWN,ISCANCEL,EXPRESSCOMPANYNAME,SELFDELIVERYNAME,SELFDELIVERYMOBILE) NONRECOVERABLE;


-- 5.建立索引
CREATE INDEX "SOP1ADM "."SOL_T_LOGISTICSORDER_IDX4" ON "SOP1ADM "."SOL_T_LOGISTICSORDER" 
		("CREATETIME" ASC)COLLECT SAMPLED DETAILED STATISTICS 
		COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "SOP1ADM "."SOL_T_LOGISTICSORDER_INDEX1" ON "SOP1ADM "."SOL_T_LOGISTICSORDER" 
		("SALEORDERNO" ASC,"SUPPLIERCODE" ASC)
		COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "SOP1ADM "."SOL_T_LOGISTICSORDER_INDEX2" ON "SOP1ADM "."SOL_T_LOGISTICSORDER" 
		("SUPPLIERCODE" ASC,"EXPRESSNO" ASC,"EXPRESSCOMPANYCODE" ASC)
		COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "SOP1ADM "."SOL_T_LOGISTICSORDER_INDEX3" ON "SOP1ADM "."SOL_T_LOGISTICSORDER" 
		("SENDTYPE" ASC,"CREATETIME" ASC)
		COLLECT SAMPLED DETAILED STATISTICS 
		COMPRESS NO ALLOW REVERSE SCANS;
CREATE INDEX "SOP1ADM "."SOL_T_LOGISTICSORDER_INDEX5" ON "SOP1ADM "."SOL_T_LOGISTICSORDER" 
	   ("SENDFLAG" ASC)COLLECT SAMPLED DETAILED STATISTICS 
	   COMPRESS NO ALLOW REVERSE SCANS;

--6 runstats
RUNSTATS ON TABLE SOP1ADM.SOL_T_LOGISTICSORDER ON ALL COLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL;