• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Issue in reading the number of sucess / failure results

 
kiran nyala
Ranch Hand
Posts: 55
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Every one,

I am running classic JDBC Batch Execution on DB2 table. I need to figure out the failed records from the batch.
I tried following code. Let the batch size is 30.



output:

Return value from inserting batch: [-3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3]

Batch Update Array returning -3 failure status for all records, but when i query on the table i found 28 records are updated. so that i am unable to add those failed records to error list. Help me in this regard

Thanks
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The very best advice I can give is to design your processes so that you don't need to recover from failed SQL operations. When something fails, roll back the entire transaction, report an error, and -perhaps- retry the entire transaction. Resolving errors correctly within the context of current transaction is difficult, probably database and/or JDBC driver dependent and prone to unforeseen problems.

Why is your insert batch failing? I guess it is a primary key or unique constraint violation. If it is the case, a possibility would be to insert all records into temporary table first and then use SQL statements to identify rows in the temporary table which fail the constraints and decide what to do with them (report them to the user?). The rest can be inserted into the table using the MERGE statement (DB2 seems to support MERGE statement). MERGE is very versatile and can be used to skip existing rows, or to update them.

If you want to pursue your current way, you need to find a documentation for the JDBC driver you're using, and see what that documentation says about handling failed JDBC batches. It might say that you need to rollback to get the database into a defined state. Or it will say how to identify failed rows. There's a caveat, though: if you resolve your issue with your current JDBC driver, the same code probably will not work with another driver (or even another database), of course.
 
kiran nyala
Ranch Hand
Posts: 55
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your valuable suggestion Mr. Martin.

But for this I must let the transaction to be happen , I don't want to rollback it. More over there is no constraint violation with my data.

I just need to capture the failed records.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do the insertions fail, if not for constraint violation?

If you really need to do it this way, I'd suggest to avoid batching. You'd insert one row after another and immediately knew which ones went through and which didn't.
 
kiran nyala
Ranch Hand
Posts: 55
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok, thank you Martin
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic