DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The IBM® Data Server Driver for JDBC and SQLJ supports batch updates in SQLJ. With batch updates, instead of updating rows of a table one at a time, you can direct SQLJ to execute a group of updates at the same time.
For all cases except homogeneous batches of INSERT statements, when an error occurs during execution of a statement in a batch, the remaining statements are executed, and a BatchUpdateException is thrown after all the statements in the batch have executed.
To obtain information about warnings, use the ExecutionContext.getWarnings method on the ExecutionContext that you used to submit statements to be batched. You can then retrieve an error description, SQLSTATE, and error code for each SQLWarning object.
When a batch is executed implicitly because the program contains a statement that cannot be added to the batch, the batch is executed before the new statement is processed. If an error occurs during execution of the batch, the statement that caused the batch to execute does not execute.
The basic steps for creating, executing, and deleting a batch of statements are:
#sql iterator GetMgr(String); // Declare positioned iterator
…
{
GetMgr deptiter; // Declare object of GetMgr class
String mgrnum = null; // Declare host variable for manager number
int raise = 400; // Declare raise amount
int currentSalary; // Declare current salary
String url, username, password; // Declare url, user ID, password
…
TestContext c1 = new TestContext (url, username, password, false); 1
ExecutionContext ec = new ExecutionContext(); 2
ec.setBatching(true); 3
#sql [c1] deptiter =
{SELECT MGRNO FROM DEPARTMENT};
// Assign the result table of the SELECT
// to iterator object deptiter
#sql {FETCH :deptiter INTO :mgrnum};
// Retrieve the first manager number
while (!deptiter.endFetch()) { // Check whether the FETCH returned a row
#sql [c1]
{SELECT SALARY INTO :currentSalary FROM EMPLOYEE
WHERE EMPNO=:mgrnum};
#sql [c1, ec] 4
{UPDATE EMPLOYEE SET SALARY=:(currentSalary+raise)
WHERE EMPNO=:mgrnum};
#sql {FETCH :deptiter INTO :mgrnum };
// Fetch the next row
}
ec.executeBatch(); 5
ec.setBatching(false); 6
#sql [c1] {COMMIT};
deptiter.close(); // Close the iterator
c1.close(); // Close the connection
}The following example demonstrates batching of INSERT statements. Suppose that ATOMICTBL is defined like this:
CREATE TABLE ATOMICTBL(
INTCOL INTEGER NOT NULL UNIQUE,
CHARCOL VARCHAR(10))Also suppose that the table already has a row with the values 2 and "val2". Because of the uniqueness constraint on INTCOL, when the following code is executed, the second INSERT statement in the batch fails.
If the target data server is DB2 for z/OS, and this application is customized without atomicMultiRowInsert set to DB2BaseDataSource.YES, the batch INSERT is non-atomic, so the first set of values is inserted in the table. However, if the application is customized with atomicMultiRowInsert set to DB2BaseDataSource.YES, the batch INSERT is atomic, so the first set of values is not inserted.
The numbers to the right of selected statements correspond to the previously described steps.
…
TestContext ctx = new TestContext (url, username, password, false); 1
ctx.getExecutionContext().setBatching(true); 2,3
try {
for (int i = 1; i<= 2; ++i) {
if (i == 1) {
intVar = 3;
strVar = "val1";
{
if (i == 2) {
intVar = 1;
strVar = "val2";
}
#sql [ctx] {INSERT INTO ATOMICTBL values(:intVar, :strVar)}; 4
}
int[] counts = ctx.getExecutionContext().executeBatch(); 5
for (int i = 0; i<counts.length; ++i) {
System.out.println(" count[" + i + "]:" + counts[i]);
}
}
catch (SQLException e) {
System.out.println(" Exception Caught: " + e.getMessage());
SQLException excp = null;
if (e instanceof SQLException)
{
System.out.println(" SQLCode: " + ((SQLException)e).getErrorCode() + "
Message: " + e.getMessage() );
excp = ((SQLException)e).getNextException();
while ( excp != null ) {
System.out.println(" SQLCode: " + ((SQLException)excp).getErrorCode() +
" Message: " + excp.getMessage() );
excp = excp.getNextException();
}
}
}