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

本文探讨了在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.

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值