I have data in a java object as data1, data2.
data1 and data2 together forms a composite key in myTable where I want to insert the object.
The writing is happening as a batch. Like 10 insert statements are prepared using 10 objects and are executed as a batch.
I want to insert the above data with the constraint: data1 + data2 should not already be present in myTable i.e. data1 + data2 should be unique --- if unique then write else just ignore.
The query I am using is:
Insert into mySchema.myTable(column1, column2)
select 'abc', '123'
from SYSIBM.DUAL
where not exists
( select 1
from mySchema.myTable A
where 'abc' = A.column1
and '123' = A.column2
)
Running above query independently for single set of data runs successfully.
However, while running in batch scenario I am getting "com.ibm.db2.jcc.b.ie: Non-atomic batch failure." error.
I think it has something to do with using SYSIBM.DUAL in batch scenario.
Code which is failing:
Insert Query:
Insert into mySchema.myTable(column1, column2)
select ?,? from SYSIBM.DUAL
where not exists (
select 1 from mySchema.myTable A
where ?=A.column1 and ?=A.column2)
Statement Setters:
ps.setString(1, item.getColumn1());
ps.setString(2, item.getColumn2());
ps.setString(3, item.getColumn1());
ps.setString(4, item.getColumn2());
where item is the java object holding the two columns to write.
Error is:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [Insert into mySchema.myTable(column1, column2) select ?,? from SYSIBM.DUAL where not exists (select 1 from mySchema.myTable A where ?=A.column1 and ?=A.column2)]; nested exception is com.ibm.db2.jcc.b.ie: Non-atomic batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
