Hi Deepa,
it sounds like the system has a number of connection leaks. I have seen this situation before.
If the system is on Maximo 7.5.0.3 then there should be a new logger called :
dbconnection
switch that to INFO and then use the JVM as normal. It should start logging details about any database connections that have been leaked.
I discuss this logger in this blog article:
https://www.linkedin.com/pulse/db-connections-mbosets-understanding-which-piece-code-mark-robbins/
If you are on later versions of 7.6 then the full list of leaked connections is logged when the JVM is shutdown.
they will be logged to the Systemout.log file.
you would need to submit those logs to IBM Support or get help from people who are experienced in dealing with them, like me.
The best solution is to get the system patched to the latest Maximo fix pack. I, and others, have been reporting a lot of the DB connection leaks and IBM have been patching them so going to the latest fix pack will help.
you said this:
"But why do I see login_name as the database username( that is defined in mxe.db.user) and not individual userid name?" - this is because Maximo creates a pool of DB connections using the user defined in there. Any single connection in the pool can be used by multiple users over time.
Mark F mentioned users not closing their sessions properly - that creates a different problem that i discuss in this article - https://www.linkedin.com/pulse/maximo-users-login-counts-accidentally-overloading-jvm-mark-robbins/
That is unlikely to be directly causing the sleeping database sessions that you refer to.
------------------------------
mark robbins
Cohesive
IBM Champion 2017-2023 Inclusive
See my blog on Maximo support related topics here:
https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/------------------------------
Original Message:
Sent: 06-15-2023 05:54
From: Deepa P
Subject: Many sleeping threads in sys.dm_exec_sessions
Hi Everyone,
From past 2 days, database team has informed that there is huge cpu utilisation for maximo db. They have informed that many sleeping sessions they found for our maximo prod database which might be causing the issue.
I executed the below query and found the same:
Select* from sys.dm_exec_sessions where status like '%sleeping%' and open_transaction_count=1 and is_user_process=1 and login_name like '...'
Mostly the records shows from maximo prod hostname and login_user is mxe.db.user.
The db team wants us( application team) to find out why there are so many sleeping sessions from maximo db. Can anyone suggest what things should I check to find out what is actually causing these sleeping sessions?
I feel like sessions are not getting closed after use.
Please suggest
#Administration
#Analytics
#Architecture
#Assets
#CivilInfrastructure
#Customizations
#EndUser
#EverythingMaximo
#HSE/OilandGas
#Infrastructure
#Integrations
#Inventory
#IoT
#LifeScience/Calibration
#Linear
#MaximoApplicationSuite
#MaximoForAviation
#MaximoUserGroups
#Mobility
#Nuclear
#Procurement
#Reporting
#Scheduling
#Security
#ServiceProvider
#Spatial
#Transportation
#Utilities
#WorkCenters
#WorkManagement
------------------------------
Deepa P
------------------------------