Java Database Connectivity (JDBC)

are you trying to commit all of them as a single unit of work? could be a transaction buffer that's exceeded.

smaller commits might help.

%

i doubt this. sure you just aren't catching and handling them?

> why is weblogic involved at all?
why do you use java to do this? why not an etl tool or imports that your database has?


%

May be a DB or JDBC driver limitation. Who knows?

Use batches of batches of 70000 instead.

But have you considered using the DB's own import and export tools? Inserting that much records using Java is a bad smell.

Yes, I am catching all the exception, but none is thrown back,

Also, am committing only once, after all the batch is executed.

Yes, I am make the change to insert splitting the records into 2 batches, But without knowing the exact reason for the issue, how can we make the change.

The insert is happening after doing some manipulation on the records in JAVA, so the option of the etl tools doesn't come into the picture.

so what does the database log tell you?
the error tells you this is a bad idea. smaller commits, please.

%

As said, maybe a DB or JDBC driver limitation (or bug).
Then consider writing it to a SQL file and let the DB import it.

If we are not at all bothered about the Performance (No Batch Update) then we can have 80k rows record by record inserts and a single commit.
Reference : http://www.javaperformancetuning.com/tips/jdbc.shtml

1)Insert to succeed needs more TABLE SPACE more the tablespace better the execution.
2)Each record while Update or Insert operation will be buffered by DB engine so that it can rollback when ever we call con.rollbackTransactions(); - So it has to store all the 80k statements and be ready for commit or rollback. if it is unable to maintain this data it will through an exception.
3)It can also depend on the connection pooling you are using.

Add 1 to a record counter and after a certain count is reached, commit. Rather than just two batches, I think you might chose to commit after each 1000 rows or so; and run a log-file out that shows the last row inserted, the count and then commit, so that if it still fails you know exactly where you left off w/o running a separate SQL inquiry.

Did you test the return value (array) from executeBatch() to determine that nothing failed.
That statement contains an incorrect idea.

The fact that java manipulates the data does not preclude you from using the tools. You need do nothing more than create an appropriate file after manipulating the data.

The return value of the Batch is -2 (SUCCESS_NO_INFO). Using whcih we are not able to determine how many records had processed.

Several posters have recommended more frequent commits, as well as other things that should help you. I don't understand why you appear reluctant to try some of these.

I am open to make the change, but the piece of code is running in production for almost 15 months, So wanted to find the exact & concrete answer. So that i can make the change.

Also, the flow of request started increasiing the recent months,

Thanks

We're going back and forth about this and really aren't getting very far are we? You are in the best position to test this out, and testing will likely get you the answer. Simply thinking and asking others to think, without doing any testing and debugging is a poor substitute.

But you do have a test server where you can try out potential changes, don't you?

Yes, I do have the test server, But I am trying a lot to stimulate the problem, b/c in test env, it is very hard to process thousands of record at a time, I am think of taking the prod request and flowing it in the test,

Huh?

Write a program that generates thousands of records.

I made the code change to commit every 10,000 records. It worked perfectly fine.

Suppose i have 100000 records to insert , i have 10 commits in this case, but the issue is if the exception happens somwhere after 50000 records, am not able to rollback the comitted records.

This is true - and could be looked at as a down-side. That's why I it was suggested that you log your commits so that you know EXACTLY where you left off and what the last committed row was.

Then there is this - committing more frequently might (as is the intention) prevent failure in the first place ... which is certainly most preferable.

So?

If you have 100,000 records that logically need to be in a single commit then I suggest you look at a different design and perhaps architecture as well.

For example, put the records into a "temporary" table and process them in the database to move them to the final destination.