Maximo -- How to detect database connection leak?

Body

Maximo uses its own internal database connection pool mechanism and does not use the application server database connection pool. In Maximo and Maximo based products such as Smart Cloud Control Desk, there can be database connection leaks. These database connection leaks could be caused by incorrect code as well as memory leaks.  In situations where there are database connection leaks, it is very difficult to identify the code where the connection leak is occurring.

The Maximo dbconnection watchdog logger can be used to find the database connection leaks.  Turn Maximo dbconnection watchdog logger to INFO. Collect Maximo logs for a 24 hour period.

If Maximo logs indicate lost connection and connection pool's used connections growing, it indicates a database connection leak.

Example,

[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

In this example, the database connection watchdog logger is indicating the connection as being held for 230233001 ms i.e. approximately 64 hours. This database connection used by some Maximo transaction is not released back to the connection pool. This connection is considered a lost database connection as it is held for a long time and is not available for freeing to the free connection pool for reuse. 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. An example is

[INFO] BMXAA7084I - The DbConnectionWatchDog class has been trying to close the database connection for: 73000 ms
DbConnectionWatchDog:Db Connection reference id=436107 SPID=397
Create time:1302986384636
Create stack trace:
psdi.server.DbConnectionWatchDog$ConnectionData.<init>(DbConnectionWatchDog.java:40)
psdi.server.DbConnectionWatchDog.addConnection(DbConnectionWatchDog.java:206)
psdi.server.ConRef.incrementRefCount(ConRef.java:129)
psdi.server.DBManager.getConnectionDetail(DBManager.java:1268)
psdi.server.DBManager.getConnection(DBManager.java:1179)
psdi.server.AppService.getDBConnection(AppService.java:548)
psdi.mbo.MboSet.getNextRecordData(MboSet.java:2794)
psdi.mbo.MboSet.fetchMbosActual(MboSet.java:2528)
psdi.mbo.MboSet.fetchMbos(MboSet.java:2494)
psdi.mbo.MboSet.getMbo(MboSet.java:1821)
psdi.mbo.MboSet.isEmpty(MboSet.java:3777)
psdi.app.asset.Asset.getParentAsset(Asset.java:7148)
psdi.app.asset.Asset.getWarrantyInfo(Asset.java:7082)
psdi.app.asset.Asset.getWarrantyInfo(Asset.java:7067)
psdi.app.workorder.WO.updateWarrantyContracts(WO.java:9777)
psdi.app.workorder.FldWOAssetnum.performWarrantyContractCheck(FldWOAssetnum.java:380)
psdi.app.workorder.FldWOAssetnum.action(FldWOAssetnum.java:201)
psdi.plust.app.workorder.PlusTFldWOAssetnum.action(Unknown Source)

......
 

With the dbconnection watchdog logger, the log files show exceptions as above and these indicate where the first occurrence of database connection access in the code. This exception indicates the code area where the connection leak is. If these connection leaks are in core product, the information can be provided to your product support team to get a fix. If the connection leak is in a custom code area, it should be addressed by your custom code development team.

The log file also indicates total number of connections used by the application server as follows.  This data is for the application server and not for the entire Maximo system.

[INFO] BMXAA7088I - The DbConnectionWatchDog class recorded 44 connection references in this period, currently there are 44 open references, 43 long running references, 43 long running connections, and 0 references that were closed by the garbage collector finalizer.
[INFO] DBManager Total number of connections: 49 Number of free connections = 5 Number of used connections = 44

The corresponding user count for the server shows as follows:

[INFO] BMXAA6369I - Server host: A.B.C.D. Server name: MXServer. Number of users: 13. WebClientSessions: (13)

In this situation, for 13 users, there are 43 long running connection.  If the long running references are significantly higher than the number of users (2 to 3 times) and constantly growing it indicates a database connection leak.