db2 表分区实例.docx

drop table MIBS.IMAGEINFO_HIS;CREATE TABLE MIBS.IMAGEINFO_HIS (ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, MINVALUE 1, MAXVALUE 9223372036854775807, NO CYCLE, NO ORDER), BATCHNUM INTEGER, STORETIME DATE NOT NULL, IMAGEPATH VARCHAR(500), IMAGESTAMP VARCHAR(1) NOT NULL, SCANTYPE SMALLINT NOT NULL, USERFLAG SMALLINT NOT NULL, IMAGEOBJECT BLOB(10485760) LOGGED NOT COMPACT, FACEFLAG VARCHAR(1) NOT NULL, BATCHSN VARCHAR(22) NOT NULL, SEQUENCESN VARCHAR(4) NOT NULL, BPCERTIFICATETYPE VARCHAR(1) NOT NULL, IMAGESN VARCHAR(20) NOT NULL ) IN TSP_MIBSSE LONG IN TSP_BLOB PARTITION BY RANGE(STORETIME NULLS LAST)( PARTITION part20130601 STARTING 2013-06-01 ENDING 2013-06-01);ALTER TABLE MIBS.IMAGEINFO_HIS ADD PRIMARY KEY (ID );CREATE INDEX MIBS.IMAGEINFO_HIS_BATCHSN_INDEX ON MIBS.IMAGEINFO_HIS (BATCHSN ASC ) PCTFREE 10 ALLOW REVERSE SCANS;CREATE INDEX MIBS.IMAGEINFO_HIS_IMAGESN_INDEX ON MIBS.IMAGEINFO_HIS (IMAGESN ASC ) PCTFREE 10 ALLOW REVERSE SCANS;CREATE INDEX MIBS.IMAGEINFO_HIS_USERFLAG ON MIBS.IMAGEINFO_HIS (USERFLAG ASC ) PCTFREE 10 ALLOW REVERSE SCANS;GRANT SELECT ON TABLE MIBS.IMAGEINFO_HIS TO USER DBVIEW;- End of generated script for 197.3.136.26-MIBS2VES-XBIMAGE (mibs)alter table MIBS.IMAGEINFO_HISTMP add partition part20130601 starting 2013-06-01 ending 2013-06-01 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130602 starting 2013-06-02 ending 2013-06-02 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130603 starting 2013-06-03 ending 2013-06-03 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130604 starting 2013-06-04 ending 2013-06-04 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130605 starting 2013-06-05 ending 2013-06-05 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130606 starting 2013-06-06 ending 2013-06-06 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130607 starting 2013-06-07 ending 2013-06-07 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130608 starting 2013-06-08 ending 2013-06-08 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130609 starting 2013-06-09 ending 2013-06-09 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130610 starting 2013-06-10 ending 2013-06-10 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130611 starting 2013-06-11 ending 2013-06-11 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130612 starting 2013-06-12 ending 2013-06-12 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130613 starting 2013-06-13 ending 2013-06-13 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130614 starting 2013-06-14 ending 2013-06-14 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130615 starting 2013-06-15 ending 2013-06-15 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130616 starting 2013-06-16 ending 2013-06-16 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130617 starting 2013-06-17 ending 2013-06-17 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130618 starting 2013-06-18 ending 2013-06-18 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130619 starting 2013-06-19 ending 2013-06-19 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130620 starting 2013-06-20 ending 2013-06-20 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130621 starting 2013-06-21 ending 2013-06-21 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130622 starting 2013-06-22 ending 2013-06-22 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130623 starting 2013-06-23 ending 2013-06-23 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130624 starting 2013-06-24 ending 2013-06-24 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130625 starting 2013-06-25 ending 2013-06-25 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130626 starting 2013-06-26 ending 2013-06-26 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130627 starting 2013-06-27 ending 2013-06-27 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130628 starting 2013-06-28 ending 2013-06-28 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130629 starting 2013-06-29 ending 2013-06-29 IN TSP_MIBSSE LONG IN TSP_BLOB;alter table MIBS.IMAGEINFO_HISTMP add partition part20130630 starting 2013-06-30 ending 2013-06-30 IN TSP_MIBSSE LONG IN TSP_BLOB;导出数据export to D:dataIMAGEINFO_HIS426.del of del lobs to D:datalob MODIFIED BY CODEPAGE=1208 select * from mibs.IMAGEINFO_HIS where STORETIME =2013-04-26;导入数据import from D:dataIMAGEINFO_HIS426.del of del lobs from D:datalob MODIFIED BY CODEPAGE=1208 COMMITCOUNT 1000 insert into mibs.IMAGEINFO_HISTMP;drop table mibs.IMAGEINFO_HIS;rename table mibs.IMAGEINFO_HISTMP to IMAGEINFO_HIS;db2 SELECT SUBSTR(tabname, 1,20) tabname, SUBSTR(datapartitionname, 1, 20) datapartitionname,seqno, SUBSTR(lowvalue, 1, 40) lowvalue, SUBSTR(highvalue, 1, 40) highvalueFROM SYSCAT.DATAPARTITIONS WHERE TABNAME=IMAGEINFO_HIS2 ORDER BY seqno导出表分区ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130201 INTO TABLE MIBS.IMAGEINFO_HIS20130201;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130202 INTO TABLE MIBS.IMAGEINFO_HIS20130202;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130203 INTO TABLE MIBS.IMAGEINFO_HIS20130203;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130204 INTO TABLE MIBS.IMAGEINFO_HIS20130204;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130205 INTO TABLE MIBS.IMAGEINFO_HIS20130205;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130206 INTO TABLE MIBS.IMAGEINFO_HIS20130206;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130207 INTO TABLE MIBS.IMAGEINFO_HIS20130207;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130208 INTO TABLE MIBS.IMAGEINFO_HIS20130208;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130209 INTO TABLE MIBS.IMAGEINFO_HIS20130209;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130210 INTO TABLE MIBS.IMAGEINFO_HIS20130210;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130211 INTO TABLE MIBS.IMAGEINFO_HIS20130211;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130212 INTO TABLE MIBS.IMAGEINFO_HIS20130212;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130213 INTO TABLE MIBS.IMAGEINFO_HIS20130213;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130214 INTO TABLE MIBS.IMAGEINFO_HIS20130214;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130215 INTO TABLE MIBS.IMAGEINFO_HIS20130215;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130216 INTO TABLE MIBS.IMAGEINFO_HIS20130216;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130217 INTO TABLE MIBS.IMAGEINFO_HIS20130217;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130218 INTO TABLE MIBS.IMAGEINFO_HIS20130218;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130219 INTO TABLE MIBS.IMAGEINFO_HIS20130219;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130220 INTO TABLE MIBS.IMAGEINFO_HIS20130220;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130221 INTO TABLE MIBS.IMAGEINFO_HIS20130221;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130222 INTO TABLE MIBS.IMAGEINFO_HIS20130222;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130223 INTO TABLE MIBS.IMAGEINFO_HIS20130223;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130224 INTO TABLE MIBS.IMAGEINFO_HIS20130224;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130225 INTO TABLE MIBS.IMAGEINFO_HIS20130225;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130226 INTO TABLE MIBS.IMAGEINFO_HIS20130226;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130227 INTO TABLE MIBS.IMAGEINFO_HIS20130227;ALTER TABLE mibs.IMAGEINFO_HIS DETACH PARTITION part20130228 INTO TABLE MIBS.IMAGEINFO_HIS20130228;DROP TABLE MIBS.IMAGEINFO_HIS20130302;DROP TABLE MIBS.IMAGEINFO_HIS20130303;DROP TABLE MIBS.IMAGEINFO_HIS20130304;DROP TABLE MIBS.IMAGEINFO_HIS20130305;DROP TABLE MIBS.IMAGEINFO_HIS20130306;DROP TABLE MIBS.IMAGEINFO_HIS20130307;DROP TABLE MIBS.IMAGEINFO_HIS20130308;DROP TABLE MIBS.IMAGEINFO_HIS20130309;DROP TABLE MIBS.IMAGEINFO_HIS20130310;DROP TABLE MIBS.IMAGEINFO_HIS20130311;DROP TABLE MIBS.IMAGEINFO_HIS20130312;DROP TABLE MIBS.IMAGEINFO_HIS20130313;DROP TABLE MIBS.IMAGEINFO_HIS20130314;DROP TABLE MIBS.IMAGEINFO_HIS20130315;DROP TABLE MIBS.IMAGEINFO_HIS20130316;DROP TABLE MIBS.IMAGEINFO_HIS20130317;DROP TABLE MIBS.IMAGEINFO_HIS20130318;DROP TABLE MIBS.IMAGEINFO_HIS20130319;DROP TABLE MIBS.IMAGEINFO_H