Db2

 View Only
  • 1.  DMC event monitor locks

    IBM Champion
    Posted Tue January 14, 2020 04:39 AM
    Dear,

    I'm a bit struggling with locks caused by the event monitors started by DMC. I see locks like:



    The statement that is waiting is :

    /* IBM_DSSNAP */ drop table IBM_RTMON.ACTIVITY

    and the lock holders are the db2 event monitor fast writer threads. 

    Our system is fairly busy since there are rows added to the table constantly :

    $ date ; db2 "select count(*) from IBM_RTMON.ACTIVITY with ur"
    Tue Jan 14 10:20:35 CET 2020

    1
    -----------
    11697695

    1 record(s) selected.

    $ date ; db2 "select count(*) from IBM_RTMON.ACTIVITY with ur"
    Tue Jan 14 10:20:39 CET 2020

    1
    -----------
    11697823

    1 record(s) selected.


    With the older version (dsm), these tables were regularly load replaced which caused loss of monitor data in the repository database. 

    I think this process of collecting and sending data to the DCM repo database should be reviewed. Data should be collected to files or to queues and then send to the other system so there is no disruption in collecting. (Writing to files instead would also have a positive impact on the database logging as there wouldn't be inserts in the event monitor tables)  Or maybe there can be a switch mechanism implemented so the event monitors are inserting into different tables. 

    I had to stop the locking event monitor, the utility event monitor and the statistics event monitor since when these were turned on, I got too many business transactions in lock wait. 


    Since I cannot turn on the statistics event monitor, I can't get the database responsiveness which is unfortunate 

    Are there maybe suggestions on how to configure the fast writers or avoid the drop table so the lock waits get resolved? 

    Many thanks in advance,
    Luc

    ------------------------------
    Luc Vandaele
    ------------------------------

    #Db2


  • 2.  RE: DMC event monitor locks

    Posted Thu January 16, 2020 09:41 AM
    Edited by System Fri January 20, 2023 04:34 PM
    @Luc Vandaele​ Thanks for the comments.    We understand event monitor also supports flush data to file/pipeline but it may have other limitations to adopt. For example,  if we want to move data from file to repo, we may need ssh to the monitor db system to get it which is not allowed on a lot of customer env., especially for production systems.  We totally understand the lock contention on event monitor and we are keeping exploring solutions to solve it.   We will update you once we made any progress on it and thanks for your continuous support and good suggestions!

    ------------------------------
    HAO WU
    ------------------------------



  • 3.  RE: DMC event monitor locks

    Posted Fri January 06, 2023 10:23 AM
    Hi All,

    There is a bit of a nasty side effect of DMC dropping tables - it fills up the Recovery History file (RHF) with some 2,500 dropped tables a day... RHF becomes slow and eventually rolls over loosing the backup history etc..  In DMC we have just the responsiveness stats enabled!  (Why delete and not truncate?)

    Regards Robert

    ------------------------------
    Robert Clow
    ------------------------------



  • 4.  RE: DMC event monitor locks

    Posted Mon January 09, 2023 09:28 AM
    Hello Robert,
    You might want to ALTER the tablespaces where these dropped tables reside with the option DROPPED TABLE RECOVERY OFF.
    Regards
    --
    Yves-Antoine Emmanuelli

    ------------------------------
    Yves-Antoine Emmanuelli
    ------------------------------



  • 5.  RE: DMC event monitor locks

    Posted Tue January 10, 2023 08:19 AM
    Yes. please try @Yves-Antoine Emmanuelli's suggest to alter tablespace with the option DROPPED TABLE RECOVERY OFF as a workaround.

    In DMC we use event monitor tables as a temporal table to collect data every 5 mins.   In every 5mins,  we copy data from event monitor tables to a historic table in repo db and then drop/create the event monitor for next batch of collection which may result in a drop table action.   The reason we use drop table is there is a chance that delete/truncate may fail then it will overwhlem the event monitor tablespace which may cause system hang, especially when the system is busy.  We understand the drop table in every 5min might not be beautiful which may bring some side effect. The purpose is to make sure the data in temporal table can be cleaned successfully in every 5mins.  We are exploring some new Db2 ways such as ITC table to improve this.  ​

    ------------------------------
    HAO WU
    ------------------------------