db2 范围分区表常用命令

范围分区不同的建表方式
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))
  PARTITION BY RANGE(l_shipdate)
( STARTING '1/1/1992' ENDING '30/06/1992',
  STARTING '1/7/1992' ENDING '31/12/1992',
  STARTING '1/1/1993' ENDING '30/6/1993',
  STARTING '1/7/1993' ENDING '31/12/1993')   --不指定边界
 
  PARTITION BY RANGE(l_shipdate)
( STARTING MINVALUE,
  STARTING '1/1/1992' ENDING '30/06/1992',
  STARTING '1/7/1992' ENDING '31/12/1992',
  STARTING '1/1/1993' ENDING '30/6/1993',
  STARTING '1/7/1993' ENDING '31/12/1993',
  ENDING MAXVALUE)       --指定边界
 
  PARTITION BY RANGE(l_shipdate)
 (STARTING MINVALUE,
 STARTING '1/1/1992' ENDING '31/12/1998'
                EVERY 1 MONTH,
 ENDING MAXVALUE);       --默认连续创建分区
 
 PARTITION BY RANGE(l_shipdate)
( PART JAN1992 STARTING '1/1/1992'  ENDING '30/6/1992' IN DMS_D1,
  PART JULY1992 STARTING '1/7/1992' ENDING '31/12/1992' IN DMS_D2,
  PART JAN 1993 STARTING '1/1/1993' ENDING '30/6/1993' IN DMS_D3,
  PART JULY1993 STARTING '1/7/1993' ENDING '31/12/1993' IN DMS_D4);  --指定表空间，并对各分区命名
 
 
 
  db2 describe data partitions for table LINEITEM    --查看结构
  db2 describe data partitions for table LINEITEM show detail   --查看详细结构
 
  db2 “select datapartitionnum(l_shipdate) as PartitionId, l_shipdate from lineitem
  where l_shipdate between '01/06/1992' and '31/07/1992'
  order by l_shipdate”   --查看数据分布

db2 “create index I_LINEITEM on LINEITEM(L_SHIPDATE) in DMS_I1”   --建立索引

参考文档 http://www.ibm.com/developerworks/cn/education/data/dm0612read/section4.html#ibm-pcon