I understand the concept of process pooling, and keep some connections active.. That's not the problem.
My point is, that this logic is not working properly.. Right now, it's quite the opposite of what you are describing.. DMC is not re-using the pre-opened connections properlty, but instead it's oppening new connections every day, and leaving the previous opened connections in a idle state.. Not using them , never again !
At some point, your 140 connections will get exuasted, and DMC stops working.. This happened 3 times already in my system, and I am forced to stop/start DMC when this happens.
My system has only 1 Db being monitored, and only one user (my self) using DMC console to montior it.. So, there isn't too much workload happening on repo db that justifies this behavior.
db2 "select substr(APPLICATION_NAME, 1, 15) appl_name, connection_start_time, uow_start_time, timestampdiff(4 , (current_timestamp ) - timestamp(uow_start_time) ) idle_minutes_since_last_activity from TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t order by 2, 3"
APPL_NAME CONNECTION_START_TIME UOW_START_TIME IDLE_MINUTES_SINCE_LAST_ACTIVITY
--------------- -------------------------- -------------------------- --------------------------------
UC_REPO_dba 2022-02-18-09.36.49.548056 2022-03-10-07.40.35.435212 0
UC_REPO_dba 2022-02-18-09.36.49.666811 2022-02-18-09.36.49.668520 28684
UC_REPO_mon 2022-02-18-09.37.03.034258 2022-02-18-09.41.58.223599 28678
UC_REPO_mon 2022-02-18-09.37.03.054720 2022-02-18-09.41.58.240651 28678
UC_REPO_run 2022-02-18-09.41.47.601963 2022-03-10-07.40.46.985173 0
UC_REPO_mon 2022-02-19-07.46.57.970596 2022-02-19-09.41.58.223516 27238
UC_REPO_mon 2022-02-19-09.41.57.999502 2022-02-19-09.41.58.220680 27238
UC_REPO_mon 2022-02-20-03.16.58.009628 2022-03-03-13.57.17.641088 9703
UC_REPO_mon 2022-02-20-09.41.57.953639 2022-02-20-09.41.58.224233 25798
UC_REPO_mon 2022-02-20-09.41.57.980273 2022-02-20-09.41.58.220687 25798
UC_REPO_mon 2022-02-21-06.36.58.006421 2022-02-21-09.41.58.223950 24358
UC_REPO_mon 2022-02-21-09.41.57.983710 2022-02-21-09.41.58.220830 24358
UC_REPO_mon 2022-02-22-05.46.57.958891 2022-02-22-09.41.58.221163 22918
UC_REPO_mon 2022-02-22-09.36.57.959418 2022-02-22-09.41.58.223598 22918
UC_REPO_mon 2022-02-23-06.36.57.974493 2022-02-23-09.41.58.224203 21478
UC_REPO_mon 2022-02-23-09.36.57.966298 2022-02-23-09.41.58.221362 21478
UC_REPO_mon 2022-02-24-03.41.58.023216 2022-02-24-09.41.58.223815 20038
UC_REPO_mon 2022-02-24-07.36.57.952447 2022-02-24-09.41.58.221358 20038
UC_REPO_mon 2022-02-25-04.36.57.967781 2022-02-25-09.41.58.223427 18598
UC_REPO_mon 2022-02-25-09.36.57.951589 2022-02-25-09.41.58.220453 18598
UC_REPO_mon 2022-02-26-07.36.58.022727 2022-02-26-09.41.58.223983 17158
UC_REPO_mon 2022-02-26-08.26.57.967553 2022-02-26-09.41.58.220365 17158
UC_REPO_mon 2022-02-27-05.51.57.956237 2022-02-27-09.41.58.223562 15718
UC_REPO_mon 2022-02-27-09.41.57.976330 2022-02-27-09.41.58.221434 15718
UC_REPO_mon 2022-02-28-09.06.57.981990 2022-02-28-09.41.58.224170 14278
UC_REPO_mon 2022-02-28-09.41.58.015308 2022-02-28-09.41.58.220365 14278
UC_REPO_mon 2022-03-01-07.36.57.950575 2022-03-01-09.41.58.223803 12838
UC_REPO_mon 2022-03-01-09.41.57.979182 2022-03-01-09.41.58.221354 12838
UC_REPO_mon 2022-03-02-08.26.57.927155 2022-03-02-09.41.58.221386 11398
UC_REPO_mon 2022-03-02-09.06.57.930853 2022-03-02-09.41.58.223353 11398
UC_REPO_mon 2022-03-03-08.06.57.985980 2022-03-03-09.41.58.223995 9958
UC_REPO_mon 2022-03-03-09.41.58.006189 2022-03-03-09.41.58.221257 9958
UC_REPO_mon 2022-03-04-07.36.58.016566 2022-03-04-09.41.58.223614 8518
UC_REPO_mon 2022-03-04-09.41.57.979112 2022-03-04-09.41.58.221222 8518
UC_REPO_mon 2022-03-05-09.41.58.008174 2022-03-05-09.41.58.224116 7078
UC_REPO_mon 2022-03-05-09.41.58.018930 2022-03-05-09.41.58.221195 7078
UC_REPO_mon 2022-03-06-03.41.57.935858 2022-03-06-09.41.58.223667 5638
UC_REPO_mon 2022-03-06-09.41.58.000653 2022-03-06-09.41.58.221191 5638
UC_REPO_mon 2022-03-07-06.16.58.009788 2022-03-07-09.41.58.223255 4198
UC_REPO_mon 2022-03-07-09.41.57.994516 2022-03-07-09.41.58.221154 4198
UC_REPO_mon 2022-03-08-09.36.57.966483 2022-03-08-09.41.58.223754 2758
UC_REPO_mon 2022-03-08-09.41.58.016662 2022-03-08-09.41.58.221182 2758
UC_REPO_mon 2022-03-09-07.36.57.951317 2022-03-09-09.41.58.221115 1318
UC_REPO_mon 2022-03-09-09.41.58.015146 2022-03-09-09.41.58.223311 1318
UC_REPO_mon 2022-03-10-02.11.57.982473 2022-03-10-07.39.18.278411 1
UC_REPO_mon 2022-03-10-02.36.57.953403 2022-03-10-07.39.22.408229 1
UC_REPO_mon 2022-03-10-04.01.57.983118 2022-03-10-07.40.50.379432 0
UC_REPO_mon 2022-03-10-04.41.57.996914 2022-03-10-07.39.18.282966 1
UC_REPO_mon 2022-03-10-05.06.57.963819 2022-03-10-07.31.57.920675 8
UC_REPO_mon 2022-03-10-05.11.57.955157 2022-03-10-07.36.57.935952 3
UC_REPO_mon 2022-03-10-05.36.57.981143 2022-03-10-07.31.57.922848 8
UC_REPO_mon 2022-03-10-06.36.57.960833 2022-03-10-07.40.50.084282 0
UC_REPO_mon 2022-03-10-06.36.57.966227 2022-03-10-07.36.57.935513 3
UC_REPO_mon 2022-03-10-06.41.57.985900 2022-03-10-07.39.17.093617 1
db2bp 2022-03-10-07.18.55.738255 2022-03-10-07.40.53.260028 0
55 record(s) selected.
This keeps growing, and grwoing every single day, but the previous opened connections are never re-used again. which DEFEATS the purpose of the connection pool.
Regards
------------------------------
Samuel Pizarro
Db2 DBA
Kyndryl
Brazil / Sumaré - SP
------------------------------
Original Message:
Sent: Wed March 09, 2022 10:35 PM
From: Tao Wen
Subject: DMC 3.1.5 leaving connections behind on repo db
Hello Samuel,
Cause the monitor service interacts with the repository database very frequently. Every 5 minutes (by default), the monitor retrieves the monitor data from the target databases, and store the data in the repository, the storing process may be slow, depending on the repository's performance and the data size. if we monitor multiple databases simultaneously, it will cost more time.
It spends extra time when creating connections to the database. if we create the connections frequently, it will bring performance issues for the monitor and the other services. For guaranteeing the performance and storing data correctly, we apply a connection pool to the repository. it will open at most 140 connections (the setting of v3.1.5, this number changes to 240 in v3.1.6)to the repository database.
Thank you
------------------------------
Tao Wen
Original Message:
Sent: Mon March 07, 2022 03:08 PM
From: Samuel Pizarro
Subject: DMC 3.1.5 leaving connections behind on repo db
dmc running on linux, and it's leaving mon connection behind on repository db.
every single day, new connections are created to the repo db, but old connecation are not terminated.. and they are not being reused as well.
APPLICATION_HANDLE APPL_NAME CONNECTION_START_TIME UOW_START_TIME-------------------- -------------------- -------------------------- -------------------------- 40 UC_REPO_dba 2022-02-18-09.36.49.548056 2022-03-07-14.53.21.055552 41 UC_REPO_dba 2022-02-18-09.36.49.666811 2022-02-18-09.36.49.668520 46 UC_REPO_mon 2022-02-18-09.37.03.034258 2022-02-18-09.41.58.223599 47 UC_REPO_mon 2022-02-18-09.37.03.054720 2022-02-18-09.41.58.240651 81 UC_REPO_run 2022-02-18-09.41.47.601963 2022-03-07-14.52.46.985563 9500 UC_REPO_mon 2022-02-19-07.46.57.970596 2022-02-19-09.41.58.223516 9767 UC_REPO_mon 2022-02-19-09.41.57.999502 2022-02-19-09.41.58.220680 12482 UC_REPO_mon 2022-02-20-03.16.58.009628 2022-03-03-13.57.17.641088 13515 UC_REPO_mon 2022-02-20-09.41.57.953639 2022-02-20-09.41.58.224233 13520 UC_REPO_mon 2022-02-20-09.41.57.980273 2022-02-20-09.41.58.220687 16882 UC_REPO_mon 2022-02-21-06.36.58.006421 2022-02-21-09.41.58.223950 17357 UC_REPO_mon 2022-02-21-09.41.57.983710 2022-02-21-09.41.58.220830 20569 UC_REPO_mon 2022-02-22-05.46.57.958891 2022-02-22-09.41.58.221163 21194 UC_REPO_mon 2022-02-22-09.36.57.959418 2022-02-22-09.41.58.223598 24631 UC_REPO_mon 2022-02-23-06.36.57.974493 2022-02-23-09.41.58.224203 25105 UC_REPO_mon 2022-02-23-09.36.57.966298 2022-02-23-09.41.58.221362 28049 UC_REPO_mon 2022-02-24-03.41.58.023216 2022-02-24-09.41.58.223815 28643 UC_REPO_mon 2022-02-24-07.36.57.952447 2022-02-24-09.41.58.221358 31999 UC_REPO_mon 2022-02-25-04.36.57.967781 2022-02-25-09.41.58.223427 32819 UC_REPO_mon 2022-02-25-09.36.57.951589 2022-02-25-09.41.58.220453 36311 UC_REPO_mon 2022-02-26-07.36.58.022727 2022-02-26-09.41.58.223983 36455 UC_REPO_mon 2022-02-26-08.26.57.967553 2022-02-26-09.41.58.220365 39921 UC_REPO_mon 2022-02-27-05.51.57.956237 2022-02-27-09.41.58.223562 40552 UC_REPO_mon 2022-02-27-09.41.57.976330 2022-02-27-09.41.58.221434 44520 UC_REPO_mon 2022-02-28-09.06.57.981990 2022-02-28-09.41.58.224170 44623 UC_REPO_mon 2022-02-28-09.41.58.015308 2022-02-28-09.41.58.220365 48198 UC_REPO_mon 2022-03-01-07.36.57.950575 2022-03-01-09.41.58.223803 48533 UC_REPO_mon 2022-03-01-09.41.57.979182 2022-03-01-09.41.58.221354 52277 UC_REPO_mon 2022-03-02-08.26.57.927155 2022-03-02-09.41.58.221386 52389 UC_REPO_mon 2022-03-02-09.06.57.930853 2022-03-02-09.41.58.223353 56139 UC_REPO_mon 2022-03-03-08.06.57.985980 2022-03-03-09.41.58.223995 56393 UC_REPO_mon 2022-03-03-09.41.58.006189 2022-03-03-09.41.58.221257 60048 UC_REPO_mon 2022-03-04-07.36.58.016566 2022-03-04-09.41.58.223614 60372 UC_REPO_mon 2022-03-04-09.41.57.979112 2022-03-04-09.41.58.221222 64345 UC_REPO_mon 2022-03-05-09.41.58.008174 2022-03-05-09.41.58.224116 64347 UC_REPO_mon 2022-03-05-09.41.58.018930 2022-03-05-09.41.58.221195 2244 UC_REPO_mon 2022-03-06-03.41.57.935858 2022-03-06-09.41.58.223667 3228 UC_REPO_mon 2022-03-06-09.41.58.000653 2022-03-06-09.41.58.221191 6497 UC_REPO_mon 2022-03-07-06.16.58.009788 2022-03-07-09.41.58.223255 7023 UC_REPO_mon 2022-03-07-09.41.57.994516 2022-03-07-09.41.58.221154 7338 UC_REPO_mon 2022-03-07-11.31.57.977599 2022-03-07-14.51.57.928497 7493 UC_REPO_mon 2022-03-07-12.36.57.958204 2022-03-07-14.52.33.858303 7494 UC_REPO_mon 2022-03-07-12.36.57.962771 2022-03-07-14.51.57.919385 7658 UC_REPO_mon 2022-03-07-13.36.57.951839 2022-03-07-14.52.30.497886 7671 UC_REPO_mon 2022-03-07-13.36.58.026437 2022-03-07-14.51.57.930741 7717 UC_REPO_mon 2022-03-07-13.51.57.974363 2022-03-07-14.51.57.930130 7786 UC_REPO_mon 2022-03-07-14.16.57.958323 2022-03-07-14.52.30.496546 7819 UC_REPO_mon 2022-03-07-14.26.58.012879 2022-03-07-14.51.57.930849 7839 UC_REPO_mon 2022-03-07-14.36.57.957600 2022-03-07-14.51.57.931294 7840 UC_REPO_mon 2022-03-07-14.36.57.964201 2022-03-07-14.52.33.858801 7893 db2bp 2022-03-07-14.53.38.533374 2022-03-07-14.53.40.783024 51 record(s) selected.
Regards
------------------------------
Samuel Pizarro
Db2 DBA
Kyndryl
Brazil / Sumaré - SP
------------------------------
#Db2