I am trying to update a million rows in a table and doing this reading
from a file which has the record pkids and constructing a
preparedstatemtn with that pkid and adding it to the batch...
count is the recordnumber read from the file...
so for every count the st is added to the batch.....since for every
record read from the file has a update statement(st)...

st.setString(1, arg1);
st.setString(2, arg2);
st.setString(3, arg3);
st.addBatch();

if(count%100==0 )
{
int[] upCounts = st.executeBatch();
}

Now the problem is, I need to know how many updates failed( in the
sense didnt update since the record's arg1 dint match etc) in which
case the return code when we do executeUpdate() is 0 (indicating 0 rows
affected) . How can i do this with the upCounts array ? or any other
way ....

It doesnt throw an exception ... since the statement executed properly
but the result set was zero.

All i want to do is to get the number of records FAILED,SUCCEEDED at
the end of all the executions ....???? I seemed ot be totally lost..how
cna i do it? any help appreciated!

More than that, if any body can give me a way to update a million
records as fast as i can..right now it takes a hell longer...with
executebatch it takes 1000recrods per min...is there a way to puch the
performance even faster?

is it a prepared statement?

"technocrat" <sumant.kalvala (AT) gmail (DOT) com> wrote

YEP...but now i ahve given it up...came accross something called
positioned fetch...

can anyone tellme how to fetch using java?????
declare a cursor and then fetch...from the cursor....

essentially do this in java...

EXEC SQL DECLARE C1 CURSOR FOR
SELECT *
FROM EMPLOYEE
FOR UPDATE OF JOB;

EXEC SQL OPEN C1;

EXEC SQL FETCH C1 INTO ... ;
if ( strcmp (change, "YES") == 0 )
EXEC SQL UPDATE EMPLOYEE
SET JOB = :newjob
WHERE CURRENT OF C1;

EXEC SQL CLOSE C1;

In my experiments in the past prepared statement batch gave me the best
performance.
Is the table that you are updating having a lot of indexes, did you get the
access plan for the where clause in
the update. Can you post your update statement, table ddl?

may be u need to get a snapshot of what is happening in the database when u
issue the update statement.
Based on the snapshot you might have to fine tune parameters like
NUM_IO_SERVERS,buffer pool size
that will help improve the overall performance.

-Sumanth


"technocrat" <sumant.kalvala (AT) gmail (DOT) com> wrote