I'm always happy to help however I can. Long term, understanding where these leaks are coming from is beneficial but I understand trying to alleviate the problem first.
1) I believe you're on 7.6.1.1 IFIX 04 which does not have the fix for the issue I reported. You need IFIX 09 or higher on 7.6.1.1 so if you're unable or don't want to patch to 7.6.1.2 you could utilize that, though being honest, the difference between getting to fix pack 7.6.1.2 and a higher 7.6.1.1 IFIX isn't that much different so I would go to the newer version personally.
When a database is closed outside of Maximo, that connection can stay in the connection pool that Maximo maintains. The bug was that Maximo would not always release these connections so cron tasks, MIF imports, user sessions, etc. could all be impacted by the connection is closed error and fail continuously. If you're not actively monitoring the logs you may not notice this is occurring. And the only way to properly resolve it is restarting the JVMs if you don't have the patch.
2) I haven't utilized this feature specifically but you should be OK with the patch. I still would suggest enabling the Maximo feature in that technote to kill long running connections on the database.
You are mostly correct except the check doesn't technically occur prior to utilizing the connection. What happens is something that causes Maximo to execute a database query (opening a new MboSet for example) will be attempted and fail. We then check the error code and identify it's a closed connection error and then attempt to re-establish a new connection and try it again. When Maximo closes the connection we remove it from the pool and don't even attempt that first query.
3) Any APAR can have a hotfix requested but it may not be approved. I've never been involved in the approval conversations but being on an older IFIX of 7.6.1.1 might be a problem. We typically try to encourage customers to apply an IFIX whenever possible and don't want them to get too far behind.
4) This would be the safer option without the patch. Follow the guide, including enabling system property mxe.db.closelongrunconn, because it's disabled by default. Because Maximo is the one closing the connections, it will remove them from it's internal pool and avoid the issue I discussed above. I would try this first if you aren't ready to patch and see if this helps alleviate your issue.
------------------------------
Steven Shull
------------------------------
Original Message:
Sent: Sat March 05, 2022 09:53 AM
From: Pankaj Bhide
Subject: MAXIMO Connection manager
Hello Steve, I thank you for your advice and suggestions. I always appreciate your untiring and unselfish efforts in helping us.
Just to confirm my understanding on this:
1) We are currently using MAXIMO 7.6.1.1-IFIX20200124-1352 Build 20190514-1348 DB Build V7611-365 HFDB Build HF7611-04. Based upon your experience, we should NOT attempt to utilize Oracle's MAX_IDLE_TIME feature to kill the unused connections. Are you aware of any specific issues if we utilize that feature other than some error/warning messages in the log files suggesting that the connection pool attempted utilizing the connection which is now killed, however the connection manager later grabbed a new connection to fill the gap. Does this affect any application performance (e.g. users getting annoying error messages or cron tasks fail etc.) ?
2) If we were using MAXIMO 7.6.1.2 with the suggested fix pack, then, it is OKAY to utilize Oracle's MAX_IDLE_TIME feature as that fix pack checks whether the connection is active or killed before it is utilized by the connection manager.
3) Can we request IBM to provide us with the "hot fix" that can possibly include the above fix pack that we can utilize in 7.6.1.1? So that, we don't have to upgrade to 7.6.1.2 and start utilizing MAX_IDLE_TIME feature in 7.6.1.1 with the hot fix?
The reason for utilizing the MAX_IDLE_TIME feature is - we can simply delegate the task of killing the db connections (that might not have been created by MAXIMO connection pool) if they are not being used. This way we don't need to bounce the db instance every night.
4) As you suggested, can we anyway start utilizing "https://www.ibm.com/support/pages/maximo-and-control-desk-database-connection-leak-resolution" with the default "mxe.db.longruntimelimit - with default 180" minutes.? At-least this way, the connection manager will try to put the long running unused connections (previously created by the connection manager) in the pool? Does this have any performance impact that you may be aware of?
------------------------------
Pankaj Bhide
Computer Systems Engineer
Berkeley National Laboratory
Berkeley CA
Original Message:
Sent: Wed March 02, 2022 09:41 PM
From: Steven Shull
Subject: MAXIMO Connection manager
I wouldn't go that approach, but if you're on 7.6.1.2, you should be OK for connections being killed on the database server side. There was an issue I reported where Maximo would re-use closed database but that was fixed in 7.6.1.2 for SQL Server & Oracle (APAR IJ25026) and the most recent IFIX (IFIX 18) for 7.6.1.2 for DB2.
The approach I would take is outlined here: https://www.ibm.com/support/pages/maximo-and-control-desk-database-connection-leak-resolution
If Maximo is the initiator of the connections, let it be the one to close them. Maximo maintains references to the connections and if it thinks it's still open it will try to utilize them. The fix IBM made handles this cleanly now but it's still going to slow down the process (connection has to fail and then Maximo has to establish a new connection).
That document also covers how to debug connections but be warned, this is VERY verbose if you enable the dbconnection logger to INFO. I would try to do this in a non-production environment if you're seeing the leaks there. It also only helps if you do it in advance. This can try and help pinpoint connection leaks related to those connections (such as utilizing isEmpty() on a set without closing it or iterating through the set).
------------------------------
Steven Shull
Original Message:
Sent: Wed March 02, 2022 07:09 PM
From: Pankaj Bhide
Subject: MAXIMO Connection manager
Also does MAXIMO provides any user interface (official or unofficial) that shows the details of connection manager? For example which database connections are created initially, which are used currently etc.
------------------------------
Pankaj Bhide
Computer Systems Engineer
Berkeley National Laboratory
Berkeley CA
Original Message:
Sent: Wed March 02, 2022 07:00 PM
From: Pankaj Bhide
Subject: MAXIMO Connection manager
Hello Steve,
We are working with the DataSplice support to get more information from them. In our setup we already have one dedicated database account titled "mobileadm" that is used for the DataSplice to query the data from MAXIMO database. It shows only 1 connection. However, as you said they utilize proprietary plugin that uses JAVA RMI to post the data to MAXIMO (this is a blackbox to us).
In the shorter run the problem we need to solve is how to kill the unused database connections over the period of time. The v$session view shows cumulative database connections that grow over the period of time. We implemented a very short term solution - bounce the db instance every night. But it not a good solution.
We found Oracle provides a setting ""MAX_IDLE_TIME". We are thinking of setting its value to an appropriate number so that the unused oracle connections would be garbage collected. However, we need to understand, analyze and evaluate its impact to MAXIMO's connection manager. For example at one point of time the MAXIMO connection manager has grabbed 10 db connections in the pool (e.g. conn1, conn2, conn3 ... conn10). After some time, Oracle decides killing (due to the MAX_IDLE_TIME setting) a few connections (e.g. conn2, conn3) since they were idle for while. We want to know whether MAXIMO connection manager is intelligent enough to understand that conn2 and conn3 are killed and it will grab 2 new connections.
So in short we want to reverse engineer how the MAXIMO connection manager works. Also the document "https://www.ibm.com/support/pages/explanation-maximo-application-server-connection-pooling-and-management" states the connection manager will use the system properties to create new connections etc. However we found that the connection manager is not using the system properties values, rather it is using the exact same values given in that tech note.
Any insights?
------------------------------
Pankaj Bhide
Computer Systems Engineer
Berkeley National Laboratory
Berkeley CA
Original Message:
Sent: Mon February 28, 2022 07:45 AM
From: Steven Shull
Subject: MAXIMO Connection manager
All of the settings inside Maximo only impact Maximo established connections. So any third party connections (mobility, reporting, etc.) won't be managed by those settings.
DataSplice and some of the other mobile products typically utilize a mixture of SQL queries for retrieving data and RMI for submitting data. Since this appears to correlate to an upgrade of that client, I would probably start there.
I believe DataSplice has its own connection pool concept but I don't know what the default settings are to describe how many connections would be opened. Do you have a dedicated account for DataSplice or are you re-using the same account that you utilize for Maximo? If not utilizing a dedicated account, I would try and switch DataSplice to a dedicated account to isolate Maximo database connections vs DataSplice database connections. Based on my previous experience, you should only need a read-only SQL account for DataSplice.
You asked about the application connection pool in WebSphere and no, Maximo does not utilize WebSphere connection pools. Some customers may utilize it for JMS configuration if they're using a database to store their JMS messages.
------------------------------
Steven Shull
Original Message:
Sent: Sat February 26, 2022 08:41 AM
From: Pankaj Bhide
Subject: MAXIMO Connection manager
Hello,
We recently upgraded our mobile app (DataSplice) to ver 6. Concidently?, we started experiencing exponential growth in the database connections on our database server (visible from Oracle's v$session view) and we are investigating the cause of this growth.
I was reading the article "https://www.ibm.com/support/pages/explanation-maximo-application-server-connection-pooling-and-management" published by IBM. In our database, I found following values for these properties:
mxe.db.initialConnections=8
mxe.db.minFreeConnections=5
mxe.db.maxFreeConnections=8
mxe.db.newConnectionCount=3
The above properties are out of box and we haven't changed them. However when we start the db instance (non-productions) we see minimum 15 connections owned by "Maximo" user (out schema owner). We don't have any cron tasks or scheduled automation scripts that open the db connections directly. Also no user is connected to the app after startup.
Why it opens extra 7 connections at the db instance startup? Do these values are stored somewhere else? I checked into the "maximo.properties" file and I did not find them.
I also found another article "https://www.ibm.com/docs/en/was-nd/8.5.5?topic=applications-connection-pool-settings" that explains the connection pooling by the webSphere. Does MAXIMO use these values?
Any insight on this?
Pankaj Bhide
------------------------------
Pankaj Bhide
Computer Systems Engineer
Berkeley National Laboratory
Berkeley CA
------------------------------
#AssetandFacilitiesManagement
#Maximo