8.5.3 Tuning parameters for optimizing the method of accessing the database

How to set up the tuning parameters used to optimize the method of accessing a database is explained in this section.

Organization of this subsection
(1) Connection pooling
(2) Statement pooling

(1) Connection pooling

This subsection explains how to set up tuning parameters for connection pooling.

Specify the items listed in the following table using the server management command (cjsetresprop / cjsetrarprop) for each resource adapter and define the parameters in the Connector property file.

Table 8-24 Tuning parameters of connection pooling

Setup itemLocation of setup
(parameter name) #1
Minimum number of connections for pooling in the connection poolMinPoolSize specified in <property> tag
Maximum number of connections for pooling in the connection poolMaxPoolSize specified in <property> tag
Select the method for checking connection failures within the poolValidationType specified in <property> tag
Time interval for regularly checking for any connection failure within the poolValidationInterval specified in <property> tag
Specify whether the requests for connections are to be queued when all connections are in useRequestQueueEnable specified in <property> tag
Waiting time when a connection request is managed in a queue when all connections are being usedRequestQueueTimeout specified in <property> tag
Retry frequency in the case of a connection failure#2RetryCount specified in <property> tag
Retry interval in the case of a connection failure#2RetryInterval specified in <property> tag
Time until it is decided to automatically delete the connections, from the time connection was used lastConnectionTimeout specified in <property> tag
Time interval before automatic deletion of connection is executed (connection sweeper)SweeperInterval specified in <property> tag
Specify whether connection warm-up is to be usedWarmup specified in <property> tag
Specify whether to set a timeout for detecting the connection failure#3NetworkFailureTimeout specified in <property> tag
Specify whether to set a timeout for the deletion process of connection adjustment functionality#3
Time interval for executing the connection adjustment functionalityConnectionPoolAdjustmentInterval specified in <property> tag
#1
Set in the member resource adaptor when the cluster connection pool functionality is used.
#2
Cannot be set when the cluster connection pool functionality is used.
#3
Common connection management method is used when timeout is used for detecting a connection failure and the connection adjustment functionality. When either of the timeout is set, timeout is enabled for both connection failure detection and connection adjustment functionality. When you want to change the timeout of connection failure detection and connection adjustment functionality, change the settings of the parameter (ejbserver.connectionpool.validation.timeout) for the J2EE server setup in the Easy Setup definition file. For details, see 4.14.1 Parameters setting the user properties for J2EE server in the uCosminexus Application Server Definition Reference Guide.

Hint
We recommend the following settings to use the connection pooling functionality in HiRDB or XDM/RD E2:
  • To use the connection pooling functionality in HiRDB
    Specify 0 in the HiRDB client environment variable PDSWATCHTIME. For details on this parameter, see the HiRDB UAP Development Guide.
  • To use the connection pooling functionality in XDM/RD E2
    Specify 0 in the SVINTERVAL parameter of the control statement for invoking the control space or the control statement for invoking the server space of the DB connection server. For details on this parameter, see the manual VOS3 Database Connection Server.
If the above settings are not specified, the pooled connections might be disconnected from the database due to a timeout.
  • If the error detection functionality is used
    The connections disconnected from the database due to a timeout are destroyed using the error detection functionality. A connection can be acquired normally.
  • If the error detection functionality is not used
    The connections disconnected from the database due to a timeout are acquired.

(2) Statement pooling

This subsection explains how to set up the tuning parameters of statement pooling.

Specify the items listed in the following table using the server management command (cjsetresprop / cjsetrarprop), and define the parameters in the Connector property file.

Table 8-25 Tuning parameters of a statement pooling

Setup itemParameter name#
Number of PreparedStatement to be pooled for each physical connectionPreparedStatementPoolSize specified in <config-property> tag
Number of CallableStatement to be pooled for each physical connectionCallableStatementPoolSize specified in <config-property> tag
#
Specify the usage of the cluster connection pool functionality in the member resource adaptor.