DB2中SQL0668N Reason code "1"以及SQL3603N报错的处理

问题描述

DB2中，在load操作或者attatch操作之后，由于这些操作可能不会检查某些约束，所以表可能会被置于set integrity pending的状态(SQL0668N, RC='1')，在对其进行set integrity操作的时候，又可能会遇到SQL3603N的报错。这时候该如何处理呢？

重现问题

$ cat t2.del
99,25
101,26
100,25
  
$ db2 "create table t2(id int not null, age int, constraint idcheck check(id > 100))"
DB20000I  The SQL command completed successfully.

$ db2 "load from t2.del of del insert into t2 nonrecoverable"

..

Number of rows read         = 3
Number of rows skipped      = 0
Number of rows loaded       = 3
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 3

$ db2 "select * from t2"

ID          AGE
----------- -----------
SQL0668N  Operation not allowed for reason code "1" on table
"MIAOQINGSONG.T2".  SQLSTATE=57016

$ db2 set integrity for t2 immediate checked
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL3603N  Integrity processing through the SET INTEGRITY statement has found
an integrity violation involving a constraint, a unique index, a generated
column, or an index over an XML column. The associated object is identified by
"MIAOQINGSONG.T2.IDCHECK".  SQLSTATE=23514

$ db2 "select TYPE, ENFORCED from SYSCAT.TABCONST where CONSTNAME='IDCHECK'"

TYPE ENFORCED
---- --------
K    Y

  1 record(s) selected.

F = Foreign key
I = Functional dependency
K = Check
P = Primary key
U = Unique  

问题解决

解决的办法之一，是创建一张exception table，然后在set integrity时指定这张表：

$ db2 "create table t2_exp(id int not null, age int, etime TIMESTAMP, desc CLOB(32K))" 
DB20000I  The SQL command completed successfully.

$ db2 "set integrity for t2 immediate checked for exception in t2 use t2_exp"
SQL3602W  Check data processing found constraint violations and moved them to
exception tables.  SQLSTATE=01603

$ db2 "select * from t2"

ID          AGE
----------- -----------
        101          26

  1 record(s) selected.


$ db2 "select id, age, etime,substr(desc,1,40)as desc from t2_exp"

ID          AGE         ETIME                      DESC
----------- ----------- -------------------------- ----------------------------------------
         99          25 2017-05-27-14.48.30.329000 00001K00023MIAOQINGSONG.T2.IDCHECK
        100          25 2017-05-27-14.48.30.329000 00001K00023MIAOQINGSONG.T2.IDCHECK

  2 record(s) selected.

参考资料

http://www-01.ibm.com/support/docview.wss?uid=swg21648713
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001111.html