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]

REPOST: Using JDBC batch - sequence of statement execution?

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.

paul@abacus.co. uk (Paul Reddin) wrote in message news:<1fd2a603. 0310170713.35ad 5b80@posting.go ogle.com>...[color=blue]
> 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.[/color]

[PM \(pm3iinc-nospam\)]

Re: REPOST: Using JDBC batch - sequence of statement execution?

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 BatchUpdateExce ption() ? (SQLException)

Handling an SQLException under the DB2 Universal JDBC Driver

http://www-3.ibm.com/software/data/db2/udb/ad/v8/java/ad/cjvjcerr.htm

Behavioral Differences Between the Legacy JDBC Drivers and the Universal
JDBC Drivers

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.doc/ad/c0010280.htm

see PreparedStateme nt.executeBatch , Compound SQL, etc.

Batched Update Considerations When Using the Universal JDBC Driver

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.doc/ad/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 BatchUpdateExce ption
extends SQLException
An exception thrown when an error occurs during a batch update operation. In
addition to the information provided by SQLException, a BatchUpdateExce ption
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
BatchUpdateExce ption 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
BatchUpdateExce ption.getUpdate Counts 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