重现步骤以及方法如下:
重现步骤:
db2 "drop table t1"
db2 "create table t1 (col1 int, col2 int ) partition by range (col2) (part p1 starting 1 ending 100000, part p2 starting 100001 ending 200000, part p3 starting 200001 ending 300000)"
db2 "create unique index t1_idx1 on t1(col1)"
seq 1 300000 > a.del
seq 1 300000 > b.del
paste -d ',' a.del b.del > t1.del
db2 "import from t1.del of del commitcount 10000 insert into t1"
db2 "alter table t1 DETACH PARTITION p1 into temp1"
这时候立刻查看 syscat.datapartitions ,可以发现有一个分区名 SQL220411141248710, 其status为I , 表示正在做异步索引清理
db2tst@NODE01:~> db2 "select substr(datapartitionname, 1, 30) as datapartitionname,substr( tabschema, 1, 30) as tabschema, substr(tabname, 1, 30) as tabname, status from syscat.datapartitions where tabname='T1'"
DATAPARTITIONNAME TABSCHEMA TABNAME STATUS
------------------------------ ------------------------------ ------------------------------ --------------------------------
SQL220411141248710 DB2TST T1 I
P2 DB2TST T1
P3 DB2TST T1
3 record(s) selected.
稍等片刻,再次查询syscat.datapartitions ,发现该只剩两个分区,表明已经做完异步索引清理。
db2tst@NODE01:~> db2 "select substr(datapartitionname, 1, 30) as datapartitionname,substr( tabschema, 1, 30) as tabschema, substr(tabname, 1, 30) as tabname, status from syscat.datapartitions where tabname='T1'"
DATAPARTITIONNAME TABSCHEMA TABNAME STATUS
------------------------------ ------------------------------ ------------------------------ --------------------------------
P2 DB2TST T1
P3 DB2TST T1
2 record(s) selected.