How to detect and resolve database connection leak?

There are 2 very detailed posts on IBM DeveloperWorks written by Manjunath about database connection leaks – how to detect them and how to resolve them.

Maximo — How to detect database connection leak
Maximo — How to solve database connection leak

Here is the crux –

To free up locked database connect in maximo, IBM has introduce 3 new system properties (7.5.0.3 onwards) –

mxe.db.closelongrunconn – default is false.

mxe.db.longruntimelimit – default 180 minutes.

mxe.db.detectlongrunconninterval – default is 30 minutes. This is the frequency in which the long running connections are checked. Cannot be less then 30 minutes.

The mxe.db.closelongrunconn property when set to true will close the lost connections if the connections have been held for greater than mxe.db.longruntimelimit time and were not used by any processes in that time duration.

To detect database connection leak, turn Maximo dbconnection watchdog logger to INFO and collect the logs for 1-2 days. If there are any connection leaks the logs will show some thing like this –

[INFO] BMXAA7084I – The DbConnectionWatchDog class has been trying to close the database connection for: 230233001 ms

DbConnectionWatchDog:Db Connection reference id=436107 SPID=397
Create time:1302986384636
Life time:230233001 ms

The Logger indicates that the connection as being held for 230233001 ms i.e. approximately 64 hours. By looking in the logs at approximately 64 hours back, one should be able to find the stack trace of where this connection was established