Technical Blog Post
Maximo -- How to solve database connection leak?
Maximo manages database connection pool internally in its code and does not use the application server database connection pool. At times, there are database connection leaks due to incorrect code or memory leaks that result in database connections increasing and stopping the database.
The database connection leaks should be identified and fixed in the code. It can be accomplished by using the dbconnection watchdog logger. The log files can indicate where the connection leaks might be.
Ideally, all database connection leaks should be fixed by fixing the offending code, but sometimes it may be difficult to find the connection leak and it may be necessary to find an alternate solution to release the leaking database connection. To address this problem, from Maximo 18.104.22.168 onwards, three new properties are added and through these properties, Maximo application server database connection pool will release the lost connection automatically.
The Maximo system properties used for this feature are:
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.
Note: If the above property is not created as part of the fixpack application, you must define the property in the system properties application. The value of this property cannot be less than 30 minutes.
To set the above properties, in Maximo, go to System Configuration --> Platform Configuration --> System Properties. Change the values as described below. From the Select Action menu, apply Live Refresh to apply the changes.
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. If this feature is enabled through the above property, Maximo server will check for any lost connections in a 30 minute frequency ( this is not configurable ). If the application server finds any database connection held for greater than mxe.db.longruntimelimit, then it will check in the database to make sure the connection was actually not used by any processes in that time duration. If the connection was not used by any process, then it sets the Maximo database connection reference count to zero and returns the connection back to the connection pool. With the database connection back in the connection pool, it can be used for later database connection needs or be closed if there are excess free connections.
If database connection is held for a long time but is being used by some process, then the database will indicate usage of the connection. In this case, Maximo will not return the connection back to the database connection pool and leave it as is. This makes sure database connections used by some long running process are not closed by accident.
For Maximo application server to check the database connection usage, Maximo database account should have permission to read the following database system tables.
Oracle : v$session
SQL Server : master..sysprocesses
DB2 : SYSIBMADM.SNAPAPPL_INFO
DB2 monitoring needs to be enabled to trace usage.
The specific monitor element that must be captured is the "appl_idle_time" monitor element (http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.mon.doc/doc/r0001193.html)
By default mxe.db.closelongrunconn property is set to false. Maximo will not automatically close any database connections that are lost. Note that Maximo connection pool mechanism closes unused free connections and decreases the connection pool to the MaxFreeConnection property level. This is different than closing the lost connections. Releasing unused database connections is part of the connection pool management.
The default value for mxe.db.longruntimelimit is 180 minutes. This time is used to check if the database connection was actually used by any process at the database level. If the database connection was not used at the database level, then it will be returned to the Maximo database connection pool. This value can be adjusted as needed.
If there are database connections outside of Maximo application server, Maximo is unaware of them and they are outside the scope of Maximo database connection pool management.
With this feature the long running database connection leak should be resolved without having to restart the Maximo server to release the database connections.