JDBC Type 2 Driver 3.0 Programmer's Reference (SQL/MX 3.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