Db2

 View Only
  • 1.  DMC 3.1.5 leaving connections behind on repo db

    Posted Mon March 07, 2022 03:08 PM

    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


  • 2.  RE: DMC 3.1.5 leaving connections behind on repo db

    Posted Wed March 09, 2022 10:36 PM
    Edited by System Fri January 20, 2023 04:21 PM
    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
    ------------------------------



  • 3.  RE: DMC 3.1.5 leaving connections behind on repo db

    Posted Thu March 10, 2022 07:53 AM
    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
    ------------------------------



  • 4.  RE: DMC 3.1.5 leaving connections behind on repo db

    Posted Sat March 12, 2022 08:18 AM
    ​Hi Samuel,
    Could you try to upgrade to DMC to version v3.1.6 or later, we fixed a similar issue about the repository connection pool in the release of v3.1.6 

    Thank you


    ------------------------------
    Tao Wen
    ------------------------------



  • 5.  RE: DMC 3.1.5 leaving connections behind on repo db

    Posted Mon March 14, 2022 08:15 AM
    Hi @Tao Wen

    I've updated DMC to the latest release during the weekend.  Will keep eye in the connections for the next days.  ​I have connections created for yesterday 13th, and today 14th,   but so far their idle times are not growing,  which means they are beind used..  

    Will keep eye on it for the next days,  and report back..  Thanks

    ------------------------------
    Samuel Pizarro
    Db2 DBA
    Kyndryl
    Brazil / Sumaré - SP
    ------------------------------



  • 6.  RE: DMC 3.1.5 leaving connections behind on repo db

    Posted Thu March 17, 2022 03:05 AM
    Hi Samuel,

    Thank you so much.

    ------------------------------
    Tao Wen
    ------------------------------



  • 7.  RE: DMC 3.1.5 leaving connections behind on repo db

    Posted Thu March 17, 2022 01:25 PM
    Hi @Tao Wen

    Issue got fixed in latest release.   The connections to repo are not increasing anymore.

    Thanks ​

    ------------------------------
    Samuel Pizarro
    Db2 DBA
    Kyndryl
    Brazil / Sumaré - SP
    ------------------------------