Prepared statement caching

Prepared statements are precompiled SQL statements that can be repeatedly invoked with different parameter values. Prepared statements reduce the need to compile the SQL statements.

You can improve the performance of agents and integration servers by using prepared statement caching, which caches executable statements that are used repeatedly.

By default, prepared statement caching is enabled for agents and integration servers.

• Oracle: Caching is enabled by default using Oracle’s Connection. If, however, you want to use caching provided out-of-the-box by IBM®, you must set the JDBC property useInternalStatementCache to true in the customer_overrides.properties file. To disable prepared statement caching, set the prepared statement cache size to zero; that is, set the JDBC property statementCacheSize=0 in the customer_overrides.properties file.
• Db2®: Caching is enabled by default using caching provided out-of-the-box by IBM. If you want to use caching provided by Db2 Data Source, you must set the JDBC property useInternalStatementCache to false in the customer_overrides.properties file. Note, however, that the benefits of caching prepared statements using Db2 Data Source can be offset by the cost of closing the connections. To disable prepared statement caching, set the prepared statement cache size to zero; that is, set the JDBC property statementCacheSize=0 in the customer_overrides.properties file.

You can determine the number of prepared statements to be maintained at any point of time in the cache, depending on your needs, by configuring the JDBC property statementCacheSize. By default, the prepared statement cache size is set to 10 for Oracle and Db2 pools. You can change this value depending on your needs, such as agent usage and load. Note, however, that if you increase this value, it may have an impact on performance and memory.