WebSphere Application Server data source properties
Use this page to set advanced data source properties in the application server. These properties activate and configure services that the application server applies to data sources to customize connections within an application server. These properties do not affect connections within the database.
- .
Statement cache size
Specifies the number of statements that can be cached per connection. The application server caches a statement after you close that statement.
- A prepared statement is a precompiled SQL statement that is stored in a PreparedStatement object. The application server uses this object to run the SQL statement multiple times, as required by your application run time, with values that are determined by the run time.
- A callable statement is an SQL statement that contains a call to a stored procedure, which is a series of precompiled statements that perform a task and return a result. The statement is stored in the CallableStatement object. The application server uses this object to run a stored procedure multiple times, as required by your application run time, with values that are determined by the run time.
If the statement cache is not large enough, useful entries are discarded to make room for new entries. To determine the highest value for your cache size to avoid any cache discards, add the number of uniquely prepared statements and callable statements, as determined by the SQL string, concurrency, and the scroll type, for each application that uses this data source on a particular server. This value is the maximum number of possible statements that can be cached on a given connection over the life of the server. Setting the cache size to this value means that you never have cache discards. In general, configure a larger cache for applications with a greater number of statements.
If there is a particular statement that you do not want the application server to cache, configure the statement poolability hint to false. The application server does not cache a statement if the poolability hint is set to false. The application specifies the statement poolability hints at run time.
In test applications, tuning the statement cache improves throughput from 10% to 20%. However, because of potential resource limitations, this tuning process might not always be possible.
Information | Value |
---|---|
Data type | Integer |
Default | Default values depend on the database. Typically, this value is 10. For Informix® versions 7.3, 9.2, 9.3, and 9.4, without the respective latest fixes, the default value must be 0. A default value of 0 means that there is no cache statement. |
Enable multithreaded access detection
J2CA0167W: An attempt to concurrently use the same connection handle by multiple threads has been detected. The connection handle is: {0}.
DSRA8720W: Detected multithreaded access on {0}. Last used with thread id: {1}id multithreaded access. Current thread id: {2} Stack trace of current thread:{3}
Enable database reauthentication
Indicates that the exact match on connections retrieved out of the application server connection pool (the connection pool search criteria does not include a user name and password) cannot exist. Instead, the connection reauthentication is done in the doConnectionSetupPerTransaction() of the DataStoreHelper class. The application server does not provide a connection reauthentication implementation at run time. Therefore, when you check this box, you must extend the DataStoreHelper class to provide implementation of the doConnectionSetupPerTransaction() method where the reauthentication occurs. If you do not complete this process, the application server might return unusable connections. For more information, read the API documentation for the com.ibm.websphere.rsadapter.DataStoreHelper#doConnectionSetupPerTransaction method.
Enable JMS one-phase optimization support
When you check this option, the application server uses Java™ Message Service (JMS) to get optimized connections from this data source. This property prevents Java database connectivity (JDBC) applications from sharing connections with container-managed persistence (CMP) applications. This option is not available if the JDBC provider of the data source is an XA provider.
Manage cached handles
Specifies whether the container tracks cached handles, which are connection handles that an application component holds active across transaction and method boundaries. You can use this property to debug connection problems, but tracking handles can cause large performance issues during run time.
AdminConfig.modify(myDataSourceVariable, '[[manageCachedHandles "true"]]')
Log missing transaction context
Specifies whether the container issues an entry to the activity log when an application obtains a connection without a transaction context. These are exceptions to the Java Platform, Enterprise Edition (Java EE) programming model connection requirements.
Non-transactional data source
Use WebSphere Application Server exception checking model
Specifies that the application server uses the error mapping facility that is defined in the data store helper to identify errors. The application server does not replace exceptions that are thrown by the JDBC driver with exceptions that are defined in the error map of the data store helper.
Use WebSphere Application Server exception mapping model
Specifies that the application server uses the error mapping facility that is defined in the data store helper to identify errors, and the application server replaces the exceptions that are thrown by the JDBC driver with exceptions that are defined in the error map of the data store helper.
Validate new connections
Specifies whether the connection manager tests newly created connections to the database.
Number of retries
Specifies the number of times you want to retry making the initial connection to a database after the first pretest operation fails.
Retry interval: Validate new connections
If you select Validate new connections, this option specifies the length of time, in seconds, that the application server waits before retrying to make a connection if the initial attempt fails.
Validate existing pooled connections
Specifies whether the connection manager tests the validity of pooled connections before returning them to applications.
Retry interval: Pretest existing pooled connections
If you select Pretest existing pooled connections, this option specifies the length of time, in seconds, to allot to the JDBC driver for validating a connection. Validation is done only once.
Validation by JDBC driver
Specifies that the application server uses the JDBC driver to validate the connections. The JDBC provider must support JDBC 4.0 or greater to use this option. This option is available only if either Validate new connections or Validate existing pooled connections is selected.
Timeout
Validation by SQL string (deprecated)
Specifies an SQL statement that the application server sends to the database to test the connection. Use a query that is likely to have low impact on performance. This option is available only if either Validate new connections or Validate existing pooled connections is selected.
Optimize for get/use/close connection pattern with heterogenous pooling
Optimizes the data source for applications that use the get/use/close connection pattern. This optimization enables the connection pool for the data source to share connections that are in the same transaction. With this optimization pattern, you can share one connection during a transaction even if connections use different connection properties.
If you use the heterogeneous pooling feature, you must first extend the data source definition so that you can specify different custom properties or applications to override non-core properties for the data source. For more information about extending data sources, see the information on extending DB2® data source definitions at the application level.
Retry interval for client reroute
Specifies the amount of time, in seconds, between retries for automatic client reroute.
Maximum retries for client reroute
Specifies the maximum number of connection retries that are attempted by the automatic client reroute function if the primary connection to the server fails. The property is only used when Retry interval for client reroute is set.
Alternate server names
host1,host2
Alternate port numbers
5000,50001
Client reroute server list JNDI name
Specifies the JNDI name that is used to bind the DB2 client reroute server list into the JNDI name space. The DB2 database server uses this name to look up the alternate server name list when the alternate server information is not already in memory. This option is not supported for type 2 data sources.
Unbind client reroute list from JNDI
Used with test connection only. When set to true, the Client reroute server list JNDI name is unbound from the JNDI name space after a test connection is issued.