db2 DPF环境下表load pending状态解除

最新推荐文章于 2023-04-19 23:26:49 发布

原创最新推荐文章于 2023-04-19 23:26:49 发布 · 4.9k 阅读

9 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#DB2 #LOAD PENDING #SQL27902N #DPF

DB2 专栏收录该内容

181 篇文章

订阅专栏

本文探讨了在DPF环境中，当表在某些分区处于LoadPending状态，而其他分区正常时的解决方案。通过针对性地对问题分区执行loadfromdevnullofdelterminateintot1partitioneddbconfigoutput_nodes命令，成功解决了表状态异常问题。

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.