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, by selecting the WebSphere Application Server data source properties link under the Additional Properties section of the data source configuration page.

• 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 should be. For example, if the application has five SQL statements, set the statement cache size to 5, so that each connection has five statements.


• Enable multithreaded 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 overhead of opening and closing connections, particularly for applications that always request connections with different user names and passwords.


•  Manage cached handles: When you call the getConnection() method to access a database, you get a connection handle returned. The handle is not the physical connection, but a representation of a physical connection. The physical connection is managed by the connection manager. A cached handle is a connection handle that is held across transaction and method boundaries by an application. This setting specifies whether cached handles should be tracked by the container. This can cause overhead and only should be used in specific situations. For more information about cached handles, see the Connection Handles topic in the Information Center.


• Transaction context logging: The J2EE programming model indicates that connections should always have a transaction context. However, some applications do not have a context associated with them. This option tells the container to log that there is a missing transaction context in the activity log when the connection is
  obtained.


• Pretest existing pooled connections: If you check this box, the application server tries to connect to this data source before it attempts to send data to or receive data from this source. If you select this property, you can specify how often, in seconds, the application server retries to make a connection if the initial attempt fails. The pretest SQL string is sent to the database to test the connection.


• Pretest new connections: If you check this box, the application server test the initial connection to database. If you select this property, you specify how often, in seconds, the application server retries to make a connection and how many times it tries. The pretest SQL string is sent to the database to test the connection.

Data Source Connection pool properties

You can configure connection pool related properties from the Connection pool screen.

• Connection Timeout: Specify the interval, in seconds, after which a connection request times out and a ConnectionWaitTimeoutException is thrown. This can occur when the pool is at its maximum (Max Connections) and all of the connections are in use by other applications for the duration of the wait. For example, if Connection Timeout is set to 300 and the maximum number of connections is reached, the Pool Manager waits for 300 seconds for an available physical connection. If a physical connection is not available within this time, the Pool Manager throws a ConnectionWaitTimeoutException.


• Max Connections: Specify the maximum number of physical connections that can be created in this pool. These are the physical connections to the back-end database. Once this number is reached, no new physical connections are created and the requester waits until a physical connection that is currently in use is returned to the pool, or a ConnectionWaitTimeoutException is thrown. For example, if Max Connections is set to 5, and there are five physical connections in use, the Pool Manager waits for the amount of time specified in Connection Timeout for a physical connection to become free. If, after that time, there are still no free connections, the Pool Manager throws a ConnectionWaitTimeoutException to the application.


• Min Connections: Specify the minimum number of physical connections to be maintained. Until this number is reached, the pool maintenance thread does not discard any physical connections. However, no attempt is made to bring the number of
  connections up to this number. For example, if Min Connections is set to 3, and one physical connection is created, that connection is not discarded by the Unused Timeout thread. By the same token, the thread does not automatically create two additional physical connections to reach the Min Connections setting.


• Reap Time: Specify the interval, in seconds, between runs of the pool maintenance
  thread. For example, if Reap Time is set to 60, the pool maintenance thread runs every 60 seconds. The Reap Time interval affects the accuracy of the Unused Timeout and Aged Timeout settings. The smaller the interval you set, the greater the accuracy. When the pool maintenance thread runs, it discards any connections that have been unused for longer than the time value specified in Unused Timeout, until it reaches the number of connections specified in Min Connections. The pool maintenance thread also discards any connections that remain active longer than the time value specified in Aged Timeout.


• Unused Timeout: Specify the interval in seconds after which an unused or idle connection is discarded. For example, if the unused timeout value is set to 120, and the pool maintenance thread is enabled (Reap Time is not 0), any physical connection that remains unused for two minutes is discarded. Note that accuracy of this timeout, as well as performance, is affected by the Reap Time value. See the Reap Time bullet for more information.


• Aged Timeout: Specify the interval in seconds before a physical connection is discarded, regardless of recent usage activity. Setting Aged Timeout to 0 allows active physical connections to remain in the pool indefinitely. For example, if the Aged Timeout value is set to 1200, and the Reap Time value is not 0, any physical connection that remains in existence for 1200 seconds (20 minutes) is discarded from the pool. Note that accuracy of this timeout, as well as performance, is affected by the Reap Time value. See Reap Time for more information.


• Purge Policy: Specify how to purge connections when a stale connection or fatal connection error is detected. Valid values are EntirePool and FailingConnectionOnly. If you choose EntirePool, all physical connections in the pool are destroyed when a stale connection is detected. If you choose FailingConnectionOnly, the pool attempts to destroy only the stale connection. The other connections remain in the pool. Final destruction of connections that are in use at the time of the error might be delayed. However, those connections are never returned to the pool.

Data Source Custom Properties

You can set the database vendor specific custom properties on data source by clicking on custom properties link on data source definition page.



This is what i see on the custom properties page for Apache Derby data source. I can enable the data source level SQL trace from here, Ex. i can turn on the trace to see what all SQL queries are getting fired on the SQL connection from this data source,
the input parameters and the result set.

As you can see there is some help available for each of the custom property that you can set


I did set traceLevel to 4, that enables only TRACE_RESULTSET_CALLS, and i set value of traceFile to c:/temp/derbytrace.log file. After setting these properties i tried accessing the data source and this is the log that got generated in the derbytrace.log

Similarly you can set custom properties specific to your data source to generate trace

[derby][Time:1250572072484][Thread:WebContainer : 0][ClientConnectionPoolDataSource@5000500] getPooledConnection () called
[derby][Time:1250572072593][Thread:WebContainer : 0][ClientConnectionPoolDataSource@5000500] getPooledConnection () returned org.apache.derby.client.ClientPooledConnection@78987898
[derby][Time:1250572072593][Thread:WebContainer : 0][ClientPooledConnection@78987898] getConnection () called
[derby][Time:1250572072593][Thread:WebContainer : 0][ClientPooledConnection@78987898] getConnection () returned org.apache.derby.client.am.LogicalConnection@77807780
[derby][Time:1250572072609][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] getMetaData () returned DatabaseMetaData@6fe66fe6
[derby][Time:1250572072734][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] getHoldability () returned 1
[derby][Time:1250572072734][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] getAutoCommit () returned true
[derby][Time:1250572072734][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] getCatalog () returned null
[derby][Time:1250572072734][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] isReadOnly () returned false
[derby][Time:1250572072734][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] setTransactionIsolation (4) called
[derby][Time:1250572072734][Thread:WebContainer : 0][org.apache.derby.client.am.Statement@2cc62cc6] executeUpdate (SET CURRENT ISOLATION = RS) called
[derby][Time:1250572072750][Thread:WebContainer : 0][org.apache.derby.client.am.Statement@2cc62cc6] executeUpdate () returned 0
[derby][Time:1250572072781][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] clearWarnings () called
[derby][Time:1250572072781][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] createStatement (1003, 1007) called
[derby][Time:1250572072781][Thread:WebContainer : 0][org.apache.derby.client.net.NetConnection@20242024] createStatement () returned Statement@1b401b4
[derby][Time:1250572072796][Thread:WebContainer : 0][org.apache.derby.client.am.Statement@1b401b4] executeQuery (SELECT * FROM DERBY.EMPLOYEE) called
[derby][Time:1250572072828][Thread:WebContainer : 0][org.apache.derby.client.am.Statement@1b401b4] executeQuery () returned ResultSet@1bae1bae
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] next () called
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] next () returned true
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject (1) called
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject () returned 1
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject (2) called
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject () returned Sunil Patil
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] next () called
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] next () returned true
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject (1) called
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject () returned 2
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject (2) called
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] getObject () returned Alden Taylor
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] next () called
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] next () returned false
[derby][Time:1250572072843][Thread:WebContainer : 0][ResultSet@1bae1bae] close () called
[derby][Time:1250572072843][Thread:WebContainer : 0][org.apache.derby.client.am.Statement@1b401b4] close () called