DB2 DPF环境，load报错SQL3604N Reason code: “2“. sqlstate: 428A5

问题现象：

DB2 DPF环境，load报错SQL3604N Reason code: "2". sqlstate: 428A5

 

重现过程：

db2dpf@node01:~> db2 "create table t1(id int, age int, heigh int) DISTRIBUTE BY HASH(id,age)"
DB20000I  The SQL command completed successfully.
db2dpf@node01:~> db2 "create table t1_exp like t1"
DB20000I  The SQL command completed successfully.
db2dpf@node01:~> db2 "alter table t1_exp add column col1 timestamp add column col2 clob(32k)"
DB20000I  The SQL command completed successfully.
db2dpf@node01:~> db2 "load from t1.del of del  replace into t1 for exception t1_exp"
SQL3604N  The statement or command failed because the following exception 
table is invalid: "T1_EXP". Table to which the exception table corresponds: 
"T1". Reason code: "2".

原因分析：

SQL3604N Reason code: "2". 表示exception表的column structure有问题：
The exception table does not have the proper column structure.

来看一下t1_exp表的定义，可以看到分布键只有ID,和原表t1不同。

db2dpf@node01:~> db2look -d sample -a -e -t t1_exp

CONNECT TO SAMPLE;

CREATE TABLE "DB2DPF  "."T1_EXP"  (
                  "ID" INTEGER , 
                  "AGE" INTEGER , 
                  "HEIGH" INTEGER , 
                  "COL1" TIMESTAMP , 
                  "COL2" CLOB(32768 OCTETS) LOGGED NOT COMPACT )   
                 DISTRIBUTE BY HASH("ID")   
                   IN "TBS1"  
                 ORGANIZE BY ROW; 

COMMIT WORK;

CONNECT RESET;

TERMINATE;

问题解决：

不建议使用create table like去创建相同的结构的表，它建的新表的分布键(DPF环境)可能和原表不一样。  另外，如果原表是分区表，新表就不是分区表了。

正确的方法是使用完整的建表语句来建exception表
db2dpf@node01:~> db2 "drop table t1_exp"
DB20000I  The SQL command completed successfully.
db2dpf@node01:~> db2 "create table t1_exp(id int, age int, heigh int, col1 timestamp, col2 clob(32k)) DISTRIBUTE BY HASH(id,age)"
DB20000I  The SQL command completed successfully.
db2dpf@node01:~> db2 "load from t1.del of del  replace into t1 for exception t1_exp"

  Agent Type     Node     SQL Code     Result
______________________________________________________________________________
  LOAD           001      +00000000    Success.
______________________________________________________________________________
  LOAD           002      +00000000    Success.
______________________________________________________________________________
  PARTITION      000      +00000000    Success.
______________________________________________________________________________
  PRE_PARTITION  000      +00000000    Success.
______________________________________________________________________________
  RESULTS:       2 of 2 LOADs completed successfully.
______________________________________________________________________________

Summary of Partitioning Agents:
Rows Read                   = 1
Rows Rejected               = 0
Rows Partitioned            = 1

Summary of LOAD Agents:
Number of rows read         = 1
Number of rows skipped      = 0
Number of rows loaded       = 1
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 1