JDBC Type 4 Driver Programmer's Reference for SQL/MX Release 3.2 (H06.25+, J06.14+)

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 PreparedStatement 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 HP NonStop SQL/MX Release 3.2
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 CallableStatement objects in the same way as
PreparedStatement objects when the statement pooling is activated.
• The Type 4 driver does not cache Statement objects.
Statement Pooling 27