DB2表分区详解
DB2 数据库认证考试614的题目：
No4. A table named SALES was created as follows:
create table sales(order_date date ,order_id int ) in ts1,ts2,ts3,ts4
partition by range(order_date)
(starting '2012-01-01' ending '2012-12-31'
every 3 months )
**Assuming all table spaces required already exist, which statement(s) must be executed to
add a new partition that already contains data for the first quarter of 2013 to the SALES
table and leave the table accessible?**
A. ALTER TABLE sales ADD PARTITION part_2013q1
STARTING ('01/01/2013') ENDING ('03/31/2013') IN ts5;
B. ALTER TABLE sales ATTACH PARTITION part_2013q1
STARTING ('01/01/2013') ENDING ('03/31/2013')
FROM TABLE q12013_sales;
C. ALTER TABLE sales ADD PARTITION part_2013q1
STARTING ('01/01/2013') ENDING ('03/31/2013') IN ts5;
SET INTEGRITY FOR sales IMMEDIATE CHECKED;
D. ALTER TABLE sales ATTACH PARTITION part_2013q1
STARTING ('01/01/2013') ENDING ('03/31/2013')
FROM TABLE q12013_sales;
SET INTEGRITY FOR sales IMMEDIATE CHECKED;
本题涉及DB2分区表相关知识
概述
CREATE TABLE 语句的 PARTITION BY 子句指定了表数据的分区。该定义中使用的列被称为表分区键列。
表分区特性提供以下收益：
表数据可轻易实现转入和转出
对大型表的管理更加轻松
灵活的索引放置
更高的业务智能样式查询的性能
创建分区表
a.创建基本分区表
1.使用如下的数据定义语言（DDL）创建具有四个范围的 LINEITEM 表：
db2 connect to SAMPLE --首先连接到数据库
执行下面DDL建表语句：
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/06/1993',
STARTING '1/7/1993' ENDING '31/12/1993');
2.使用下面的命令说明为 LINEITEM 表创建的分区的范围：
db2 describe data partitions for table LINEITEM
下图说明为 LINEITEM 表创建的分区范围：
注意：创建了四个数据分区。其中的范围包括边界值。
3.将数据导入到 LINEITEM 表。该操作的导入命令:
import from 'lineitem.tbl' of del
modified by coldel|
commitcount 1000
insert into lineitem;下图是将数据导入到 LINEITEM 表：
注意：导入时拒绝了 729 行数据，这是因为它们不具有位于当前 LINEITEM 表的数据分区定义范围内的 l_shipdate。
4.标量函数可用于显示行所属的数据分区号（datapartitionnum）。执行以下示例 SQL 查看标量函数的输出
查询 - 匹配日期的分区
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”
标量函数的输出
注意：标量函数（datapartitionnum）返回的值和 describe 命令返回的是同一个 PartitionId。该语句的子句间使用的谓词范围超出了 PartitionId 0 和 PartitionId 1 的边界
b.具有全部范围的分区表
1. 创建具有两个额外数据分区的新 LINEITEM 表，其中一个分区用来捕获低于当前范围的值，另一个分区用来捕获高于当前范围的值。
首先使用下面的命令删除现有的 LINEITEM 分区表：
db2 drop TABLE LINEITEM
然后使用如下 DDL 创建 LINEITEM 表的新版本：
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 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 );
2.使用下面的命令说明为 LINEITEM 表创建的分区范围。
db2 describe data partitions for table LINEITEM
下图说明为 LINEITEM 表创建的分区范围：
注意：新的 MINVALUE 范围具有一个最高值，该值和下一个数据分区开始部分的值相等，但它并不包含该值。MAXVALUE 范围具有一个最低值，该值和前一个范围结束部分的值相等，但它不包含该值。这将创建一个无间隙的连续范围。
3.将数据导入到 LINEITEM 表中。该操作的导入命令位于 EX1-8.sql 文件中，可使用下面的命令运行该文件：
import from 'lineitem.tbl' of del
modified by coldel|
commitcount 1000
insert into lineitem;
下图显示将数据导入到 LINEITEM 表中
c.具有生成范围的分区表
1.创建一个新 LINEITEM 表，它具有从 1992 年 1 月 1 日到 1998 年 12 月 31 日按月划分的数据分区生成范围。同样，添加 minvalue 和 maxvalue 范围来存放具有超过此范围的 l_shipdate 的值的行。首先使用以下命令删除现有的 LINEITEM 分区表：
db2 drop TABLE LINEITEM
然后使用如下 DDL 创建 LINEITEM 表的新版本：
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 MINVALUE,
STARTING '1/1/1992' ENDING '31/12/1998' EVERY 1 MONTH,
ENDING MAXVALUE);
2.使用如下命令来说明为 LINEITEM 表创建的分区范围：
db2 describe data partitions for table LINEITEM
下图 说明为 LINEITEM 表创建的分区范围
注意：创建了 86 个数据分区，但是没有包括这些范围的最高值，因为这些最高值将和之后的数据分区的最低值重叠。
4.将数据导入到 LINEITEM 表。可使用下面的命令运行该文件：
import from 'lineitem.tbl' of del
modified by coldel|
commitcount 1000
insert into lineitem;
下图将数据导入到 LINEITEM 表
使用下面的 SQL 来验证 LINEITEM 表每一个数据分区的行数：
db2 “select year(l_shipdate) as year, month(l_shipdate) as month,
count(*) as count from lineitem
group by year(l_shipdate), month(l_shipdate)
order by 1, 2”
下图校验行数：
注意：执行加载操作后，86 个范围中有 82 个范围包含一个或多个行。
放置分区表
下面探讨放置分区表以及使用 describe 命令说明表内的范围和放置情况的方法；
基本环境设置
1.创建五个表空间来说明不同的放置选项
db2 create tablespace dms_d1 managed by automatic storage ;
db2 create tablespace dms_d2 managed by automatic storage;
db2 create tablespace dms_d3 managed by automatic storage;
db2 create tablespace dms_d4 managed by automatic storage;
db2 create tablespace dms_d5 managed by automatic storage;
创建一个新的 LINEITEM 表，具有位于 dms_d1 和 dms_d2 表空间的生成分区集。
首先，使用如下命令删除现有的 LINEITEM 分区表：
db2 drop TABLE LINEITEM
然后，使用下面的 DDL 创建 LINEITEM 表的新版本：
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))
IN DMS_D1, DMS_D2
PARTITION BY RANGE(l_shipdate)
(STARTING MINVALUE,
STARTING '1/1/1992'
ENDING '31/12/1998' EVERY 1 MONTH,
ENDING MAXVALUE);
使用下面的命令说明为 LINEITEM 表创建的分区范围：
db2 describe data partitions for table LINEITEM show detail
下图 说明为 LINEITEM 表创建的分区范围
注意：TableSpID 列给出了包含分区的表空间的 ID 号。在本例中，TableSpID 为 4（对应于 DMS_D1）或 5（对应于 DMS_D2）。本例中将生成的分区依次分配给指定的表空间。
分区的显式放置
1.创建一个具有四个数据分区的新 LINEITEM 表，每一个数据分区被显式地放在表空间中。首先使用如下命令删除现有的 LINEITEM 表：
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 minvalue in dms_d1,
STARTING '1/1/1992' ENDING '31/12/1992' in dms_d2,
STARTING '1/1/1993' ENDING '31/12/1993' in dms_d3 ,
ending maxvalue in dms_d4 );
用下面的命令说明为 LINEITEM 表创建的分区范围：
db2 describe data partitions for table LINEITEM show detail
下图为 LINEITEM 表创建的分区范围
注意：在本例中，每一个分区被放置在一个不同的 TableSpID 中，这个 TableSpID 和创建表的 DDL 中指定的表空间是相对应的。
2.创建一个具有四个数据分区的 LINEITEM 表，每一个数据分区被显式地放在表空间并且索引被放在表空间 DMS_I1 中。 在这一步中，将引入命名分区的概念，而不是使用默认的生成名称。 首先，使用下面的命令删除现有的 LINEITEM 分区表：
db2 drop TABLE LINEITEM
然后，使用以下的 DDL 创建 LINEITEM 表的新版本：
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)
)
INDEX IN DMS_I1
PARTITION BY RANGE(l_shipdate)
( PART JAN1992 STARTING '1/1/1992' ENDING '30/06/1992' in dms_d1,
PART JULY1992 STARTING '1/7/1992' ENDING '31/12/1992' in dms_d2,
PART JAN1993 STARTING '1/1/1993' ENDING '30/06/1993' in dms_d3,
PART JULY1993 STARTING '1/7/1993' ENDING '31/12/1993' in dms_d4);
3.在 LINEITEM 表中创建一个索引，并将它放置在表空间 DMS_I1 中。使用如下 SQL：
db2 “create index I_LINEITEM on LINEITEM(L_SHIPDATE) in DMS_I1”
4. 使用下面的 SQL 检验和该表相关联的索引的位置：
db2 “select tabname, index_tbspace from syscat.tables where tabname = ‘LINEITEM’”
下图检验索引的位置
注意：索引空间是 DMS_I1。如果没有为分区表指定表空间，那么默认情况下索引位于连接着的第一个表空间。 在 CREATE TABLE 中定义表空间是很好的实践。然而，无论您是否在创建表语句 ID 中指定索引表空间，这并不限制您将来放置索引的位置。您可以在 CREATE INDEX 语句本身显式地指定索引表空间。 同一分区表的不同索引可以放置在不同的表空间。
管理分区表
使用现有的表添加一个新的分区
1.将数据导入 LINEITEM 表。
import from EX3oldrange.ixf of ixf
insert into lineitem;
将数据导入到 LINEITEM 表
注意：分区表中的记录数，以及将进行连接和分离的记录数对于说明数据库中的数据何时可用非常重要。
2.创建一个名为 NP_LINEITEM 的新表。
CREATE TABLE NP_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/06/1993',
STARTING '1/7/1993' ENDING '31/12/1993');
将数据导入到 NP_LINEITEM 表
import from EX3newrange.ixf of ixf
replace_create into np_lineitem;
3.使用下面的命令说明为 LINEITEM 表创建的分区范围：
db2 describe data partitions for table LINEITEM show detail
下图所示为 LINEITEM 表的分区 ：
注意：LINEITEM 表目前包含 4 个数据分区。
4. 使用 Alter 语句将一个新的分区连接（转入）到现有的 LINEITEM 表。
alter table lineitem attach partition JAN1994
STARTING '1/1/1994' ENDING '30/6/1994'
from np_lineitem;
注意：LINEITEM 表被置于 SET INTEGRITY PENDING 状态。
5.连接后，使用 describe data partitions 命令来查看 LINEITEM 表创建的分区范围：
db2 describe data partitions for table LINEITEM show detail
下图所示为 LINEITEM 表的分区
注意：新数据分区（JAN1994）PartitionId 4 现在连接到了 LINEITEM 表。然而连接的分区的 AccessMode 值为 ‘N’ 并且 Status 的值为 ‘A’。 AccessMode 可能的值有：
D = 没有数据移动
F = 完全访问
N = 不访问
R = 只读访问
Status 可能的值有：
A = 数据分区是新连接的
D = 数据分区是分离的
I = 只有在执行异步索引清除时才对条目位于目录的分离的数据分区进行维护；当所有引用分离数据分区的索引记录删除后，将删除 STATUS 值为 ‘I’ 的行。
Empty string = 数据分区是可见的（普通状态）
6.运行两个 select count 语句来检查连接语句涉及的两个表中数据的可用性。
db2 “select count(*) from lineitem”
下图为select count 语句的结果
注意：LINEITEM 表最初的分区是可用的，但是 PartitionId 4 中的新数据仍不可见。
db2 “select count(*) from np_lineitem”
下图为select count 语句的结果
注意：NP_LINEITEM 表现在是一个未定义的对象，在 LINEITEM 表内只能将其作为一个分区使用。
7.创建一个异常表并与 SET INTEGRITY 语句结合使用。
异常表创建：
CREATE TABLE lineitem_ex
(
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)
);
8.对 LINEITEM 分区表运行 set integrity 语句， 设置完整性：
set integrity for lineitem allow write access
immediate checked for exception in lineitem use lineitem_ex;
commit work;
将上的命令存储在EX3-8.sql 文件中，然后执行
db2 -vtf EX3-8.sql
注意：SET INTEGRITY 对于检查新连接的数据是否在范围内是必需的，它还执行对索引和其他独立对象（例如物化查询表）所有必需的维护工作。只有得到 SET INTEGRITY 语句的允许，新的数据才能变为可见。然而，当运行 SET INTEGRITY 时，可以对 LINEITEM 表中的现有数据进行完全访问，包括读和写操作。 用户应该执行 SET INTEGRITY 事务从而能够使用整个表。当运行 SET INTEGRITY 时，不能够对表执行 DDL 或其他实用类型的操作。 在这个练习中，在 NP_LINEITEM 表中创建并被连接到 LINEITEM 表的所有的行，都在连接语句指定的范围内。如果这些行中存在超出此范围的行，则需要在 SET INTEGRITY 语句中创建一个异常表来防止语句发生错误。所以推荐您始终在 SET INTEGRITY 语句中包含一个异常表。如果没有提供异常表的话，SET INTEGRITY 语句发现的错误将导致语句失败并且所有的工作都必须从头做起。如果使用大量数据时，这可能是一个长期操作。有一点值得注意，如果 SET INTEGRITY 操作失败，所有工作都需要重做，与之相比较，LOAD 仅仅抛弃存在问题的行。
9.对 LINEITEM 表运行 select count SQL 以检查连接的分区中数据的可用性：
db2 “select count(*) from lineitem”
下图为 select count 语句的结果
注意：成功执行 SET INTEGRITY 操作后，LINEITEM 表应包含 PartitionId 4 的数据。
从分区表中分离一个分区
1.使用 describe data partitions show detail 命令来标识一个分区的 PartitionName，您将把这个分区从 LINEITEM 分区表中分离（转出）出来。
db2 describe data partitions for table LINEITEM show detail
如下图
注意：将分离最早的分区范围 PartitionId 0。该分区的 PartitionName 是 JAN1992。将在 DETACH 操作中使用它来标识被转出的分区。同样还需注意成功执行了 SET INTEGRITY 操作后，分区 JAN1994 的 AccessMode 的值为 ‘F’，Status 值为空。TableSpId、PartObjId 和 LongTblSpId 的结果可能和这里显示的不一样。
2.使用 Alter 语句将 JAN1992 从 LINEITEM 表中分离（转出）。
ALTER TABLE LINEITEM DETACH PARTITION JAN1992 INTO LINEITEM_JAN1992
注意：将 JAN1992 成功分离后，将创建一个新的表 LINEITEM_JAN1992。在 DETACH 操作中没有涉及数据移动，并且位于相同表空间的新表的行为和它作为 LINEITEM 分区表的一部分时是一样的。此时不需要对 LINEITEM 表运行 SET INTEGRITY 语句，因为没有对 LINEITEM 表定义的 MQTs。 还有一点值得注意，如果从 Multi-Dimensional Clustering（MDC）分离一个分区从而创建了一个新表时，这个表也将是一个 MDC。这个规则同样适用于下面这个情况：从一个分布式表中分离分区从而在相同的分区组创建分布式表。执行 DETACH 操作后产生的表使用 MDC 索引定义而不是其他的索引。对于 MDC，在首次访问连接的表时将重新构建索引。在这种情况下，将自动对分离的分区进行索引清除操作。将从执行 DETACH 操作的用户 ID 继承索引的模式、权限和表空间。
运行两个 select count 语句检查 DETACH 语句涉及的两个表中的数据的可用性。
db2 “select count(*) from lineitem_jan1992”
select count 语句的结果
注意：创建的 LINEITEM_JAN1992 表包含 38 行，它被包含在 LINEITEM 分区表的 JAN1992 分区中。
db2 “select count(*) from lineitem”
下图为select count 语句的结果:
注意：此时 LINEITEM 表完全可用，并且不包括 PART0 中的数据。
4.当数据被移动到分区表中，或当希望将数据加载到或直接插入分区表中时，一个更合适的方法是向现有的分区表添加一个空的分区。使用下面的命令向现有的 LINEITEM 表添加一个空的分区：
db2 “ALTER TABLE LINEITEM ADD PARTITION JULY1994
STARTING '1/7/1994' ENDING '31/12/1994'”
下图为向现有的 LINEITEM 表添加一个空的分区
5.使用 describe data partitions show detail 命令来检验 PartitionName 为 JULY1994 的分区是否被添加到 LINEITEM 中：
db2 describe data partitions for table LINEITEM show detail
下图为 LINEITEM 表的分区：
分区表的访问计划
1. 对 LINEITEM 表执行 RUNSTATS 操作：
db2 runstats on table db2inst1.lineitem
2.以下 SQL 语句并检查说明输出：
db2 “select l_shipdate,sum(l_quantity) from LINEITEM group by l_shipdate”
3.将上面SQL存储到 EX4-2.sql 文件，然后执行：
db2expln –d SAMPLE –t –f EX4-2.sql
注意：该 SQL 执行了 LINEITEM 表的索引扫描。说明输出中有一个关于表分区的要点需要注意，所访问的表是被分区的，并且在扫描过程中所有数据分区都将被访问。
4.下面的 SQL 语句并检查说明输出：
db2 “select l_shipdate, l_partkey, l_returnflag
from LINEITEM
where l_shipdate between '01/01/1993' and '31/08/1993'
and l_partkey = 49981”
5.将该SQL 语句存储到 EX4-3.sql 文件中，可使用下面的命令运行该文件：
db2expln –d SAMPLE –t –f EX4-3.sql
下图说明输出
注意：这个 SQL 语句执行了 LINEITEM 表的索引扫描。在本例中，可以看到优化器能够执行数据分区排除操作。在说明输出中要注意的是关于表分区，访问的表是被分区的，将执行分区排除功能以及删除活动数据分区的值。 在本例中，活动的数据分区为 1-2。这里引用的是 syscat.datapartitions 中的序列号（seqno）而不是 describe data partitions 命令中的 PartitionId。
6.使用下面的 SQL 确定在前面说明示例中活动的分区的名称：
db2 “select seqno,datapartitionname
from syscat.datapartitions
where tabname = ‘LINEITEM’ order by seqno”
下图为分区名称
注意：序列号 1 和 2 分别映射的是 JAN1993 和 JULY1993 分区名称。
本题考察的是如何将已经存有数据的表添加到分区表内进而作为其一个分区使用；
管理分区表---使用现有表添加一个新分区(attach)---然后执行SET INTEGRITY检查完整性；
参考文献
Information Management
数据文件下载地址
声明：版权所有非经授权请勿做任何商业用途，仅限技术交流！
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞14作者其他文章
评论 1 · 赞 3
评论 3 · 赞 2
评论 1 · 赞 5
评论 0 · 赞 1
评论 1 · 赞 2
添加新评论2 条评论
2017-09-12 17:47
2017-09-09 22:55