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

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.).

Answer 2

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