Java Db2 .SqlIntegrityConstraintViolationException:SQLCODE=-803,SQLSTATE=23505
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/28826508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Db2 .SqlIntegrityConstraintViolationException: SQLCODE=-803, SQLSTATE=23505
提问by mavrav
I am reading from a table in Oracle and inserting the entire dump into Db2. The table structures are the same.I am using Simple scala class which does the above mentioned task. I have set the insert batchsize as 300. After a few batches gets updated, the class is throwing the below exception
我正在从 Oracle 中的一个表中读取数据并将整个转储插入到 Db2 中。表结构是相同的。我正在使用执行上述任务的 Simple scala 类。我已将插入批次大小设置为 300。更新几批后,该类抛出以下异常
com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #10: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;PME.TM_ASSET_LQA_DETL, DRIVER=4.13.127
at com.ibm.db2.jcc.am.id.a(id.java:673) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.am.id.a(id.java:60) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.am.id.a(id.java:127) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.t4.cb.a(cb.java:481) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.t4.cb.a(cb.java:70) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.t4.q.a(q.java:57) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.t4.tb.a(tb.java:225) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.am.oo.a(oo.java:3434) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.am.oo.d(oo.java:5550) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.am.oo.a(oo.java:4992) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.am.oo.c(oo.java:4664) ~[db2jcc-4.13.127.jar:na]
at com.ibm.db2.jcc.am.oo.executeBatch(oo.java:2934) ~[db2jcc-4.13.127.jar:na]
at com.baml.regw.db.replicator.ReplicationRunnable$$anonfun$run.apply(SimpleReplicator.scala:105) ~[regw-db-replicator-0.0.933-SNAPSHOT.jar:na]
at com.baml.regw.db.replicator.ReplicationRunnable$$anonfun$run.apply(SimpleReplicator.scala:80) ~[regw-db-replicator-0.0.933-SNAPSHOT.jar:na]
Since the exception was related to IntegrityConstraint I tried checking for presence of Composite primary key(ID+TimeStamp+9999-12-31 00.00.000000) but the combo is neither present in the Oracle table nor the Db2 table. The constraints on thE Db2 table are
由于异常与 IntegrityConstraint 相关,我尝试检查是否存在复合主键(ID+TimeStamp+9999-12-31 00.00.000000),但组合既不存在于 Oracle 表中,也不存在于 Db2 表中。E Db2 表上的约束是
COLUMN NAME UNIQUE RULE
+ID+BUSINESS_STOP+BUSINESS_START Primary
+ID Duplicate
+BUSINESS_START Duplicate
+LOW_QUALITY_IND Duplicate
+IDENTIFIER1 Duplicate
+IDENTIFIER2 Duplicate
I scoured through the other such issues in SO but none of the fixes worked for me. The code which is performing this task
我在 SO 中搜索了其他此类问题,但没有一个修复对我有用。执行此任务的代码
logger.info("Retrieving based on query string: " + queryStr + " for thread " + threadNum)
val start = System.currentTimeMillis()
val rs = stmt.executeQuery(queryStr)
val rsMd = rs.getMetaData()
val end = System.currentTimeMillis()
logger.info("Query execution time: " + (end - start) + "ms.")
done = true
var stmtCount = 0
Iterator.continually(rs).takeWhile(_.next()).foreach { rs =>
if (sourceConf.hasPath("blockSize")) {
done = false
}
//Subtract one to ignore the timestamp field that we are using
for (idx <- 1 to (rsMd.getColumnCount()-extraColumnCount)) {
try {
logger.info("destStmt.setObject"+rs.getObject(idx)+" column Type "+ rsMd.getColumnType(idx))
destStmt.setObject(idx, rs.getObject(idx), rsMd.getColumnType(idx))
}
catch {
case e:Exception => {
logger.warn("While attempting to set (1-based) index: " + idx +
" to value of type " + {if(rs.getObject(idx) != null) rs.getObject(idx).getClass().getName() else "[NULL]"} +
" received error: " + e.getMessage())
throw e
}
}
}
destStmt.addBatch()
stmtCount += 1
if(stmtCount % { if (destConf.hasPath("batchSize")) destConf.getInt("batchSize") else 200 } == 0) {
destStmt.executeBatch()
destDbConn.commit()
destStmt.clearBatch()
stmtCount = 0
}
}
if(stmtCount > 0) {
destStmt.executeBatch()
destDbConn.commit()
destStmt.clearBatch()
}
rs.close()
stmt.close()
采纳答案by Beryllium
Check if there is really the same integrity constraint in your source database (Oracle). Otherwise you might import rows which exist in your source table (because there isn't a constraint), but which can't be imported in the target table.
Check if the column indices are really the same in both tables (Oracle and DB2) from the perspective of JDBC. Other tools may sort by column name etc. Better yet: Use column names (iterate over the column names in the meta data). Your index-based approach won't work, if the columns are reordered which possibly happens, if columns are dropped and re-added etc. For example, if you have a
select * from x
and aninsert into x values(...)
the column order is relevant.Check if you have
null
values in your source table in the columns which are part of the constraint. Possibly Oracle handlesnull
values in the constraint differently than DB2, if it's backed by an index.
检查源数据库 (Oracle) 中是否确实存在相同的完整性约束。否则,您可能会导入源表中存在的行(因为没有约束),但无法导入到目标表中。
从 JDBC 的角度检查两个表(Oracle 和 DB2)中的列索引是否真的相同。其他工具可能按列名等排序。更好的是:使用列名(遍历元数据中的列名)。如果列被重新排序(可能发生),如果列被删除并重新添加等,您的基于索引的方法将不起作用。例如,如果您有一个
select * from x
和一个insert into x values(...)
列顺序是相关的。检查
null
源表中作为约束一部分的列中是否有值。null
如果有索引支持,Oracle 处理约束中的值的方式可能与 DB2 不同。