WAS data source properties

 

+

Search Tips   |   Advanced Search

 

To configure data source properties to activate services....

 

Configuration tab

Statement cache size

Number of statements that can be cached per connection.

WAS caches a statement after the user closes it.

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

  • Prepared statement

    Precompiled SQL statement that is stored in a PreparedStatement object. WAS 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.

  • Callable statement

    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. WAS 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 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 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, the more statements your application has, the larger the cache should be.

This statement cache size setting is different from WAS V4.0.x. In V4.0.x, the maximum number of possible prepared statements is cached for the data source within an appserver. In V5 and higher, statement cache size is defined on a given physical connection.

You 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.

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 versions 7.3, 9.2, 9.3, and 9.4, without the respective latest fixes, must be 0. A default of 0 means there is no cache statement.

Enable multithreaded access detection

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

Enable database reauthentication

If checked, there cannot be an exact match on connections retrieved out of the WAS connection pool. Connection pool search criteria do not include user name and password. The reauthentication of connection is done in the...

doConnectionSetupPerTransaction()

...of the DataStoreHelper class.

WAS runtime does NOT provide connection reauthentication implementation. Therefore, when this box is checked you MUST extend the DataStoreHelper class. Failure to do that results in wrong connections being handed out to users.

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 appserver allows JMS to get optimized connections from this data source. This property prevents JDBC applications from sharing connections with CMP applications.

Cached handles

Specify whether the container tracks cached handles, which are connection handles that an application component holds active across transaction and method boundaries. Use this property for debugging purposes. Tracking handles can cause large performance overhead during runtime.

Log missing transaction context

Specify 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 J2EE programming model connection requirements.

PreTest existing pooled connections

If checked, the appserver 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, you can specify how often, in seconds, the appserver retries to make a connection if the initial attempt fails.

Retry interval

When PreTest existing pooled connections is checked, use this property to specify how long, in seconds, the appserver waits before retrying to make a connection if the initial attempt fails.

PreTest SQL string

String of data that the appserver sends to the database to test the connection. Because the pretest operation is designed to test only that a connection makes a round trip to and from the database server, use a simple, low-impact query. A good choice for a PreTest SQL string is a generic query that has minimal potential impact on performance of the appserver.

Data type Integer



 

Related concepts

Resource adapters
JDBC providers
Data sources

 

Related tasks

Configure a data source using the console
Connection factories for resource adapters within applications
J2EE Connector connection factories

 

Related Reference

Custom property settings