V8.2中的表分区 [英] Table partitioning in V8.2
问题描述
DB2 V8.2（还没有Viper而且没有范围分区!!）
我创建了一个表格T1（col1，col2），其中col1作为主键。
当我尝试在col2上创建一个分区键时，它给出了我的错误
应该包含所有主键。
所以，我创建了表格T1再次以col2作为分区键。
现在，我没有col1作为主键。
当我尝试创建col1作为主键时，我得到以下错误：
1主键，每个唯一约束和每个唯一索引
必须包含表的所有分区列（列可能是
以任何顺序出现。）
另外，在
分区之前我应该看到的其他注意事项是什么？我在SMS表空间中创建上表。
有没有办法用名称或
来识别特定的分区？
请告诉我....我是这个分区的新手。
干杯，
San。
shsandeep写道：
DB2 V8.2（还不是Viper）并且没有范围分区!!）
我创建了一个表格T1（col1，col2），其中col1作为主键。
当我尝试创建分区键时col2，它给了我错误它
应该包含所有主键。
分区键需要包含主键，这样如果你b $ b尝试插入一个主键重复值的行，它是
保证被路由到系统中的分区/节点
已经拥有该主键的现有行。
例如假设您有T1（col1，col2）与主键（col1）和
分区键（col1）。假设表中有一行（1,1），
节点4（只是为了组成一个节点号）。然后，如果你试图插入
行（1,2），它就会被发送到节点4（因为那个值为1，因为
col1被哈希到之前，它将始终被散列到节点4）。然后，节点4上的DB2子代理进程将检测到您正在尝试
插入重复值并返回错误。
如果DB2没有这个限制，任何INSERT进入分区的
表可能需要检查每一行的每一行以确保它
是不重复。实际上，可以拆分许多行的INSERT，
并且每个节点可以检查
并行中主键的重复值。
听起来你真的希望你的主键是col1。也许（col1）或（col1，col2）的分区键可以满足您的需求吗？
感谢Harold你的回复。
当我尝试在（col1，col2）上创建一个分配键时，它给了我
以下错误：
SQL0270N功能不支持（原因代码=" 1
"）。
说明：
声明不能处理，因为它违反了
限制，如以下原因代码所示：
1主键，每个唯一约束和每个唯一索引
必须包含表格的所有分区列（列可以按任何顺序显示
）。
我已将col1定义为主键。
干杯，
圣。
忘记提及：
它是一个单独的分区数据库。我想知道这将如何帮助
以任何方式。
干杯，
San。
DB2 V8.2 (not Viper yet and no range partitioning!!)
I have created a table T1 (col1, col2) with col1 as the primary key.
When I try to create a partitioning key on col2, it gives me error that it
should have all primary keys included.
So, I created table T1 again with col2 as the partitioning key.
Now, I do not have col1 as the primary key.
When I try to create col1 as the primary key, I get the following error:
1 The primary key, each unique constraint, and each unique index
must contain all partitioning columns of the table (columns may
appear in any order).
Also, what are the other considerations that I should see before
partitioning? I am creating the above table in a SMS tablespace.
Is there any way to identify the particular partition with a name or
something?
Please let me know.... am a newbie to this partitioning thing.
Cheers,
San.
shsandeep wrote:DB2 V8.2 (not Viper yet and no range partitioning!!)
I have created a table T1 (col1, col2) with col1 as the primary key.
When I try to create a partitioning key on col2, it gives me error that it
should have all primary keys included.The partitioning key needs to contain the primary key so that if you
try to insert a row with a duplicate value for the primary key, it is
guaranteed to be routed to the partition/node in the system that
already has the existing row with that primary key.
e.g. Say you have T1 (col1, col2) with primary key (col1) and
partitioning key (col1). Suppose you have a row (1, 1) in the table, on
node 4 (just to make up a node number). Then if you try to insert the
row (1, 2) it is sent to node 4 (because that''s where a value of 1 for
col1 was hashed to before, it will always be hashed to node 4). Then,
the DB2 subagent process on node 4 will detect that you''re trying to
insert a duplicate value and return an error.
If DB2 did not have this restriction, any INSERT into a partitioned
table might need to check with every node for each row to make sure it
is not a duplicate. As it is, an INSERT of many rows can be split up,
and each node can check for duplicate values for the primary key in
parallel.
It sounds like you really want your primary key to be col1. Maybe a
partitioning key of (col1) or (col1,col2) would work for your needs?
Thanks Harold for your reply.
When I try to create a partioning key on (col1,col2) it gives me the
following error:
SQL0270N Function not supported (Reason code = "1
").
Explanation:
The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
1 The primary key, each unique constraint, and each unique index
must contain all partitioning columns of the table (columns may
appear in any order).
I have defined col1 as the primary key.
Cheers,
San.
Forgot to mention this:
It is a single partitioned database. Am wondering as to how this will help
in any manner.
Cheers,
San.
这篇关于V8.2中的表分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
