IBM Data Server Driver for JDBC and SQLJ statement caching

The IBM® Data Server Driver for JDBC and SQLJ can use an internal statement cache to improve the performance of Java™ applications by caching and pooling prepared statements.

Internal statement caching is available for connections that use IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, or for connections that use IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2® for z/OS®.

You enable internal statement caching in any of the following ways:

• By setting one of the following properties to a positive value:
  • com.ibm.db2.jcc.DB2ConnectionPoolDataSource.maxStatements, for objects that are created using the javax.sql.ConnectionPoolDataSource interface.
  • com.ibm.db2.jcc.DB2XADataSource.maxStatements, for objects that are created using the javax.sql.XADataSource interface.
  • com.ibm.db2.jcc.DB2SimpleDataSource.maxStatements, for objects that are created using the com.ibm.db2.jcc.DB2SimpleDataSource interfaces.
• By setting the maxStatements property in a URL, and passing the URL to the DriverManager.getConnection method.

When internal statement caching is enabled, the IBM Data Server Driver for JDBC and SQLJ can cache PreparedStatement objects, CallableStatement objects, and JDBC resources that are used by SQLJ statements when those objects or resources are logically closed. When you explicitly or implicitly invoke the close method on a statement, you logically close the statement.

Reuse of a previously cached statement is transparent to applications. The statement cache exists for the life of an open connection. When the connection is closed, the driver deletes the statement cache and closes all pooled statements.

A logically open statement becomes ineligible for caching under either of the following circumstances:

• An exception occurs on the statement.
• JDBC 4.0 method Statement.setPoolable(false) is called.

When the IBM Data Server Driver for JDBC and SQLJ attempts to cache a statement, and the internal statement cache is full, the driver purges the least recently used cached statement, and inserts the new statement.

The internal statement cache is purged under the following conditions:

• A SET statement is issued that affects target objects of the SQL statement.
• A SET statement is executed that the IBM Data Server Driver for JDBC and SQLJ does not recognize.
• The IBM Data Server Driver for JDBC and SQLJ detects that a property that modifies target objects of the SQL statement was modified during connection reuse. currentSchema is an example of a property that modifies target objects of an SQL statement.

In a Java program, you can test whether the internal statement cache is enabled by issuing the DatabaseMetaData.supportsStatementPooling method. The method returns true if the internal statement cache is enabled.

The IBM Data Server Driver for JDBC and SQLJ does not check whether the definitions of target objects of statements in the internal statement cache have changed. If you execute SQL data definition language statements in an application, you need to disable internal statement caching for that application.

The internal statement cache requires extra memory. If memory becomes constrained, you can increase the JVM size, or decrease the value of maxStatements.