Making batch updates in SQLJ applications
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.
About this task
- Searched INSERT, UPDATE, or DELETE, or MERGE statements
- CREATE, ALTER, DROP, GRANT, or REVOKE statements
- CALL statements with input parameters only
- Instances of the same statement
- Different statements
- Statements with different numbers of input parameters or host expressions
- Statements with different data types for input parameters or host expressions
- Statements with no input parameters or host expressions
For all cases except batch inserts, 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.
For batch inserts, the behavior is as follows:
- If you set atomicMultiRowInsert to DB2BaseDataSource.YES (1) when you run db2sqljcustomize, and the target data server is Db2® for z/OS®, when an error occurs during execution of an INSERT statement in a batch, the remaining statements are not executed, and a BatchUpdateException is thrown.
- If you do not set atomicMultiRowInsert to DB2BaseDataSource.YES (1) when you run db2sqljcustomize, or the target data server is not Db2 for z/OS, when an error occurs during execution of an INSERT 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.
Procedure
The basic steps for creating, executing, and deleting a batch of statements are:
Example
The following example demonstrates batching of UPDATE statements. The numbers to the right of selected statements correspond to the previously described steps.#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();
}
}
}