Connection Manager diagnostic options for V4.0 data source

Resolving The Problem

The WebSphere Connection Manager provides some new diagnostic option settings that allow you to gather information on what pieces of your applications might be creating orphaned connections, creating inadvertent 2PC transactions or holding connections for longer than expected.

	Data sources (Version 4) running under the connection manager (CM) architecture
	Data sources running under the J2EE Connector Architecture (JCA) connection manager

---

Data sources (Version 4) running under the CM architecture
The following applies only to the WebSphere Application Server V4.0 style data sources, not the J2C data sources. This means that if you are using any release of WebSphere Application Server V5.0 or higher, this function works only if you are using the backward-compatible V4.0 style data sources for database connectivity versus the J2C data sources.

There are three different types of diagnostic information that can be gathered. These options can be used individually or in combination.

The first diagnostic option prints an informational message to the standard out log file each time a connection is returned to the connection pool by the WebSphere Orphan alarm.

The second diagnostic option also prints a message to the standard out log file when a connection is collected by the WebSphere Orphan alarm, but includes a stack trace snapshot taken when the getConnection request is fulfilled. This allows you to analyze which pieces of your code are not correctly returning connections.

The third diagnostic option prints the getConnection stack trace snapshots for each connection in use when a ConnectionWaitTimeoutException is issued. This allows you to analyze which pieces of code are holding connections at the time of the exception. This might indicate connections being held longer than necessary, or being orphaned. It might also indicate normal usage, in which case you should increase the size of your connection pool, or of your Connection wait timeout.

To activate the connection manager diagnostic options, choose a data source you want to monitor and add the property diagOptions with one (or a combination) of the values below.

Value	Description
0	No CM Diagnostics are enabled
1	Orphaned Connection Notification, a short message each time a connection is orphaned.
2	Orphaned Connection Application Code Path Tracing
4	Connection Wait Timeout Code Path Tracing

To set more than one value at a time, add the numeric values and set as the value in the console. For example, to turn on both the orphan code path (2) and the connection wait code path (4), set the diagOptions to 6 in the console (2+4=6). To turn on orphan notifications and connection wait code path tracing, set diagOptions to 5 (1+4=5).

After setting the diagnostic options on the data source you want to monitor, you must restart the Application Server using that data source for the changes to be activated. When any of the diagnostic options are activated, a message displays in the server standard out file upon server startup indicating what options are activated. All diagnostic output go to the standard out file also.

The performance impact of turning on the diagnostic options ranges from 1%-to-5% performance degradation, depending on which options and how many are activated. Option 1 is at the low end of the performance impact range, options 2 and 4 fall at the high end of the range.

Back to top

---

Data sources running under the JCA connection manager
The following explains how to use the diagnostics in a JCA connection manager.

• Using the Connection Leak Detection in J2C style data sources
  The best way of determining the root cause of a saturated connection pool or ConnectionWaitTimeoutException is to recreate the error using a light weight trace specification:
  
  

ConnLeakLogic=all=enabled


This trace specification stores diagnostic data of all requests for a connection from the connection pool, and outputs them to the trace log file when a ConnectionWaitTimeoutException occurs. It also displays the total time that each connection is in-use.

Once the diagnostic data has been captured, it is then possible to track down the code paths where connection leaks seem prominent. For more information on detecting and preventing connection leaks in application code, see the Connection pool tuning and management problems slide deck.


• Using the Inadvertent 2PC Transaction Detection
  The 2PC transaction detection is automatically enabled as part of the First Failure Data Capture (FFDC) framework in WebSphere Application Server. The administrator does not have to turn on special function for this feature to run.
  
  Detecting the root cause of this second scenario can be difficult. In this situation, the new function prints out the details of each connection to the FFDC or StdOut logs in WebSphere Application Server V4.0 releases. (The FFDC logs are located in the install_root/logs/ffdc directory.) This helps determine what setting is different between multiple connections.
  
  The following is some sample output:
  
  

PoolName = jdbc/testV5DS com.ibm.ws.rsadapter.spi.WSConnectionRequestInfoImpl@9e6090f   UserName     = user2   Password     = ******   Catalog      = null   IsReadOnly   = null   TypeMap      = null   NoEnlist     = false   Holdability  = DEFAULT CURSOR HOLDABILITY VALUE (0)   ConfigID     = 0   Isolation    = REPEATABLE READ (4)   Support isolation switching  = false   Handle type  = java.sql.Connection = Subject = null Container Managed Auth = No MCWrapper = MCWrapper id 3bb860b5  Managed connection com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@1aef60a0  State:STATE_TRAN_WRAPPER_INUSE PoolName = jdbc/testV5DS com.ibm.ws.rsadapter.spi.WSConnectionRequestInfoImpl@9e6090f   UserName     = user1   Password     = ******   Catalog      = null   IsReadOnly   = null   TypeMap      = null   NoEnlist     = false   Holdability  = DEFAULT CURSOR HOLDABILITY VALUE (0)   ConfigID     = 0   Isolation    = REPEATABLE READ (4)   Support isolation switching  = false   Handle type  = java.sql.Connection = Subject = null Container Managed Auth = No

Notes:
1. In this case, the first connection request had a user name of user2 while the second had a user name of user1. This difference in user names results in two connections being returned to the application. To correct this, the application must be modified to request both connections using the same user name.
   
   
2. All passwords will be blocked out to preserve the security of passwords. If the passwords in both connections are different, a message is printed under the connection information (for example: “Passwords do NOT match!”).

There can often be a lot of FFDC files, and searching through all of them can be tedious. The best way to quickly identify the FFDC log file that contains the connection information from this error is to do a text search for the error message ID, WTRN0062E, in the FFDC directory.

Back to top

---