DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

Question

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. 

Thanks, Nik

• Jack Douglas: So did you "Use getNextException() to retrieve the exceptions for specific batched elements"? It looks like one or more individual inserts failed in the batch, perhaps due to constraint failures, but you need to see which ones failed and what errors were raised to debug
• Clockwork-Muse: @Jack - my read too. What version of DB2 are you using? SYSIBM.DUAL doesn't exist on the iSeries, for example (it's SYSIBM.SYSDUMMY1 or SYSIBM/SYSDUMMY1, depending on what conventions you're using).
• Jack Douglas: @X-Zero I'm just guessing with DB2, but: "Running above query independently for single set of data runs successfully" indicates Nikunj has SYSIBM.DUAL on his version?
• Clockwork-Muse: @Jack - ack, missed that, you're probably right. So, it's still 'what other exceptions are you seeing'.

Answer 1

This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...

It may be better to use a MERGE statement in this situation (click the link for the publib entry):

MERGE INTO mySchema.myTable tab USING (
 VALUES ('abc', '123')
 ) AS merge (C1, C2)
 ON tab.column1 = merge.C1
 AND tab.column2 = merge.C2
 WHEN MATCHED THEN
 IGNORE
 WHEN NOT MATCHED THEN 
 INSERT (column1, column2)
 VALUES (merge.C1, merge.C2)

This will take the columns in the "fake" table merge, and compare them using the keys in the ON clause, and if there is not a match, then it will use the INSERT statement. If there is a match, that row will just be ignored.

However, the availability of MERGE depends on your platform. I'm fairly certain MERGE has been in Linux/Unix/Windows DB2 since v8 (although, you can only use PREPARE'd merges since 9.7), and it was added in z/OS DB2 in v9.1. I don't know about the other platforms (AS/400, etc.).

• Jack Douglas: Great point - it does look like a merge is needed. Thanks for contributing.

Answer 2

select ?, ? from SYSIBM.DUAL is not valid syntax. To my knowledge the question marks can only go in the where clause.

• Jack Douglas: thanks for helping out @shaune and welcome to dba.se - do you happen to know where to find any DB2 documentation to back up your answer?
• mustaccio: It is perfectly valid, except you need to wrap them in CAST(? AS whatever) to provide data type information to the query compiler.