对DB2分区表reorg时分区的访问模式
DB2数据库中有一张分区表（包含主键索引和部分分区索引），在对某个分区做reorg时，其它分区是否可以select或者update？ reorg的语句，如：db2 "reorg table wf_h_activityinst on datapartition P201701"
3同行回答
对于联机交易型系统，如果无法提供停机reorg窗口，在分区表设计阶段应该考虑将主键创建为分区索引。
默认情况下主键都是全局索引，可以先创建唯一性分区索引并且要包含分区键，然后添加主键，DB2自动会将这个唯一索引设置为主键约束。
感兴趣的可以测试下，如下命令供参考。
--创建唯一性索引
db2 \"create unique index idx_process_1 on process(id,dt) partitioned\" //必需包含分区键
--删除原来的主键
db2 \"alter table process drop primary key\"
--添加主键约束
db2 \"alter table process alter column dt set not null\" //主键所有列都是非空
db2 \"reorg table process\"
db2 \"alter table process add primary key (id,dt)\"
db2 describe indexes for table process //查看索引类型由U变为P
db2 \"reorg table process allow read access on data partition 20170101\" //reorg可以allow read access了
收起信息中心查到如下描述，分区表包含全局索引的情况下，对单个分区reorg也是allow no access模式。
When the ON DATA PARTITION clause is specified for a table reorganization of a data partitioned table, only the specified data partition is reorganized:
If there are no nonpartitioned indexes (except system-generated XML path indexes)defined on the table, the access mode applies only to the specified partition,users are allowed to read from and write to the other partitions of the table.
If there are nonpartitioned indexes defined on the table (excluding system-generated XML path indexes), the ALLOW NO ACCESS mode is the default and only supported access mode. In this case, the table is placed in ALLOW NO ACCESS mode. If ALLOW READ ACCESS is specified, SQL1548N is returned (SQLSTATE 5U047).
收起