Using JDBC batch

[Paul Reddin]

Hi,

(V8.1 Fp2)
Our application uses JDBC batch to execute mutiple insert statements
and we saw a strange thing this morning.

There were 4 SQL Insert statements in the batch, and we know the 2nd
would have
violated a Unique Constraint and failed, but the SQL error returned
was related to the 3rd statement (which had a FK dependent on the 2nd
statement).

This has raised some fundamental questions about DB2 JDBC Dtriver
batch processing.

1. Is it possible the 3rd statement was executed before the 2nd
statement?
2. Do we 'Have' to include BEGIN ATOMIC in the batch statements ?
3. Is there some other explanation why we saw the 3rd statement error
returned
and not the 2nd statement?

Any insight/pointers on this or more generally using JDBC batch
greatly
appreciated.

Paul.

[Paul Reddin]

Hi,
I am reposting this as we have just witnessed it again, and I just
want
to check if anyone believes this is working as designed/configurable
before I open a formal PMR with IBM.

Thanks.

Paul.

pa**@abacus.co.uk (Paul Reddin) wrote in message news:<1f**************************@posting.google. com>...

Hi,

(V8.1 Fp2)
Our application uses JDBC batch to execute mutiple insert statements
and we saw a strange thing this morning.

There were 4 SQL Insert statements in the batch, and we know the 2nd
would have
violated a Unique Constraint and failed, but the SQL error returned
was related to the 3rd statement (which had a FK dependent on the 2nd
statement).

This has raised some fundamental questions about DB2 JDBC Driver
batch processing.

1. Is it possible the 3rd statement was executed before the 2nd
statement?
2. Do we 'Have' to include BEGIN ATOMIC in the batch statements ?
3. Is there some other explanation why we saw the 3rd statement error
returned
and not the 2nd statement?

Any insight/pointers on this or more generally using JDBC batch
greatly
appreciated.

Paul.

[PM \(pm3iinc-nospam\)]

I don't have the answer but i think it would help to know the
driver used? (db2java, db2jcc)
type-2/3, universal/jcc type 4, ...
(to be sure it's supported)

Also verify how the error handling is coded.
What is used in the program.
You trap any BatchUpdateException() ? (SQLException)

Handling an SQLException under the DB2 Universal JDBC Driver
http://www-3.ibm.com/software/data/d...d/cjvjcerr.htm

Behavioral Differences Between the Legacy JDBC Drivers and the Universal
JDBC Drivers
http://publib.boulder.ibm.com/infoce...d/c0010280.htm
see PreparedStatement.executeBatch, Compound SQL, etc.

Batched Update Considerations When Using the Universal JDBC Driver
http://publib.boulder.ibm.com/infoce...d/c0010280.htm
snip. universal JDBC driver always batches nonatomically

Online doc link :
http://java.sun.com/j2se/1.3/docs/api/
http://java.sun.com/j2se/1.4.1/docs/api/
see java.sql

public class BatchUpdateException
extends SQLException
An exception thrown when an error occurs during a batch update operation. In
addition to the information provided by SQLException, a BatchUpdateException
provides the update counts for all commands that were executed successfully
during the batch update, that is, all commands that were executed before the
error occurred. The order of elements in an array of update counts
corresponds to the order in which commands were added to the batch.

After a command in a batch update fails to execute properly and a
BatchUpdateException is thrown, the driver may or may not continue to
process the remaining commands in the batch. If the driver continues
processing after a failure, the array returned by the method
BatchUpdateException.getUpdateCounts will have an element for every command
in the batch rather than only elements for the commands that executed
successfully before the error. In the case where the driver continues
processing commands, the array element for any command that failed is -3.

I remember seeing that the ibm db2 drivers sometimes uses -2, -3 values.
I guess it's the kind of driver that continues processing on error...
although i'm not sure.

PM