Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 –  Jack Douglas Sep 5 '11 at 13:07
    
@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). –  Clockwork-Muse Sep 6 '11 at 18:13
    
@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? –  Jack Douglas Sep 6 '11 at 18:18
    
@Jack - ack, missed that, you're probably right. So, it's still 'what other exceptions are you seeing'. –  Clockwork-Muse Sep 6 '11 at 20:38
add comment

2 Answers 2

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

share|improve this answer
    
Great point - it does look like a merge is needed. Thanks for contributing. –  Jack Douglas Nov 26 '11 at 12:47
add comment

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

share|improve this answer
2  
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? –  Jack Douglas Nov 28 '11 at 16:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.