JDBC Type 4 Driver Programmer's Reference

Troubleshooting Statement Pooling●

Note the following JDBC Type 4 driver for NonStop SQL/MX implementation details if you are troubleshooting statement pooling:

JDBC Type 4 driver for NonStop SQL/MX 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 Type 4 driver for NonStop SQL/MX finds the matching

PreparedStatement object, JDBC Type 4 driver for NonStop SQL/MX 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 Type 4 driver for NonStop SQL/MX 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 Type 4 driver for NonStop SQL/MX pools the CallableStatement objects in the same way as

PreparedStatement objects when the statement pooling is activated.

●

JDBC Type 4 driver for NonStop SQL/MX 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:

SQLMXConnection.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

corruption.

●

HP NonStop JDBC Type 4 Driver Programmer's Reference (528781-003 )