MX 2.x)

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.

Using Additional JDBC/MX Properties

You can use JDBC/MX properties for the following application features:

BatchUpdate Exception handling Improvements

Statement Level Atomicity

Managing Nonblocking JDBC/MX

Setting Batch Processing for Prepared Statements

Setting the reserveDataLocators Property

In addition to these topics, also see Enabling Tracing for Application Servers.

BatchUpdate Exception handling Improvements