This post refers to entries in the SystemOut.log /
Maximo always keeps a number of DB connections open ready for its work. When a piece of code wants to execute a piece of SQL it requests a DB connection from Maximo’s DB connection manager.
When the code is finished it should perform some steps to release the connection. If the code doesn’t perform these actions then a DB connection leak can occur. In this situation the connection can’t be freed and Maximo creates a new connection to perform its work.
An observant DBA will spot the rise in connections and ask for it to be investigated.
In some cases the rate of new connections can reach a point where the database will not allow the JVM to create more connections. In this situation there are normally error messages about the JVM being unable to create connections and performance can suffer as a result. In extreme cases the JVM will stop functioning although it may not crash.
Running out of cursors could be seen before the DB connection limit is reached
If the DBA has enabled limits on the number of cursors per session then this could lead to errors about too many open cursors e.g. ORACLE ORA-01000: maximum open cursors exceeded.
A common mistake is to dramatically increase the number of cursors that are allowed, this is bad because it will only delay when the error is generated and it could mean that the software could pass testing but still result in problems when the code goes live.
IBM publishes various technotes that refer to cursor problems. Vetasi support record details of these technotes along with suspected problems that haven’t been confirmed as problems.
Vetasi support can provide consultancy to identify the cause of connection leaks and progress a solution.
DB Connection Watchdog
Maximo 7.5.0.3 onwards exposes a piece of core code called the DB Connection watchdog. This component monitors the connections and flags up connections that have been opened longer than 60 seconds. The watchdog writes details of the code that requested the connection to the logs so that support staff can analyse the code to see if there is a leak and fix it.
Sometimes the warning refers to normal user behaviour e.g. if a user has retrieved a list of records to the List tab and is then working through them then the connection could be open for a number of minutes.
Tip -> It is always worth enabling the DB Connection watchdog by following the instructions below.
Enabling the DB Connection Watchdog
This is enabled via the Logging application and setting the DB connection watchdog to INFO as per these instructions.
- Navigate to the Logging application (System Configuration->Platform Configuration->Logging)
- Turn Maximo DB Connection watchdog logger (log4j.logger.maximo.dbconnection) to INFO
- Apply the logger settings
- Where multiple JVMs are being used the change may not come into effect until the JVMs are restarted
Encourage the users to perform their normal actions and then look for entries like this in the logs:
BMXAA7084I - The DbConnectionWatchDog class has been trying to close the database connection for: 166987944 ms
There may be additional text here indicating the Java code that was being executed when the connection was requested.
These reports may be written infrequently e.g. every 30 minutes so it is important to let the JVM run once the testing has been completed. This has changed in 7.6 and is discussed later.
If you are seeing these BMXAA7084I messages then you should investigate further e.g. raise a PMR providing the detailed steps and the log files. The entries can then be evaluated and the code can be fixed if appropriate. When you raise the PMR it is very helpful to say how often the operation occurs as that can help the developers prioritise the investigation/fix. It an operation is being executed 1,000 times a day e.g. when saving a SR
Enabling this also generates this summary of the number of open connections.
[INFO] [MAXIMO] [] BMXAA7088I - The DbConnectionWatchDog class recorded 37 connection references in this period, currently there are 0 open references, 0 long running references, 0 long running connections, and 0 references that were closed by the garbage collector finalizer.
This summary can be very useful when working with a DBA as you can watch the connection counts and identify users/operations who may have been performing actions that used the connections.
The types of leaks highlighted by the DB Connection watchdog are too complex to explain in a blog and the Java code is not available when the code is in IBM provided Java code.
Vetasi support can provide specialist training on the causes of leaks and how to resolve them.
Closing the leaked connections
Maximo 7.5.0.3 introduces functionality to allow leaked connections to be closed automatically after they have been open for a long period of time.
This is controlled via several system properties:
- mxe.db.closelongrunconn - default is false but needs to be set to true
- mxe.db.longruntimelimit - default 180 minutes.
This functionality is described in detail in this IBM blog “Maximo -- How to solve database connection leak?” posting
https://www.ibm.com/developerworks/community/blogs/a9ba1efe-b731-4317-9724-a181d6155e3a/entry/Maximo_How_to_solve_database_connection_leak?lang=en
It is unclear what the effect these closures will have on the code that opened the requests.
Using this automatic closure functionality should be considered as a short term solution as it is better to prevent the problem occurring rather than clean up afterwards.
Vetasi support – Awareness of possible connection leaks
Vetasi support monitor reports of the DB connection leaks and use them when analysing the logs.
We also monitor systems for leaks and raise PMRs to ensure the leaks are fixed.
Vetasi maintain a list of known or suspected database leaks to enable quick identification of these problems.
The next article will explain about some of the changes in 7.6 related to DB connections. It includes information on BIRT which is relevant to 7.1/7.5 report developers.
If you like this article then please share or like it. Why not look at the other articles listed in this index.