JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.2.1 (H06.26+, J06.15+)

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” (page 35) 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.
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.
38 Accessing SQL Databases with SQL/MX