JDBC Type 4 Driver 1.1 Programmer's Reference

Guidelines for Statement Pooling
To enable statement pooling, set the maxStatements property to an integer value greater than 0 and enable
connection pooling. See Connection Pooling for more information.
●
Enabling statement pooling for your JDBC applications might dramatically improve the performance.●
Explicitly close a prepared statement by using the Statement.close method because PreparedStatement
objects that are not in scope are also not reused unless the application explicitly closes them.
●
To ensure that your application reuses a PreparedStatement, call either of the following:
Statement.close method—called by the application❍
Connection.close method—called by the application. All the PreparedStatement objects that were in
use are ready to be reused when the connection is reused.
❍
●
Troubleshooting Statement Pooling
Note the following Type 4 driver implementation details if you are troubleshooting statement pooling:
Type 4 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 the Type 4 driver finds the matching
PreparedStatement object, the 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.
●
The Type 4 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.
●
The Type 4 driver pools the CallableStatement objects in the same way as PreparedStatement objects when
the statement pooling is activated.
●
The Type 4 driver does not cache Statement objects.●
Thread-safe SQL/MX Access
In the Type 4 driver, API layer classes are implemented as instance-specific objects to ensure thread safety:
SQLMXDataSource.getConnection() is implemented as a synchronized method to ensure thread safety in
getting a connection.
●
Once a connection is made, the connection object is instance-specific.●
If multiple statements are run on different threads in a single connection, statement objects are serialized to prevent data●