MX 2.x)

Statement Pooling

The statement pooling feature allows applications to reuse the PreparedStatement object in same way that they can

reuse a connection in the connection pooling environment. Statement pooling is done completely transparent to the

application. Using statement pooling is described in the following topics:

Guidelines for Statement Pooling

Controlling the Performance of ResultSet Processing

Troubleshooting Statement Pooling

Guidelines for Statement Pooling

Enable statement pooling by setting the DataSource object maxStatements property to an integer value greater

than 0 and, also, by enabling 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.

Controlling the Performance of ResultSet Processing

To improve JDBC application performance of result fetches for statements that are expected to return more than two

rows, the application should set the fetch size before executing the statement. This operation works because the

ResultSet getter methods have been modified in the JDBC/MX driver to optimize database interactions. The

JDBC/MX driver uses the fetch-size setting to determine the size of memory used for reading and buffering data.

The application can control the ResultSet fetch size by using the setFetchSize() method of the Statement class,

PreparedStatement class, and ResultSet class.

Considerations:

Applications that use SQL/MX tables, rather than SQL/MP tables, have improved performance only for result

fetches that have greater than two rows returned. The default JDBC/MX fetch size is set to 1.

Once the application sets the fetch size to a value greater than 2 for a statement, the application should not reset

the value back to 2 or less. If the application does so, the application will experience a slight degradation in

performance as compared to using the default value.

Setting the fetch size greater than 2 for statements that return fewer than two rows causes a slight performance

degradation, as compared to using the default fetch size.

Setting the fetch size to a value greater than the number of rows returned by a statement causes the JDBC/MX

driver to use more memory, but does not affect the API's functionality.