首页 / DB2 / db2 定义分区表和分区键
db2 定义分区表和分区键
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了db2 定义分区表和分区键，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8356字，纯文字阅读大概需要12分钟。
内容图文
下面，为了提高数据库性能，我们将不同的分区放到不同的表空间下。首先创建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)
内容总结
以上是互联网集市为您收集整理的db2 定义分区表和分区键全部内容，希望文章能够帮你解决db2 定义分区表和分区键所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。