Server data source properties

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.

To access this admin console page complete one of the following paths:

Statement cache size

Number of statements that can be cached per connection. The appserver caches a statement after you close that statement.

The WAS data source optimizes the processing of prepared statements and callable statements by caching those statements that are not used in an active connection. Both statement types help maximize the performance of transactions between the application and datastore.

  • A prepared statement is a precompiled SQL statement that is stored in a PreparedStatement object. The appserver uses this object to run the SQL statement multiple times, as required by the 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 appserver uses this object to run a stored procedure multiple times, as required by the 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 the 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 you never have cache discards. In general, configure a larger cache for applications with a greater number of statements.

We can also use the Tivoli Performance Viewer to minimize cache discards. Use a standard workload that represents a typical number of incoming client requests, use a fixed number of iterations, and use a standard set of settings.

The higher the statement cache, the more system resources are delayed. Therefore, if we set the number too high, then we might lack resources because the system cannot open multiple prepared statements.

If there is a particular statement that you do not want the application server to cache, configure the statement's poolability hint to false. The appserver 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 might not always be possible.

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

When you check this option, the appserver detects the existence of access by multiple threads.

Enable database reauthentication

Indicates that the exact match on connections retrieved out of the application server's 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 appserver does not provide a connection reauthentication implementation at run time. Therefore, when you check this box, extend the DataStoreHelper class to provide implementation of the doConnectionSetupPerTransaction() method where the reauthentication occurs. If we do not complete this process, the appserver might return unusable connections.

See, refer to the API documentation for the com.ibm.websphere.rsadapter.DataStoreHelper#doConnectionSetupPerTransaction method.

Connection reauthentication can help improve performance by reducing the overhead of opening and closing connections, particularly for applications that frequently request connections with different user names and passwords.

Avoid trouble: We cannot enable database reauthentication if we select TrustedConnectionMapping for the mapping configuration alias.

Enable JMS one-phase optimization support

When you check this option, the appserver allows Java messaging 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.

Manage cached handles

Whether the container tracks cached handles, which are connection handles that an application component holds active across transaction and method boundaries. Use this property to debug connection problems, but tracking handles can cause large performance overhead during run time.

If the Manage cached handles property is selected in the admin console, and you deselect it, the field will no longer be visible for resources that are at V7.0 of the appserver. This field is only displayed if the manageCachedHandles property is set to true in the resources.xml file. To make the field available, change the value for the manageCachedHandles entry from false to true in the resources.xml file, or enter the following Jython command from wsadmin:

AdminConfig.modify(myDataSourceVariable, '[[manageCachedHandles "true"]]')

Supported configurations: For any resources that are running at V6.x of the appserver, the Manage cached handles property will always be visible. For example, if we have a node that is at V 6.1, the entry in the resources.xml file will not affect how the field is displayed in the admin console.

For an alternative method of debugging problems, use the multi-thread and cross-component diagnostic alerts to detect violations in the Java Connectivity Architecture (JCA) model. To enable these alerts, select those options from Servers > Application servers > application_server > Performance > Performance and Diagnostic Advisor Configuration > Performance and Diagnostic Advice configuration panel. These alerts force the connection manager to manage cached handles, detect the connection conditions, and send alerts.

For these alerts to be active also select Enable Performance and Diagnostic Advisor Framework (Runtime Performance Advisor) from the Servers > Application servers > application_server > Performance > Performance and Diagnostic Advisor Configuration panel.

Log missing transaction context

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 EE model connection requirements.

Non-transactional data source

The appserver will not enlist the connections from this data source in global or local transactions. Applications must explicitly call setAutoCommit(false) on the connection if they want to start a local transaction on the connection, and they must commit or rollback the transaction that they started.

Avoid trouble: Set this property to true in rare circumstances, but the Java Persistence API (JPA) requires both JTA and non-JTA data sources.

Use WAS exception checking model

The appserver 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 defined in the error map of the data store helper.

Use WAS exception mapping model

The appserver uses the error mapping facility that is defined in the data store helper to identify errors, and the application server will replace the exceptions that are thrown by the JDBC driver with exceptions defined in the error map of the data store helper.

Supported configurations: This error detection model functions with JDBC V 3.0 and earlier.

Validate new connections

Whether the connection manager tests newly created connections to the database.

Number of retries

Number of times you want to retry making the initial connection to a database after the first pretest operation fails.

Retry interval

If we select Validate new connections, this option specifies the length of time, in seconds, that the appserver waits before retrying to make a connection if the initial attempt fails.

Validate existing pooled connections

Whether the connection manager tests the validity of pooled connections before returning them to applications.

Retry interval

If we 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 by JDBC driver

The appserver will use the JDBC driver to validate the connections. The JDBC provider must support JDBC 4.0 or greater to use this option.

Timeout

Timeout in seconds for testing connections (either new or pooled by the appserver) to the database. If the timeout expires before validating then the connection is considered unusable. If retries are configured, the full value of the timeout applies to each retry. A value of 0 indicates the JDBC driver does not impose a timeout on validation attempts.

Supported configurations: This option is only available for JDBC drivers that are JDBC 4.0 compliant.

Validation by SQL string (deprecated)

Specifies an SQL statement that the appserver sends to the database to test the connection. Use a query that is likely to have low impact on performance.

Optimize for get/use/close/connection pattern with heterogenous pooling

The appserver will use the get/use/close/connection pattern. This allows connection pooling for the appserver to share connections in the same transaction. This optimization pattern allows one connection to be shared during a transaction even when connections use different connection properties.

The heterogeneous pooling feature allows you to extend the data source definition so that we can specify different custom properties or allow applications to override non-core properties for the data source.

Supported configurations: This field is only available for DB2 data sources.

Retry interval for client reroute

Amount of time, in seconds, between retries for automatic client reroute.

Supported configurations: This field is only available for DB2 data sources.

Maximum retries for client reroute

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.

Supported configurations: This field is only available for DB2 data sources.

Alternate server names

List of alternate server name or names for the DB2 server. If more than one alternate server name is specified, the names must be separated by commas. For example:

host1,host2

Supported configurations: This field is only available for DB2 data sources.

Alternate port numbers

List of alternate server port or ports for the DB2 server. If more than one alternate server port is specified, the ports must be separated by commas. For example:

5000,50001

Supported configurations: This field is only available for DB2 data sources.

Client reroute server list JNDI name

JNDI name used to bind the DB2 client reroute server list into the JNDI name space. The DB2 database server will use 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.

Supported configurations: This field is only available for DB2 data sources.

Unbind client reroute list from JNDI

Used with test connection only. When set to true, the Client reroute server list JNDI name will be unbound from the JNDI name space after a test connection is issued.

Supported configurations: This field is only available for DB2 data sources.





 

Related concepts


Relational resource adapters and JCA
JDBC providers
Data sources

 

Related tasks


Set a data source
Set connection factories for resource adapters within applications
Set Java EE Connector connection factories in the admin console

 

Related


Custom property settings
Advice settings