问题描述
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. 00001K00023MIAOQINGSONG.T2.IDCHECK 100 25 2017-05-27-14.48.30. 00001K00023MIAOQINGSONG.T2.IDCHECK 2 record(s) selected.
参考资料
http://www-01.ibm.com/support/docview.wss?uid=swg
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001111.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/61809.html