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

zzl001

-- 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;