DPF环境下,某张表跨越多个分区,在某些分区上状态是normal的,其他分区上则是load pending 这种情况该如何处理?
示例:
dpf105@node01:~$ db2 "select count(*) from t1"
1
-----------
SQL0668N Operation not allowed for reason code "3" on table "DPF105.T1".
SQLSTATE=57016
dpf105@node01:~$ db2_all "db2 connect to sample && db2 load query table t1" | grep -i Tablestate -A 1
Tablestate:
Load Pending
--
Tablestate:
Normal
--
Tablestate:
Load Pending
--
Tablestate:
Normal
--
Tablestate:
Load Pending
dpf105@node01:~$ db2 "load from /dev/null of del terminate into t1"
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 000 +00000000 Success.
______________________________________________________________________________
LOAD 001 -00027902 Init error. Table unchanged.
______________________________________________________________________________
LOAD 002 +00000000 Success.
______________________________________________________________________________
LOAD 003 -00027902 Init error. Table unchanged.
______________________________________________________________________________
LOAD 004 +00000000 Success.
______________________________________________________________________________
RESULTS: 3 of 5 LOADs completed successfully.
______________________________________________________________________________
Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0
SQL27902N LOAD RESTART/TERMINATE is not allowed on a table that is not in
LOAD PENDING state.
从上面的示例可以看到表t1在分区1和3上是正常的,在0、2、4分区上是load pending,在尝试使用load xxx terminate into解除状态的时候报错,无法解除。
解决办法是仅针对有问题的分区发出load xxx terminate into命令:
dpf105@node01:~$ db2 "load from /dev/null of del terminate into t1 partitioned db config output_nodes (0,2,4)"
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 000 +00000000 Success.
______________________________________________________________________________
LOAD 002 +00000000 Success.
______________________________________________________________________________
LOAD 004 +00000000 Success.
______________________________________________________________________________
RESULTS: 3 of 3 LOADs completed successfully.
______________________________________________________________________________
Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0
dpf105@node01:~$ db2 "select count(*) from t1"
1
-----------
50000000
1 record(s) selected.