db2 定义分区表和分区键

下面，为了提高数据库性能，我们将不同的分区放到不同的表空间下。首先创建6个表空间，3个数据表空间，3个索引表空间：

db2 "create tablespace ts_dat managed by database using (file '/home/db2inst1/data/ts_dat' 100M)"

db2 "create tablespace ts_dat1 managed by database using (file '/home/db2inst1/data/ts_dat1' 100M)"

db2 "create tablespace ts_dat2 managed by database using (file '/home/db2inst1/data/ts_dat2' 100M)"

db2 "create tablespace ts_idx managed by database using (file '/home/db2inst1/data/ts_idx' 100M)"

db2 "create tablespace ts_idx1 managed by database using (file '/home/db2inst1/data/ts_idx1' 100M)"

db2 "create tablespace ts_idx2 managed by database using (file '/home/db2inst1/data/ts_idx2' 100M)"

首次执行后不需要执行

--DDL for Bufferpools--

CREATE BUFFERPOOL "BP32K_DATA" SIZE 32768 PAGESIZE 32768;

CREATE BUFFERPOOL "BP32K_INDEX" SIZE 32768 PAGESIZE 32768;

--Minic Storage Groups--

ALTER STOGROUP "IBMSTOGROUP" OVERHEAD 6.725000 DEVICE READ RATE 100.000000 DATA TAG NONE SET AS DEFAULT;

创建用户临时表空间

CREATE USER TEMPORARY TABLESPACE "USER_TMP" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY SYSTEM

--修改表空名TABLESPACE "USER_TMP"

USING ('/home/db2inst1/data/usrtmp')----修改路径'/home/db2inst1/data/usrtmp'

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF;

创建系统临时表空间

CREATE TEMPORARY TABLESPACE "TMP_SYSTEM"  PAGESIZE 32768 MANAGED BY SYSTEM

USING ('/home/db2inst1/data/SYStmp')

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF;

创建普通表空间

CREATE LARGE TABLESPACE "IDM_DATATABLE"  PAGESIZE 32768 MANAGED BY DATABASE

----修改表空间TABLESPACE "IDM_DATATABLE"

USING (file '/home/db2inst1/data/IDM_datatable' 100M)-----修改路径和大小

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF;

CREATE TABLESPACE "IDMINDX"  PAGESIZE 32768 MANAGED BY DATABASE

USING (file '/home/db2inst1/data/IDMINDX' 100M)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF;

CREATE LARGE TABLESPACE "IDMDTL1"  PAGESIZE 32768 MANAGED BY DATABASE

USING (file '/home/db2inst1/data/IDMDTL1' 100M)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF;

CREATE LARGE TABLESPACE "IDMDTL2"  PAGESIZE 32768 MANAGED BY DATABASE

USING (file '/home/db2inst1/data/IDMDTL2' 100M)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF;

CREATE LARGE TABLESPACE "IDMDTL3"  PAGESIZE 32768 MANAGED BY DATABASE

USING (file '/home/db2inst1/data/IDMDTL3' 100M)

EXTENTSIZE 64

PREFETCHSIZE AUTOMATIC

BUFFERPOOL "BP32K_DATA"

OVERHEAD 6.725000

TRANSFERRATE 0.320000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY OFF;

分区表类型

1、自动分区

2、手工分区      

PARTITION BY RANGE(分区列)   

(PART 分区名 STARTING 开始  ENDING 结束 IN 表空间 INDEX IN 索引锁在表空间)

省略starting,下一个上一个结束  

STARTING(2017,1) ENDING (2017,6),

ENDING (2017, 9),                

ENDING (2017, 12),               

ENDING (2018, 12));

省略ending,结束为上一个的开始

PARTITION BY RANGE(COL35)

(PART PART0 STARTING('2017-01-01') IN IDMDTL1,

PART PART1 STARTING('2017-04-01') IN IDMDTL2,

PART PART2 STARTING('2017-07-01') IN IDMDTL3,

PART PART3 STARTING('2017-10-01') ENDING('2017-12-31') IN IDMDTL1,

EEXCLUSIVE 分区2018-02-01这个排除在外

PART IDM_TRANSDTL_PART201801 STARTING('2018-01-01') ENDING('2018-02-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX,                              

表分区键不支持的数据类型如下：

用户定义的类型(结构化)

LONG VARCHAR

LONG VARCHAR FOR BIT DATA

BLOB

BINARY LARGE OBJECT

CLOB

CHARACTER LARGE OBJECT

DBCLOB

LONG VARGRAPHIC

REF

C变长字符串

Pascal变长字符串

XML

db2 "describe DATA PARTITIONS for table parttabtest02 show detail"

实例：

实例1、表空间均匀分布在表空间上

CREATE TABLE parttabtest01

( ID                INTEGER NOT NULL,

SALES_PERSON       VARCHAR(50),

REGION             VARCHAR(50),

SALES_DATE         DATE)

IN ts_dat1,ts_dat2     ------指定表在分区上

INDEX IN ts_idx        ------指定索引分区上

PARTITION BY RANGE(SALES_DATE)

( STARTING MINVALUE, STARTING '1/1/2012' ENDING '12/31/2012' EVERY 1 MONTH,ENDING MAXVALUE);

实例2、

类似的，我们也可以在分区后面加表空间名称，显式地为分区指定所在的表空间。

对于建立分区语句里没有指定表空间的分区，使用CREATE TABLE里指定的表空间。

CREATE TABLE  parttabtest02

( ID                 INTEGER NOT NULL,

SALES_PERSON       VARCHAR(50),

REGION             VARCHAR(50),

SALES_DATE         DATE)

IN TS_DAT

INDEX IN TS_IDX

PARTITION BY RANGE(SALES_DATE)

( PART PJAN STARTING '1/1/2017' ENDING '3/31/2017' IN TS_DAT1 INDEX IN TS_IDX1,-----指定分区在指定的表空间上

PART PFEB STARTING '4/1/2017' ENDING '7/31/2017' IN TS_DAT1 INDEX IN TS_IDX1,

PART PMAR STARTING '8/1/2017' ENDING '12/31/2017' IN TS_DAT2 INDEX IN TS_IDX2,

PART PAPR STARTING '1/1/2018' ENDING '11/31/2018' )

实例3、

如果选择使用CREATE TABLE语句的EVERY子句来自动生成数据分区，那么只能将一列用作表分区键。

如果选择通过在CREATE TABLE语句的PARTITION BY子句中指定每个范围来手动生成数据分区，

那么可以将多个列用作表分区键，如以下示例所示：

CREATE TABLE parttabtest03

( ID                INTEGER NOT NULL,

SALES_PERSON     VARCHAR(50),

REGION            VARCHAR(50),

SALES_YEAR       INT,

SALES_MONTH      INT)

PARTITION BY RANGE(SALES_YEAR, SALES_MONTH)

( STARTING(2017,1) ENDING (2017,6),

ENDING (2017, 9),

ENDING (2017, 12),

ENDING (2018, 12));

实例4、 

CREATE TABLE parttabtest04

( ID              INTEGER NOT NULL,

SALES_PERSON     VARCHAR(50),

REGION           VARCHAR(50),

SALES_DATE       DATE,

SALES_MONTH      GENERATED ALWAYS AS (month(SALES_DATE)))

PARTITION BY RANGE(SALES_MONTH)

( STARTING FROM 1 ENDING AT 12 EVERY 1 );

表名

CREATE TABLE IDM_TRANSDTL (

COL1 VARCHAR(22) NOT NULL,

COL2 VARCHAR(8) NOT NULL,

COL3 VARCHAR(8) NOT NULL,

COL4 VARCHAR(6) NOT NULL,

COL5 VARCHAR(6) ,

COL6 VARCHAR(6) ,

COL7 VARCHAR(40) ,

COL8 VARCHAR(4) ,

COL9 VARCHAR(3) ,

COL10 VARCHAR(8) ,

COL11 VARCHAR(22) ,

COL12 VARCHAR(4) ,

COL13 VARCHAR(14) ,

COL14 VARCHAR(5) ,

COL15 VARCHAR(30) ,

COL16 VARCHAR(100) ,

COL17 VARCHAR(20) ,

COL18 VARCHAR(30) ,

COL19 VARCHAR(30) ,

COL20 VARCHAR(20) ,

COL21 VARCHAR(1) ,

COL22 VARCHAR(1) ,

COL23 VARCHAR(20) ,

COL24 VARCHAR(2) ,

COL25 VARCHAR(14) ,

COL26 VARCHAR(2) ,

COL27 VARCHAR(10) ,

COL28 VARCHAR(256) ,

COL29 VARCHAR(20) ,

COL30 VARCHAR(100) ,

COL31 VARCHAR(100) ,

COL32 VARCHAR(100) ,

COL33 VARCHAR(100) ,

COL34 VARCHAR(100) , 

COL35 DATE NOT NULL WITH DEFAULT CURRENT DATE)

INDEX IN IDMINDX PARTITION BY RANGE(COL35)

(PART PART0 STARTING('2017-01-01') IN IDMDTL1,

PART PART1 STARTING('2017-04-01') IN IDMDTL2,

PART PART2 STARTING('2017-07-01') IN IDMDTL3,

PART PART3 STARTING('2017-10-01') ENDING('2017-12-31') IN IDMDTL1,

PART IDM_TRANSDTL_PART201801 STARTING('2018-01-01') ENDING('2018-02-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX,

PART IDM_TRANSDTL_PART201802 STARTING('2018-02-01') ENDING('2018-03-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX, 

PART IDM_TRANSDTL_PART201803 STARTING('2018-03-01') ENDING('2018-04-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX, 

PART IDM_TRANSDTL_PART201804 STARTING('2018-04-01') ENDING('2018-05-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX,

PART IDM_TRANSDTL_PART201805 STARTING('2018-05-01') ENDING('2018-06-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX, 

PART IDM_TRANSDTL_PART201806 STARTING('2018-06-01') ENDING('2018-07-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX,  

PART IDM_TRANSDTL_PART201807 STARTING('2018-07-01') ENDING('2018-08-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX,

PART IDM_TRANSDTL_PART201808 STARTING('2018-08-01') ENDING('2018-09-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX, 

PART IDM_TRANSDTL_PART201809 STARTING('2018-09-01') ENDING('2018-10-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX, 

PART IDM_TRANSDTL_PART201810 STARTING('2018-10-01') ENDING('2018-11-01') EXCLUSIVE IN IDMDTL1 INDEX IN IDMINDX,

PART IDM_TRANSDTL_PART201811 STARTING('2018-11-01') ENDING('2018-12-01') EXCLUSIVE IN IDMDTL2 INDEX IN IDMINDX, 

PART IDM_TRANSDTL_PART201812 STARTING('2018-12-01') ENDING('2019-01-01') EXCLUSIVE IN IDMDTL3 INDEX IN IDMINDX)