JDBC Driver for SQL/MX Programmer's Reference

Troubleshooting Statement Pooling
Note the following JDBC/MX driver implementation details if you are troubleshooting statement
pooling:
JDBC/MX driver looks for a matching PreparedStatement object in the statement pool
and reuses the PreparedStatement. The matching criteria include the SQL string, current
catalog, current schema, current transaction isolation, and resultSetHoldability. If
JDBC/MX driver finds the matching PreparedStatement object, JDBC/MX driver
returns the same preparedStatement object to the application for reuse and marks the
PreparedStatement object as in use.
●
The algorithm, "earlier used are the first to go," is used to make room for caching
subsequently generated PreparedStatement objects when the number of statements
reaches the maxStatements limit.
●
JDBC/MX driver assumes that any SQL CONTROL statements in effect at the time of
execution or reuse are the same as those in effect at the time of SQL/MX compilation. If this
condition is not true, reuse of a PreparedStatement object might result in unexpected
behavior.
●
You should avoid SQL/MX recompilation to yield performance improvements from statement
pooling. The SQL/MX executor automatically recompiles queries when certain conditions are
met. Some of these conditions are:
A run-time version of a table has a different redefinition timestamp than the
compile-time version of the same table.
❍
An existing open operation on a table was eliminated by a DDL or SQL utility
operation.
❍
The transaction isolation level and access mode at execution time is different from that
at the compile time.
❍
For more information on SQL/MX recompilation, see the SQL/MX Programming Manual for
C and COBOL or the SQL/MX Programming Manual for Java.
●
When a query is recompiled, the SQL/MX executor stores the recompiled query; therefore, the
query is recompiled only once until any of the previous conditions are met again.
●
JDBC/MX driver pools the CallableStatement objects in the same way as
PreparedStatement objects when the statement pooling is activated.
●
JDBC/MX driver does not cache Statement objects.●