WebSphere Application Server V8.5 Administration and Configuration Guide for the Full Profile

376 WebSphere Application Server V8.5 Administration and Configuration Guide for the Full Profile

9.8 WebSphere Application Server data source properties

You can set the properties that apply to the WebSphere Application Server connection, rather

than to the database connection. To access the connection pool properties, navigate to

Resources  JDBC  Data sources, and click the data source name. Click WebSphere

Application Server data source properties in the Additional Properties section. See

Figure 9-24 on page 373.

Clicking the link opens the window shown in Figure 9-25.

Figure 9-25 WebSphere data source custom properties

Specify the following information:

 Statement Cache Size

Specify the number of prepared statements that are cached per connection. A prepared

statement is a precompiled SQL statement that is stored in a prepared statement object.

This object is used to execute the given SQL statement multiple times. The WebSphere

Application Server data source optimizes the processing of prepared statements.

In general, the more statements your application has, the larger the cache must be. For

example, if the application has five SQL statements, set the statement cache size to 5 so

that each connection has five statements.

Statement Cache Size: This setting is vital to performance of the database and most

likely requires tuning to suit the specific application. In general, the default is not high

enough for best performance.

Chapter 9. Accessing relational databases from WebSphere 377

 Enable multi-threaded access detection

If you enable this feature, the application server detects the existence of access by

multiple threads.

 Enable database reauthentication

Connection pool searches do not include the user name and password. If you enable this

feature, a connection can still be retrieved from the pool, but you must extend the

DataStoreHelper class to provide implementation of the

doConnectionSetupPerTransaction() method where the reauthentication takes place.

Connection reauthentication can help improve performance by reducing the impact of

opening and closing connections, particularly for applications that always request

connections with different user names and passwords.

 Enable JMS one-phase optimization support

Activating this support enables the Java Message Service (JMS) to get optimized

connections from the data source. Activating this support also prevents JDBC applications

from obtaining connections from the data source. For further explanation of JMS

one-phase support, refer to the article entitled “Sharing connections to benefit from

one-phase commit optimization” at the following website.

http://pic.dhe.ibm.com/infocenter/wasinfo/v8r5/index.jsp?topic=%2Fcom.ibm.websp

here.express.doc%2Fae%2Ftjm0280_.html

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

 Non-transactional data source

Setting the flag to true causes the Application Server to never enlist the connections from

the 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 roll back the transaction that they started. This

property is rarely set to true.

 Error detection model

The error detection model was expanded and the data source has a configuration option

that you can use to select the exception mapping model or the exception checking model

for error detection.

 Connection validation properties

There are two properties, and you can choose both. If you select the Validate new

connections option, the connection manager attempts to communicate to the database

using the new connection allocated, before returning the connection to the application for

use. If you select this property, you can specify how often, in seconds (interval), the

connection attempt will be retried, and how many attempts are made.

If you select the Validate existing pooled connections option, when the connection

manager reuses an existing connection, it attempts to communicate to the database using

that connection before returning it to the application for use. If you select this property, you

can specify how often, in seconds (interval), the connection attempt will be retried.The

pretest SQL string is sent to the database to test the connection.

Note: Connection validation by SQL query is deprecated in WebSphere Application

Server V8.0.