DB2: 分区表Detach出来的表查询记录数为0

问题现象：

DB2中有一个分区表，每个分区都有数据，但将其中一个分区detach出来成为一个新表，查询该新表发现数据量为0，后来发现原因是原分区表上有column access control，因此detach出来的表默认是有row access control的。

这也说明一个坑：就是查询出来的某张表记录数为0，不代表这个表中没有数据，还有另外一种可能就是它开启了row access control

重现步骤：

$ db2 "connect to tstdb"   
$ db2 "drop table t1"     
$ db2 "create table t1 (id int, age int) partition by range(age)(partition part1 starting 1 ending 18, partition part2 starting 19 ending 60, partition part3 starting 61 ending 80)"
$ db2 "alter table t1 ACTIVATE COLUMN ACCESS CONTROL"
$ db2 "insert into t1 values (1,2),(2,8),(3,10),(4,20),(5,22),(6,61)"
$ db2 "alter table t1 detach partition part1 into t1_part1"
$ db2 "select * from t1"

ID          AGE       
----------- -----------
          4          20
          5          22
          6          61

  3 record(s) selected.
 
$ db2 "select * from t1_part1"

ID          AGE       
----------- -----------

  0 record(s) selected.

 

原因分析：

根据信息中心DETACH PARTITION的描述，原因是源表开启了 access control，导致detach出来的新表默认也开启了access control

DETACH PARTITION partition-name INTO table-name1

Detaches the data partition partition-name from the altered table, and uses the data partition to create a new table named table-name1. The data partition is detached from the altered table and is used to create the new table without any data movement. The specified data partition cannot be the last remaining partition of the table being altered (SQLSTATE 428G2). The table being altered to detach a partition must not be a system-period temporal table (SQLSTATE 428HZ).

When a partition is detached from a table for which either row level access control or column level access control is defined, the new table that is created for the detached data will automatically have row level access control (though not column level access control) activated to protect the detached data. Direct access to this new table will return no rows until appropriate row permissions are defined for the table or row level access control is deactivated for this table.

 

如果使用db2look查看 t1_part1 定义，会发现该表开启了 row level access control

$ db2look -d tstdb -a -e -t t1_part1
...

CREATE TABLE "DB2TST  "."T1_PART1"  (
                  "ID" INTEGER ,
                  "AGE" INTEGER )   
                 IN "MB_DATA_32" 
                 ORGANIZE BY ROW;

ALTER TABLE "DB2TST  "."T1_PART1" ACTIVATE ROW ACCESS CONTROL;

 

解决方法：

将detach出来的表的Access control关闭就行了：

$ db2 "alter table DB2TST.T1_PART1 DEACTIVATE ROW ACCESS CONTROL"
DB20000I  The SQL command completed successfully.
$ db2 "select * from T1_PART1"

ID          AGE       
----------- -----------
          1           2
          2           8
          3          10

  3 record(s) selected.