JDBC Batch creation problem

Hi Experts...
I have a requirement of updating a table which has about 5 million rows. So for that purpose i want to create batch statements in java and update as a bulk operation.

Right now I have 100 batches and it works fine.But when i increase the number of batches over hundred i get an exception as : com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Message empty.

How can i have more batch statements in my CallableStatement object.
Please help...
gaugetaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonCommented:
Hi!

The number of batches you need to use depends on the number of rows in each batch
and total number of rows that you are adding/updating to the table.
So having 100 batches does not tell me how many rows are in each batch but the error "Message empty"
tells me that you are adding a empty batch most likely due to EOF in the total number of rows.

Adding more batches is only necessary if
                [number of rows in a batch] * [number of batches] < [total number of rows to be added/updated]

Regards,
         Tomas Helgi
0
gaugetaAuthor Commented:
@Tomas Helgi:Thanks for the reply.
Here i am adding 100 rows to a batch statement and then executing the batch containing worth 100rows of data and I'm clearing the batch for accomodating the next hundred and so on.
I meant i could only fit 100 rows in the batch statement before execution not 100 batches.
Sorry for the confusion caused.
Please help...
0
Tomas Helgi JohannssonCommented:
Hi!

Hmmm, increase your batch size parameter in the Sybase database as it is default 100. :)
See here for more detail
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1209.htm
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00842.1502/html/spsysmon/X21140.htm

Regards,
    Tomas Helgi
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

gaugetaAuthor Commented:
@TomasHelgi:Thanks for the reply.
I took a look at the document.But the procedure to increase the same is not present anywhere.
Please help....
0
Tomas Helgi JohannssonCommented:
Hi!

If you look at the left side of the first link I provided then there is a link
"Setting configuration Parameters" and little lower is the "Using sp_configure" link
http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sag1/html/sag1/sag196.htm

which tells you how you sett/reset these parameters. :)

Regards,
      Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe WoodhousePrincipal ConsultantCommented:
If you mean the sp_configure parameter "i/o batch size", that has nothing to do with this issue. I/O batch size controls how ASE manages it's interface to the I/O system and under no circumstances affects how ASE processes result sets. As a strictly internal server-side setting it's rather unlikely to be relevant to JDBC processing.

That error message is from JDBC, it isn't a Sybase ASE error. Something is wrong in the middle layer - this isn't a database issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.