3.14.4 Statement pooling

When you use DB Connector, you can use the pooling functionality that reuses JDBC APIs java.sql.PreparedStatement and java.sql.CallableStatement. By the statement pooling functionality, you can try to improve the performance when PreparedStatement and CallableStatement are used. Note that you specify the pool size for PreparedStatement and CallableStatement in the DB Connector settings. For details on the guidelines for specifying the pool size when a statement pooling is used, see 8.5.2 Using statement pooling in the uCosminexus Application Server System Design Guide. Also, for details on defining the DB Connector properties, see 4.2.2 Defining the DB Connector properties in the uCosminexus Application Server Application Setup Guide.

With the statement pooling functionality, the statement is initialized when the statement is reused. You specify the content to be initialized by customizing the J2EE server properties. For details on the settings of the statement pooling functionality, see 3.14.10 Settings in the execution environment.

To use the statement pooling functionality, you must use the connection pooling functionality. Also, if global transaction is specified in the transaction support level, the statement pooling functionality might be unavailable depending on the HiRDB version.

Note
When you use HiRDB Type4 JDBC Driver, the pool size of PreparedStatement and CallableStatement is restricted. For details on the specifiable pool size, see 4.1.10 Properties that can be specified in the <config-property> tag set in DB Connector in the uCosminexus Application Server Application and Resource Definition Reference Guide.
Organization of this subsection
(1) Preconditions
(2) Statement pooling operations
(3) Notes on using the statement pooling functionality

(1) Preconditions

The following table describes the usage of statement pooling based on the mapping with the transaction support level, the use of connection pooling, and the database type used.

Table 3-49 Usage of statement pooling

Transaction support levelConnection pooling is usedConnection pooling is not used
HiRDB#1OracleSQL ServerXDM/RD E2HiRDB/Oracle/SQL Server
No transaction (NoTransaction)YYYYN
Local transaction (LocalTransaction)YYYYN
Global transaction (XATransaction)Y#2YNNN
Legend:
Y: Can be used
N: Cannot be used

#1: Do not execute the definition SQL statement when you use the statement pooling functionality. When you execute the definition SQL statement, you cannot use the statement pooling functionality. Also, when you execute the definition SQL statement, you must set up PDDDLDEAPRP=YES as the HiRDB client environment variable.

#2: This can be used if the return value of DatabaseMetaData#supportsStatementPooling() of the JDBC is true.


Note that when you use XDM/RD E2, the statement pooling functionality is available only when you use XDM/RD E2 11-03 or later and HiRDB Type4 JDBC Driver 08-02 or later. For details on the settings of the statement pooling functionality, see 3.14.10 Settings in the execution environment.

(2) Statement pooling operations

This section describes the statement pooling operations specified in the resource adapter configuration properties.

The following table describes the operations of the statement pooling functionality.

Table 3-50 Statement pool states and operations

User application program processingStatement pool stateStatement pool operations
Requesting the generation of PreparedStatement and CallableStatementThere are unused PreparedStatement and CallableStatement in the poolEither of the unused PreparedStatement or CallableStatement are selected in the pool and passed to the user application program. The status of the selected PreparedStatement or CallableStatement in the pool changes to in-use.
There are no unused PreparedStatement and CallableStatement in the pool, and the total number of PreparedStatement and CallableStatement in the pool is less than the value of PreparedStatementPoolSize and CallableStatementPoolSizeNew PreparedStatement and CallableStatement are generated. The generated PreparedStatement and CallableStatement are passed to the user application, and the status of PreparedStatement and CallableStatement in the pool changes to in-use.
There are no unused PreparedStatement and CallableStatement in the pool, and the total number of PreparedStatement and CallableStatement in the pool is the value of PreparedStatementPoolSize and CallableStatementPoolSize or morePreparedStatement and CallableStatement with the oldest time stamp# are deleted from the pool, and then new PreparedStatement and CallableStatement are generated. The generated PreparedStatement and CallableStatement are passed to the user application, and the status of PreparedStatement and CallableStatement in the pool changes to in-use.
Releasing PreparedStatement and CallableStatement--PreparedStatement and CallableStatement are returned to unused in the pool.
Legend:
--: Not applicable

#: The time stamps for PreparedStatement and CallableStatement in the pool are updated at the following times:


(3) Notes on using the statement pooling functionality

The following table describes the notes on using the statement pooling functionality.

Table 3-51 Notes on using the statement pooling functionality

ConditionNotes
--
  • Compared to when the statement pooling functionality is not used, the memory is only consumed for the number of pooled PreparedStatement and CallableStatement. For details on the memory used for each statement, see the documentation for JDBC driver in use.
  • There is a statement pool for each connection in the connection pool, so the maximum number of pooled PreparedStatement and CallableStatement becomes MaxPoolSize x (PreparedStatementPoolSize + CallableStatementPoolSize).
--
  • If you use the statement pooling functionality together with the connection count adjustment functionality or the connection error detection functionality, the unused connections removed from the connection pool are not counted as the number of connections in the connection pool. Therefore, the number of pooled PreparedStatement and CallableStatement might temporarily exceed the maximum-connection-pool-value x PreparedStatementPoolSize, and maximum-connection-pool-value x CallableStatementPoolSize.
  • If you use the statement pooling functionality to reuse the statements, the value specified for setMaxFieldSize might not be initialized.
    The value is not initialized when all of the following conditions are satisfied:
    - DB Connector supporting Oracle JDBC Thin Driver is used
    - The setMaxFieldSize method is used to change the value of java.sql.PreparedStatement or java.sql.CallableStatement
Connecting to Oracle or SQL Server
  • When you use the statement pooling functionality and the connection error detection functionality together
    One PreparedStatement, used for detecting the connection errors, is pooled for each connection. Therefore, when you determine PreparedStatementPoolSize and CallableStatementPoolSize, add PreparedStatement for detecting the connection errors in the estimation of the number of resources, and set CallableStatementPoolSize to a value that is less than the maximum number of statements. #
  • When you use the statement pooling functionality and the connection pool warming up functionality together
    When the connections are generated and pooled, one PreparedStatement, generated with the SQL statement used for detecting the connection errors, is pooled for each connection.
  • When you use the statement pooling functionality and the result set holding functionality together
    When you use the result set holding functionality with an Oracle connection, specify the result set holding functionality in the argument of the Connection.prepareStatement() method or prepareCall() method. You cannot specify the result set holding functionality with the Connection.setHoldability() method.
Legend:
--: Not applicable

#
When you use the connection error detection functionality, set CallableStatementPoolSize to a value less than the maximum number of statements.
If you use the connection error detection functionality, the process of detecting the connection errors is executed when the number of pooled CallableStatement reaches the maximum number of statements. At this time, if CallableStatementPoolSize = maximum-number-of-statements is set, the maximum number of resources that can be used in one JDBC driver connection is exceeded, so an exception occurs. If an exception occurs, an error is determined; therefore, that connection is deleted from the connection pool and simultaneously the statement pool is also destroyed. In other words, using the statement pooling functionality becomes meaningless.