DB2分区表重点笔记

–说明
如果一个表用的partition by字句创建,那么它就是分区表。

可以把分区表看成把一张大表分成几个小表,处理的时候SQL语句被DB2进行了加工,实际上去访问各张小表。
各张小表可以通过转入、转出灵活的加入到一个分区、脱离一个分区。这个过程非常迅速。其实可以把分区表看成一个逻辑概念,每次加入一个分区,其实就是链表上多一个节点,指向实际存在的那张表。每次删除一个分区,也只不过是从链表上删除而已。

索引是按照分区的。

–限制
分区表中的所有表空间必须具有相同的页大小、扩展数据大小、存储机制(DMS、SMS)和类型(REGULAR、LARGE),并且所有的表空间都必须位于统一数据库分区组中。

如果插入的数据大于了分区范围,会的报错。

–好处
1.比如按月来分,那么可以备份的时候备份想要的表空间
2.提高了SQL的性能,如果用户要访问一个特定月的数据,只要访问对应的分区就可以了
3.可以并行IO
4.如果只访问一个月,那么锁只会用于某个分区

–简单实例
短格式:
[db2inst1@localhost ~]$ db2 create tablespace tbs01
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 create tablespace tbs02
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 create tablespace tbs03
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 “create table tbp1(c1 int) in tbs01,tbs02,tbs03 partition by range(c1) (starting (1) ending (99) every (33))”
DB20000I The SQL command completed successfully.

长格式:
[db2inst1@localhost ~]$ db2 “create table tbp2(c1 int) partition by range(c1) (partition p1 starting(1) ending(33) in tbs01,part p2 ending(66) in tbs02,part p3 ending(99) in tbs03)”

–说明
分区列必须是基本类型(不允许使用LONG,LONG VARCHARS等类型)。

范围中有两个特殊值:MINVALUE和MAXVALUE分别表明分区的范围。

–索引(最好明确指定索引的表空间!不然可能会建立在不对的地方)
9.7之前只能是全局索引(非分区索引),这样子索引只能保存在一个表空间之中。
9.7之后可以建立分区索引,且默认是分区索引。

全局索引(默认建立在第一个分区所在的表空间中):
create table t1(c1 int,c2 int)
in tbs01,tbs02
index in tbs03
partition by range(c1)
(starting from (1) ending (100) every (33))

create index i1 on t1(c1) not partitioned
create index i2 on t1(c2) not partitioned in tbs04

分区索引:
可以指定索引放到哪个表空间中,也可以分开来放,如果不指定表空间,则按每个分区的内容存放到其对应的表空间上,或者是建表时规定的表空间上。如:
create table t1(c1 int,c2 int)
in tbs01,tbs02,tbs03
index in tbs04
partition by range(c1)
(starting from (1) ending (100) every (33))

create index i1 on t1(c1) partitioned
create index i2 on t1(c2) not partitioned

create table t2(c1 int,c2 int)
in tbs01,tbs02,tbs03
index in tbs03
partition by range(1)
(starting from (1) ending (33) index in tbs05,
starting from (34) ending (66) index in tbs05,
starting from (67) enxing (100))

create index i3 on t2(c1) partitioned

这个时候,i1会的保存在tbs01,tbs02,tbs03中。
i2会的保存在tbs04中。
i3会的保存在tbs05和tbs03中。tbs05保存了两张表的两个索引部分,范围是1~33,34~66,tbs03则保存了67~100的索引。

–监控
LIST UTILITIES SHOW DETAIL

–查看分区表信息
[db2inst1@localhost ~]$ db2 describe data partitions for table test
[db2inst1@localhost ~]$ db2 describe data partitions for table test show detail

datapartitionnum()函数可以用来查看某个key属于哪个分区。

–转入、转出
参考:数据库管理 > 管理概念 > 数据库对象 > 表 > 表分区和数据组织方案 > 分区表 > 数据分区和范围

转入:将一张普通表加入到分区表中
转出:将分区表中的某张表变成普通表。

借助 DB2 V9.7 FP1 和更高发行版,在使用带 DETACH PARTITION 子句的 ALTER TABLE 语句从分区表拆离数据分区时,源分区表仍然可供在 RS、CS 或 UR 隔离级别下运行的动态查询访问。同样,借助 DB2 V10.1 和更高发行版,在使用带 ATTACH PARTITION 子句的 ALTER TABLE 语句将数据分

区连接至分区表时,目标分区表仍然可供在 RS、CS 或 UR 隔离级别下运行的动态查询访问。

对于转出:
为了加速拆离操作,源表上的索引清除是通过异步索引清除进程在后台自动完成的。如果源表上没有定义已拆离的从属表,那么不需要发出 SET INTEGRITY 语句就可以完成拆离操作。

可以删除新表,也可以将其连接到另一个表,或者也可首先将其截断并装入新数据,然后再将其重新连接到源表。即使异步索引清除尚未完成也可以立即执行这些操作,除非源表有已拆离的从属表。

要确定已拆离表是否可访问,请查询 SYSCAT.TABDETACHEDDEP 目录视图。如果发现已拆离表不可访问,那么对所有已拆离的从属表发出带有 IMMEDIATE CHECKED 选项的 SET INTEGRITY 语句。如果在维护已拆离表的所有已拆离从属表之前试图访问该已拆离表,那么将返回错误 (SQL20285N)。

对于转入:
记得SET INTEGRITY

此 ALTER TABLE⋯ATTACH PARTITION 语句将等待正在对 STOCK 表运行的现有静态查询或可重复读查询完成,然后使相关程序包无效。这样的现有查询通常在连接操作开始处理 STOCK 表之前完成。针对 STOCK 表的现有动态不可重复读查询将继续运行,并且可以与连接操作同时运行。在 ALTER TABLE⋯ATTACH PARTITION 语句开始处理 STOCK 表之后,任何访问该表的新查询必须等待连接操作完成。

–添加分区
db2 “alter table reporter.reporter_status add PARTITION MX STARTING(‘2014-05-01-00.00.00.000000’) ENDING(‘2014-06-01-00.00.00.000000’) EXCLUSIVE IN RPTREG32K”

–相关视图
SYSCAT.DATAPARTITIONS
[db2inst1@localhost ~]$ db2 “select substr(datapartitionname,1,30),substr(tabschema,1,30),substr(tabname,1,30),datapartitionid,tbspaceid,access_mode,status from SYSCAT.DATAPARTITIONS”
其中:
ACCESS_MODE:
Access restriction state of the data partition. These states only apply to objects that are in set integrity pending state or to objects that were processed by a SET INTEGRITY statement. Possible values are:
D = No data movement
F = Full access
N = No access
R = Read-only access

STATUS:
A = Data partition is newly attached
D = Data partition is detached and detached dependents are to be incrementally maintained with respect to the content of this partition
I = Detached data partition whose entry in the catalog is maintained only during asynchronous index cleanup; rows with a STATUS value of ‘I’ are removed when all index records referring to the detached partition have been deleted
L = Data partition is logically detached
Empty string = Data partition is visible (normal status)
Bytes 2 through 32 are reserved for future use.

–一个例子。
这个例子只保存5天的数据,所以我们建立6个分区,每次用5个,当用第6个的时候我们转出第一个分区,并重新定义它,让它成为下一天的分区。
[db2inst1@localhost ~]$ db2 “create table test(id int,data date) partition by range(data) (partition d1 starting ‘3/24/2008’,partition d2 starting ‘3/25/2008’,partition d3 starting ‘3/26/2008’,partition d4 starting ‘3/27/2008’,partition d5 starting ‘3/28/2008’,partition d6 starting ‘3/29/2008’ ending ‘3/29/2008′)”
插入数据:
[db2inst1@localhost ~]$ db2 “insert into test values(1,’3/25/2008′)”
[db2inst1@localhost ~]$ db2 “insert into test values(1,’3/24/2008′)”
这个时候可以往里边写5天以内的数据了,直到第六天的时候,我们进行下面的操作:
[db2inst1@localhost ~]$ db2 “alter table test detach partition d1 into table reuse”
[db2inst1@localhost ~]$ db2 commit –这个时候查看管理视图会发现这个分区木有了
这个时候就多了一张reuse的表。这个过程是很快的,毕竟物理上的数据没有发生移动。
然后我们把这个分区(表)上的数据给弄干净,待会我们要用来放第七天的东东呢:
[db2inst1@localhost ~]$ db2 “load from /dev/null of del replace into reuse nonrecoverable”
现在我们再把这张表弄会分区中,让它存放下一天的分区。注意这里别忘了set integrity:
同时,如果要建立索引,可以现在reuse表中建好,然后再进行这一步:
[db2inst1@localhost ~]$ db2 “alter table test attach partition d1 starting ’03/30/2008′ ending ’03/30/2008′ from table reuse” –这个时候只有这个分区的access mode变成了N,status变成了A。其它分区一切正常。在连接过程中任何现有的非RR操作可以同步进行,但是新的操作会被阻塞。在连接结束后除了新加入的那表无法看到,其它分区都能正常读写。
[db2inst1@localhost ~]$ db2 commit
[db2inst1@localhost ~]$ db2 set integrity for test off
然后就要把这个表从check pending状态取出来,方法有下面几个(对于大表,如果确定不会有问题,建议直接unchecked):
–由于这个时候只是一个分区处于需要set的状态,但这张表目前其它分区正常,能访问,所以为了能执行下面一行的命令,我们需要手动把它设置成需要set状态(这一步貌似可以没有)。
[db2inst1@localhost ~]$ db2 set integrity for test immediate checked not incremental
最后一步可以允许读写,最好参考这个:
SET INTEGRITY FOR stock ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN stock USE stock_ex;
–或者如果确定没问题的话,直接至于unchecked就好了(分区表好像不能用这个)
db2 set integrity for reporter_status all immediate unchecked

–一个生产上的例子(有时候会出现PartitionName为SQL140212154003030的东东,不要慌,这是在做异步的索引清除,可以用db2pd -util看下)
db2 “alter table reporter.reporter_status detach partition m2 into table reporter_status_reuse”
db2 commit
db2 “load from /dev/null of del replace into reporter_status_reuse nonrecoverable”
db2 “alter table reporter.reporter_status attach partition m2 starting ’04/01/2014′ ending ’05/01/2014’ exclusive from table reporter_status_reuse”
db2 commit
db2 set integrity for reporter.reporter_status off
db2 set integrity for reporter.reporter_status immediate checked
db2 commit

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*