DB2: Using NOT EXISTS with SYSIBM.DUAL in a complex batch query

[vicky chauhan]

Hello All,

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.

Please help!
Nik