分区表

1、STARTING '2011-01-01'ENDING '2011-01-02'

这里包括边界值，即>=2011-01-01且<=2011-01-02

2、查看分区信息(重点关注是否包含边界)

db2 describe data partitions for table 表名

3、创建表空间案例

create tablespace dms_d1 managed bydatabase using (file 'c:\ts1' 10000);

create tablespace dms_d2 managed bydatabase using (file 'c:\ts2' 10000);

create tablespace dms_d3 managed bydatabase using (file 'c:\ts3' 10000);

create tablespace dms_d4 managed bydatabase using (file 'c:\ts4' 10000);

create tablespace dms_i1 managed bydatabase using (file 'c:\ts5' 10000);

4、创建分区表的经典SQL语句

CREATE TABLE lineitem

(

       l_orderkey  DECIMAL(10,0) NOT NULL,

       l_partkey   INTEGER,

       l_suppkey       INTEGER,

       l_linenumber    INTEGER,

       l_quantity      DECIMAL(12,2),

       l_extendedprice DECIMAL(12,2),

        l_discount      DECIMAL(12,2),

       l_tax           DECIMAL(12,2),

       l_returnflag    CHAR(1),

       l_linestatus    CHAR(1),

       l_shipdate      DATE,

       l_commitdate    DATE,

       l_receiptdate   DATE,

       l_shipinstruct  CHAR(25),

       l_shipmode      CHAR(10),

       l_comment       VARCHAR(44)

)

INDEX IN DMS_I1

PARTITION BY RANGE(l_shipdate)

( PARTJAN1992 STARTING '1/1/1992'    ENDING '30/06/1992' in dms_d1,

  PARTJULY1992    STARTING '1/7/1992'    ENDING '31/12/1992' in dms_d2,

  PARTJAN1993 STARTING '1/1/1993'    ENDING '30/06/1993' in dms_d3,

  PARTJULY1993    STARTING '1/7/1993'    ENDING '31/12/1993' in dms_d4);

5、如何添加一个新分区

alter table lineitem attach partition JAN1994

STARTING '1/1/1994' ENDING '30/6/1994'

from np_lineitem;

6、如何剥离一个分区

alter table lineitem detach partitionJAN1992 into lineitem_jan1992;