Solved

JDBC Batch creation problem

Posted on 2012-04-07
6
727 Views
Last Modified: 2012-11-24
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...
0
Comment
Question by:gaugeta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37821792
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
 

Author Comment

by:gaugeta
ID: 37826356
@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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37826613
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:gaugeta
ID: 37827045
@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
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 37827092
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37843299
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question