DB2 V9表分区-技术开发专区

DB2 V9表分区

作者：IT168 程永编辑：李博（泡泡） 2007-12-27 10:07

使用手工方式创建示例分区表

手工生成方法为PARTITION BY子句中列示的每个范围创建一个新数据分区｡这种语法格式提高了定义范围时的灵活性,从而增加了数据和LOB布置选项｡对清单12所创建的示例分区表TEST3,如果用手工方式创建,可以在DB2CLP窗口中,连上数据库,先发出DROP TABLE命令,删除示例分区表TEST3,再发出CREATE TABLE命令创建示例分区表TEST3,其分区键RQ允许录入的时间段为“2007-01-01”到“2007-12-31”,具体如清单13所示:

- - 清单13 .手工创建示例分区表TEST3

C:\> DB2 CONNECT TO DB2TEST1
数据库连接信息
数据库服务器 = DB2 / NT 9.1.0
SQL 授权标识 = RHETTE
本地数据库别名 = DB2TEST1
C:\> db2 drop table test3
DB20000I SQL命令成功完成｡
C:\> db2 CREATE TABLE TEST3 ( ID INTEGER NOT NULL , RQ DATE NOT NULL , IMAGE BLOB (1 M ) NOT NULL LOGGED NOT COMPACT ) PARTITION BY RANGE (RQ NULLS LAST) (PARTITION DATAPATITION1 STARTING FROM ('2007-01-01') INCLUSIVE ENDING AT ('2007-02-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION2 STARTING FROM ('2007-02-01') INCLUSIVE ENDING AT ('2007-03-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION3 STARTING FROM ('2007-03-01') INCLUSIVE ENDING AT ('2007-04-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION4 STARTING FROM ('2007-04-01') INCLUSIVE ENDING AT ('2007-05-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION5 STARTING FROM ('2007-05-01') INCLUSIVE ENDING AT ('2007-06-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ,PARTITION DATAPATITION6 STARTING FROM ('2007-06-1') INCLUSIVE ENDING AT ('2007-07-1') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION7 STARTING FROM ('2007-07-01') INCLUSIVE ENDING AT ('2007-08-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION8 STARTING FROM ('2007-08-01') INCLUSIVE ENDING AT ('2007-09-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION9 STARTING FROM ('2007-09-01') INCLUSIVE ENDING AT ('2007-10-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION10 STARTING FROM ('2007-10-01') INCLUSIVE ENDING AT ('2007-11-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION11 STARTING FROM ('2007-11-01') INCLUSIVE ENDING AT ('2007-12-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION12 STARTING FROM ('2007-12-01') INCLUSIVE ENDING AT ('2007-12-31') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3) INDEX IN USERSPACE1
DB20000I SQL命令成功完成｡

    这样我们通过手工方式创建了示例分区表TEST3,用来对表数据进行分区的列是RQ,表数据将存储在下列表空间中:TABLESPACE1,TABLESPACE2和TABLESPACE3｡缺省情况下,索引将存储在以下表空间中:USERSPACE1｡此语句生成12个数据分区,每个数据分区包含1个键值:

 (RQ) >= ( ' 2007-01-01 ' ) , (RQ) < (' 2007-02-01 ' )
 (RQ) >= ( ' 2007-02-01 ' ) , (RQ) < (' 2007-03-01 ' )
 . . . . . .
 (RQ) >= ( ' 2007-12-01 ' ) , (RQ) <= (' 2007-12-31 ' )

    每个数据分区通过PARTITION命令进行了命名,名字分别DATAPARTITION1,DATAPARTITION2, . . . . . . DATAPARTITION12｡

    接下来我们对示例分区表TEST3插入数据,在DB2CLP窗口中,发出INSERT INTO命令,具体如清单14所示:

- - 清单14 .对示例分区表TEST3插入一条记录

C:\> DB2 INSERT INTO TEST3 VALUES ( 1 , '2007-01-10' , blob ( 'the first rows' ) )
DB20000I SQL命令成功完成｡

命令成功完成,把行插入示例分区表时,根据该行的键值以及它所处的范围自动将其放入正确的数据分区,这样我们数据就插入到了数据分区DATAPARTITION1中了｡如果该行处于对该表定义的所有范围之外,插入就会失败,并且将把以下错误返回给应用程序,比如我们插入一条如清单15所示的记录,RQ的值为'2006-01-10',不在'2007-01-01'和'2007-12-31'的范围内:

- - 清单15 .对示例分区表TEST3插入一条分区键范围外的记录

C:\> DB2 INSERT INTO TEST3 VALUES (1 , '2006-01-10' , blob ( 'the first rows' ) )
DB21034E 该命令被当作 SQL语句来处理,因为它不是有效的“命令行处理器”命令&#65377;在 SQL 处理期间,它返回:
SQL0327N 无法将行插入表 " RHETTE.TEST3 " 中,因为它在定义的数据分区范围之外&#65377;
SQLSTATE = 22525

此时我们对查看示例分区表中的记录,通过在WHERE子句中指定分区键的值,可以能够避免扫描不相关的数据,直接从DATAPARTITION1数据分区中得到想要的记录,从而提高查询处理性能,具体如清单16所示:

- - 清单16 .查询示例分区表TEST3

C:\> db2 select id,rq from test3 where rq='2007-01-10'
ID RQ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 2007-01-10
1 条记录已选择&#65377;

如果想在范围之间允许存在间隔,可以使用MINVALUE和MAXVALUE指定间隔的范围,MINVALUE表示最小的值,就是比你插入的任何值都会小的意思,MAXVALUE表示最大的值,就是比你插入的任何值都要大的意思｡对示例分区表TEST3,我们可以增量两个数据分区:
 数据分区OTHERS1,范围从MINVALUE到 ” 2007-01-01” ,不包含” 2007-01-01”;所有比” 2007-01-01 ”小的值,都会放入到此分区中｡
 数据分区OTHERS2,范围从 ” 2008-01-01” 到MAXVALUE,包含” 2008-01-01”;所有比 ” 2008-01-01” 大的值,都会放入到此分区中｡
具体如清单17中蓝色标记部分所示:

- - 清单17 .手工创建示例分区表TEST3

C:\> db2 drop table test3
DB20000I  SQL命令成功完成｡
C:\> db2 CREATE TABLE TEST3 ( ID INTEGER  NOT NULL , RQ DATE  NOT NULL , IMAGE BLOB  (1 M )  NOT NULL  LOGGED  NOT  COMPACT   ) PARTITION BY RANGE (RQ NULLS LAST)  (PARTITION DATAPATITION1 STARTING FROM ('2007-01-01') INCLUSIVE ENDING AT ('2007-02-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION2 STARTING FROM ('2007-02-01') INCLUSIVE ENDING AT ('2007-03-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION3 STARTING FROM ('2007-03-01') INCLUSIVE ENDING AT ('2007-04-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION4 STARTING FROM ('2007-04-01') INCLUSIVE ENDING AT ('2007-05-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION5 STARTING FROM ('2007-05-01') INCLUSIVE ENDING AT ('2007-06-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ,PARTITION DATAPATITION6 STARTING FROM ('2007-06-1') INCLUSIVE ENDING AT ('2007-07-1') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION7 STARTING FROM ('2007-07-01') INCLUSIVE ENDING AT ('2007-08-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 ,  PARTITION DATAPATITION8 STARTING FROM ('2007-08-01') INCLUSIVE ENDING AT ('2007-09-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION9 STARTING FROM ('2007-09-01') INCLUSIVE ENDING AT ('2007-10-01') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 , PARTITION DATAPATITION10 STARTING FROM ('2007-10-01') INCLUSIVE ENDING AT ('2007-11-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION DATAPATITION11 STARTING FROM ('2007-11-01') INCLUSIVE ENDING AT ('2007-12-01') EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 , PARTITION DATAPATITION12 STARTING FROM ('2007-12-01') INCLUSIVE ENDING AT ('2007-12-31') EXCLUSIVE IN TABLESPACE3 LONG IN LARGETBS3 ,PARTITION OTHERS1 STARTING FROM (MINVALUE) EXCLUSIVE ENDING AT ('2007-01-01') EXCLUSIVE IN TABLESPACE1 LONG IN LARGETBS1 , PARTITION OTHERS2 STARTING FROM ('2008-01-01') INCLUSIVE ENDING AT (MAXVALUE) EXCLUSIVE IN TABLESPACE2 LONG IN LARGETBS2 ) INDEX IN USERSPACE1
DB20000I  SQL命令成功完成｡

此时我们再次插入清单15中所插入的值,就可以成功了,具体如清单18所示:

- - 清单18 .对示例分区表TEST3插入一条记录

C:\> DB2 INSERT INTO TEST3 VALUES (1,'2006-01-10' , blob ( ' the first rows ' ) )
DB20000I SQL命令成功完成｡

对清单10中,把数值型的列作为表分区键,也可以用手工的方式进行创建,具体如清单19所示:

- - 清单19 .创建示例分区表TEST4

C:\> DB2 CREATE TABLE RHETTE.TEST4 ( COL1 INTEGER NOT NULL , COL2 BIGINT NOT NULL ) PARTITION BY RANGE ( COL2 NULLS LAST ) ( PARTITION DATAPARTION1 STARTING FROM ( 1 ) INCLUSIVE ENDING AT ( 10 ) INCLUSIVE IN TABLESPACE1 , PARTITION DATAPARTITION2 STARTING FROM ( 11 ) INCLUSIVE ENDING AT ( 20 ) INCLUSIVE )
DB20000I SQL命令成功完成｡

使用手工方式创建分区表可以将多个列用作表分区键,比如我们在DB2CLP窗口中创建示例分区表TEST5,具体如清单20所示:

- - 清单20 .创建示例分区表TEST5

C:\> DB2 CREATE TABLE TEST5 ( year INT , month INT ) PARTITION BY RANGE( year , month ) ( STARTING FROM ( 2007 , 1 ) ENDING ( 2007 , 3 ) IN TABLESPACE1 , ENDING ( 2007 , 6 ) IN TABLESPACE2 , ENDING ( 2007 , 9 ) IN TABLESPACE3 )
DB20000I SQL命令成功完成｡

命令成功完成,这样我们就成功地创建了示例分区表TEST5,其包含3个数据分区,即2007年前3个季度,每个季度一个数据分区｡需要注意的是,当将多个列用作表分区键时,将把这些列视为组合键(类似于索引中的组合键),其中,后面的列依赖于前面的列｡指定的每个起始值或结束值(所有列一起)不能超出512个字符｡此限制与 SYSCAT.DATAPARTITIONS目录视图中的LOWVALUE和HIGHVALUE 列大小对应｡如果指定超出512个字符的起始值或结束值,就会导致错误SQL0636N,原因码为9｡表分区是多列的,而不是多维的｡在表分区中,使用的所有列都包含在单个维中｡
另外,还可以将生成列用作表分区键｡接下来我们在DB2CLP窗口中,创建示例分区表TEST6,其包含 12 个数据分区的表,即每个月一个数据分区｡对于任何年份,一月份的所有行都将被放到第一个数据分区中,二月份的行将被放到第二个数据分区中,依此类推,具体如清单21所示:
- - 清单21 .创建示例分区表TEST6

C:\> DB2 CREATE TABLE TEST6 ( RQ date,YF int GENERATED ALWAYS AS ( month( RQ ) ) ) PARTITION BY RANGE ( YF ) (STARTING FROM 1 ENDING AT 12 EVERY 1 )
DB20000I SQL命令成功完成｡

命令成功完成,这样我们成功创建了示例分区表TEST6,需要注意的是对于表分区键中使用的生成列,不能改变或删除其表达式｡不允许对表分区键中使用的列添加生成列表达式｡对于表分区键中使用的列,如果尝试添加､删除或改变该列的生成列表达式,就会导致错误(SQL0270N,原因码为52)｡

第1页：简介第2页：创建示例数据库DB2TEST1 第3页：使用自动方法创建示例分区表第4页：使用手工方式创建示例分区表

关注我们