问题描述
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