SQLJ Batching 

Use

In Open SQL/SQLJ, DML statements (INSERT, UPDATE and DELETE) that differ only in their parameters can be executed in batch update mode. In this mode, a statement is not executed immediately on the database. Instead, a statement batch is first prepared. Then, for each statement, only its parameters are added to the batch; the execution of the statement is deferred. Finally, the statement batch as a whole is sent to the database where it can be executed most efficiently.

In Open SQL/SQLJ, the execution context class that is used to execute a DML statement determines whether this statement is batchable. To use batching, a DML statement must be executed on the execution context class com.sap.sql.BatchExecutionContext, which is a subclass of sqlj.runtime.ExecutionContext. A statement, which is executed via the standard execution context class sqlj.runtime.ExecutionContext is not batchable. In the BatchExecutionContext, batching is always switched on and cannot be switched off. Therefore, BatchExecutionContext should be used only for batching.

Activities

Creating a BatchExecutionContext

An individual BatchExecutionContext object should be created for every batch. Initially, a BatchExecutionContext is clean (it is not associated with a parameter batch). If a DML statement is executed through the BatchExecutionContext, a new statement batch is prepared and attached to the DML statement.

import com.sap.sql.BatchExecutionContext;    [...]      BatchExecutionContext bCtx = new BatchExecutionContext();

Creating a new BatchExecutionContext. A new BatchExecutionContext object bCtx is constructed. On bCtx, batching is always switched on. The initial object bCtx is not associated with a parameter batch.

Adding Statements to the Batch

The first DML statement that is executed on a clean BatchExecutionContext object associates the context with the statement. If the same statement is executed on this batch execution context with different parameters, the parameters are added to the batch for deferred execution.

String[] names = new String[] { "Smith", "Jones", "Wright" };   for (int i = 0; i < 3; i++) {    #sql [cCtx, bCtx] {       INSERT INTO names (key, name)          VALUES (:i, :(names[i])) };

Adding statements to a batch. Three INSERT statements are added to a statement batch simply by executing the statements on a BatchExecutionContext object bCtx. The batch execution context bCtx on which the statements are executed is specified as the second argument in the square brackets after the  #sql token.

 

If a batch execution context is associated with a particular statement, only the statement with exactly the same source code position can be added to this statement batch – typically in a loop. If one attempts to add a non-matching statement to a statement batch, this attempt is rejected with a runtime exception.

Executing a Batch

In batch update mode, DML statements are not executed immediately on the database but are collected instead for deferred execution. You must trigger the execution on the database. A statement batch can be executed either explicitly by the executeBatch() method of the BatchExecutionContext class, or implicitly when a finite batch limit is reached.

Explicit Batch Execution

Once all statements have been added to the batch, the pending statement batch must be executed on the database using the executeBatch() method.

int[] result = bCtx.executeBatch();

Explicit Batch Execution. A pending statement batch is executed on the database using the executeBatch() method.

The method returns an array of update counts reflecting the number of rows changed for each statement in the batch. For more information, see com.sap.sql.BatchExecutionContext.

Implicit Batch Execution

If a batch is very large, it may be more efficient to execute it in packages instead of accumulating the entire batch on the client. To enable packaged execution, the BatchExecutionContext has a batch limit that determines the number of statements the batch can hold. By default, this number is unlimited (UNLIMITED_BATCH). Using the setBatchLimit()method, you can set this number to a finite value. If the batch limit is finite, the SQLJ runtime automatically executes a pending statement batch once the given limit is reached. However, you must invoke the executeBatch() method to ensure the execution of all batched statements.

import com.sap.sql.BatchExecutionContext;   [...]   String[] manyNames = getManyNames();   BatchExecutionContext bCtx = new BatchExecutionContext();   bCtx.setBatchLimit(100);   for (int i = 0; i < manyNames.length; i++) {    #sql [cCtx, bCtx] {       INSERT INTO names (key, name)          VALUES (:i, :(manyNames[i])) }; }   bCtx.executeBatch();

The batch limit. A large but unknown number of rows are inserted in the database. The batch limit is set to the value 100 forcing the SQLJ runtime to execute the batch implicitly if it contains 100 statements. Once the last row has been added to the batch, the remaining rows that may still be pending in the batch are executed using the executeBatch() method.

If the batch limit is set to the value AUTO_BATCH, the SQLJ runtime automatically chooses an appropriate finite batch limit.

The getUpdateCount() method determines whether a new batch has been started (NEW_BATCH_COUNT), a statement has been added to an existing batch (ADD_BATCH_COUNT), or whether the batch has been executed (EXEC_BATCH_COUNT).

Clearing a Batch

A pending statement batch can be cleared using the cancel() method. Note that if a finite batch limit has been set, part of the batch may already have been executed. This part cannot be reverted using the cancel() method.

Parallel Batches

Open SQL/SQLJ does not allow different statements to be mixed in a batch. However, it is possible to have multiple pending statement batches simultaneously. Every statement batch must use its own batch execution context.

import com.sap.sql.BatchExecutionContext;   [...]   interface Data {    boolean isNew();    long getId();    String getName; }   [...]   Data[] records = getData();   BatchExecutionContext bInsert = new BatchExecutionContext(); BatchExecutionContext bUpdate = new BatchExecutionContext();   for (int i = 0; i < records.length; i++) {    if (records[i].isNew()) {       #sql [cCtx, bInsert] {          INSERT INTO names (key, name)             VALUES (:(records[i].getId()),                   :(records[i].getName()) };    } else {       #sql [cCtx, bUpdate] {          UPDATE names             SET name = :(records[i].getName())             WHERE key = :(records[i].getId()) };    } }   bInsert.executeBatch(); bUpdate.executeBatch();

Parallel Batches. INSERT and UPDATE statements are executed using two different batch execution contexts. The two batches are collected and executed individually. We assume that the isNew()method of the interface Data ensures that INSERT and UPDATE statements do not collide.

Error Handling

If the execution of a batch fails, a java.sql.BatchUpdateException is thrown. The getUpdateCounts()method of the BatchUpdateException returns an array of integer values that obtain information about the success or failure of the statements of the executed batch. Note that the length of the array may be smaller than the number of statements in the batch. This makes proper error handling quite cumbersome, especially if implicit batching is being used.

String[] manyNames = getManyNames(); int lastExecBatchIndex = -1;   // obtain a new BatchExecutionContext BatchExecutionContext bCtx = new BatchExecutionContext();   // set the batch limit to 100 bCtx.setBatchLimit(100); int i = 0;   try {    for (; i < manyNames.length; i++) {       #sql [cCtx, bCtx] {          INSERT INTO names (key, name)             VALUES (:i, :(manyNames[i])) };       if (bCtx.getUpdateCount() ==          ExecutionContext.EXEC_BATCH_COUNT) {          lastExecBatchIndex = i;       }    }    bCtx.executeBatch(); } catch (java.sql.BatchUpdateException ex) {    int[] updateCounts = ex.getUpdateCounts();    int stmtNum, j = 0;      for (stmtNum = lastExecBatchIndex + 1;        stmtNum <= i;        stmtNum++, j++) {       // extract information from the updateCounts       if (j < updateCounts.length) {          switch (updateCounts[j]) {             case -3:    System.out.println("Statement " + stmtNum +                   " failed.");                break;             case -2:    System.out.println("Statement " + stmtNum +                   " executed. " +                   "The number of rows " +                   "affected is unknown.");                break;             default:    System.out.println("Statement " + stmtNum +                   " executed. " +                   "The number of rows " +                   "affected is " +                   updateCounts[j] + ".");          }       } else { // not contained in updateCounts    System.out.println("Statement " + stmtNum +                   " failed. " +                   "Not in updateCounts");       }    } }

Error Handling with Implicit Batching.On a BatchExecutionContext with a batch limit of 100, data is inserted. In the variable lastExecBatchIndex, the number of the last statement that was executed successfully is kept. In case of a BatchUpdateException, the array returned by the getUpdateCounts() method is analyzed. Special attention is paid to the facts that the statement batch may have been partly executed and that the array may not contain all statements of the batch.