+

Search Tips   |   Advanced Search

Enable SQL statement batching


SQL statement batching can improve the performance of the application server. Java Persistence API (JPA) for WAS uses the JDBC addBatch and executeBatch APIs to batch statements.

By default, statement batching is enabled for DB2 and Oracle databases. To enable SQL statement batching and to set the batch limit for JPA applications, we need to configure the persistence.xml file.

The following steps review how to enable and disable statement batching, as well as set the batch limit:

 

  1. Define the UpdateManager property in the persistence.xml file.

    For example:

    <property name="openjpa.jdbc.UpdateManager"  value="com.ibm.ws.persistence.jdbc.kernel.OperationOrderUpdateManager(batchLimit=100)"/>
    

    The example shows that the SQL statement batch limit is set to 100. Remember: For a DB2 or an Oracle database, by default the SQL statement batching is enabled and set to batchLimit=100. However, if we are using DB2 or Oracle, we are not required to specify this property in the persistence.xml file.

  2. to disable SQL statement batching, set the batchLimit value to 0 (zero) or remove the property. However, if we are using a DB2 or an Oracle database, specify the DBDictionary property, database, and set the defaultBatchLimit to 0 (zero). For example:

    <property name="openjpa.jdbc.DBDictionary" value="db2(defaultBatchLimit=0)"/>
    

 

Results

we have now updated the persistence.xml file to enable or disable statement batching and set the batch limit.

 

Related tasks


Associating persistence units and data sources