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.