WebSphere Application Server data source properties collection

Use this page to view the WAS data source properties. These properties apply to the WAS connection, rather than to the database connection.

To view this administrative console page, click Resources >JDBC Providers > JDBC_provider > Data sources > data_source > WAS connection properties.

 

Configuration tab

Statement Cache Size

Number of free statements that are cached per connection.

The WAS data source optimizes the processing of prepared statements. A prepared statement is a precompiled SQL statement that is stored in a prepared statement object. This object is used to efficiently execute the given SQL statement multiple times.

If the cache is not large enough, useful entries are discarded to make room for new entries. To determine the largest 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 prepared statements that are 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, the more statements your application has, the larger the cache should be. For example, if the application has 5 SQL statements, set the statement cache size to 5, so that each connection has 5 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 configuration settings. Note that The higher the statement cache, the more system resources are delayed. Therefore, if you set the number too high, you could lack resources because your system is not able to open that many prepared statements.

In test applications, tuning the statement cache improved throughput by 10-20%. However, because of potential resource limitations, this might not always be possible.

Data type Integer
Default Depends on the database. Most are 10. Informix V7.3, 9.2, or 9.3 without latest fix must be 0. A default of 0 means there is no cache statement.

Enable Multithreaded Access Detection

If checked, the application server detects the existence of access by multiple threads.

Enable WebSphere Connection Pooling

If checked, the application server sets up connect pools for this datasource.

Enable Database Reauthentication

If checked, there is not be an exact match on connections retrieved out of the WAS connection pool (that is, connection pool search criteria do not include user name and password). Instead, the reauthentication of connection is done in the doConnectionSetupPerTransaction() of the DataStoreHelper class. Note that WAS runtime does NOT provide connection reauthentication implementation. Therefore, when this box is checked you MUST extend the DataStoreHelper class to provide implementation of the doConnectionSetupPerTransaction() method where the reauthentication takes place. Failure to do that results in wrong connections being handed out to users. For more information, refer to the API documentation for com.ibm.websphere.rsadapter.DataStoreHelper#doConnectionSetupPerTransaction(...).

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

Enable JMS One Phase Optimization Support

If checked, the application server allows JMS to get optimized connections from this data source. This property prevents JDBC applications from sharing connections with CMP applications.

PreTest Connections

If checked, the application server tries to connect to this data source before it attempts to send data to or receive data from this data source. If you select this property, one can specify how often, in seconds, the application server retries to make a connection if the initial attempt fails.

PreTest Connection Retry Interval

When PreTest Connection is checked, use this property to specify how long, in seconds, the application server waits before retrying to make a connection if the initial attempt fails.

PreTest SQL String

Specifies the string of data that the application server sends to the database to test the connection.

Data type Integer


 

See Also


Resource adapter
JDBC providers
Data sources

 

Related Tasks


Creating and configuring a data source using the administrative console
Configuring connection factories for resource adapters within applications
Configuring J2EE Connector connection factories in the administrative console