Db2 On Premise and Cloud

  • 1.  How to reduce the repository database for DMC

    Posted Tue September 28, 2021 11:30 AM
    Hi,

    How can I reduce the size of the repository database for DMC?
    The table IBMCONSOLE.EVENT_ACTIVITY is to large. I changed the parameters in "Monitor settings -> Persistence" to 1 week for "Monitoring Data" and 7 days for "Package cache". But the database remains the same size.

    ------------------------------
    Regards, Dennis
    ------------------------------


  • 2.  RE: How to reduce the repository database for DMC

    Posted Wed September 29, 2021 10:45 AM
    Hi Dennis, 

    If data gets deleted from a table, the free space is not automatically returned to the disk. You have to do a REORG for the table and a ALTER TABLESPACE. Here you can find the required steps to reclaim available space to the disk. 

    https://www.ibm.com/support/pages/steps-reclaim-all-available-space-reclaimable-storage-dms-automatic-storage-tablespace

    ------------------------------
    Herbert Wirkner
    ------------------------------



  • 3.  RE: How to reduce the repository database for DMC

    Posted Wed September 29, 2021 02:21 PM
    Hi Herbert,

    Thanks for the answer.
    I have done these steps. Without noticeable changes.

    In IBMCONSOLE.EVENT_ACTIVITY is collected data since 08/09/2021. More then 7 days.
    It seems to me that the cleaning process is not working.

    ------------------------------
    Regards, Dennis
    ------------------------------



  • 4.  RE: How to reduce the repository database for DMC

    Posted Wed September 29, 2021 03:12 PM
    Hello Dennis,
    Please make sure that you are using DMC V3.1.5.1, as we did have some improvements in this area.
    One of the issues is that in the previous DMC releases, there was a query to delete the old records which was returning SQL -952 error, and the old data was not being deleted.
    If you already have V3.1.5.1, please create a case with us, and attach the DMC collector to speed up the analysis process.
    Here are the instructions:
    https://www.ibm.com/docs/en/db2-data-mgr-console/3.1.x?topic=support-exchanging-information


    ------------------------------
    Cintia Ogura
    ------------------------------



  • 5.  RE: How to reduce the repository database for DMC

    Posted Wed September 29, 2021 05:42 PM
    Hello Cintia,

    We already have V3.1.5.1. I created a case.

    ------------------------------
    Regards, Dennis
    ------------------------------



  • 6.  RE: How to reduce the repository database for DMC

    Posted Wed September 29, 2021 05:25 PM
    Hi Dennis,

    In DMC 3.1.5.1, we are in transition to migrate the EVENT_ACTIVITY table to using MDC format.  This will significantly reduce the storage footprint on EVENT_ACTIVITY table.  Together with the recommendation from Herbert, you should see database storage footprint reduced as a result.

    See more on the migration steps:
    https://www.ibm.com/docs/en/db2-data-mgr-console/3.1.x?topic=console-migrating-multidimensional-clustered-mdc-tables

    Let me reach out to you in private message and check if your DMC repository is beneficial in using MDC format for EVENT_ACTIVITY.  

    Thanks.

    ------------------------------
    Jason Sizto
    ------------------------------



  • 7.  RE: How to reduce the repository database for DMC

    IBM Select
    Posted Thu September 30, 2021 03:32 AM
    Hi Jason,
    I had the same issue. The older Docker image (migration failed to finish properly due to some additional access rights were needed during the migration and the customer wanted to have only minimum access rights deployed) was still collecting data and storing the data in DB2DMC.EVENT_ACTIVITY table.

    The final size after 2 1/2 month was 240 GB. So I was also deleting manually from the table on a day by day basis using intermitting commits to keep the number of logfiles low. Similar to this:
    BEGIN DECLARE count INTEGER; SET count = 0; FOR row AS c CURSOR WITH HOLD FOR SELECT COLLECTED FROM DB2DMC.EVENT_ACTIVITY WHERE COLLECTED < CAST ('2021-05-01-00.00.00' AS TIMESTAMP) FOR UPDATE DO SET count = count + 1; DELETE FROM DB2DMC.EVENT_ACTIVITY WHERE CURRENT OF c; IF (MOD (count, 100000) = 0) THEN COMMIT; END IF; END FOR; COMMIT; END@
    But in the end, I decided to drop the database entirely, as managing this with 240 GB takes a while, plus the REORG and ALTER TABLESPACE.
    With this step I also lost my complex LDAP setup, but it saved a lot of time and I/O cycles for the system.

    While deleting, I was wondering, if there are other tables in the DMC database, which might refer to this table and if there is RI between them to support a cascaded delete. Or if such a procedure would require to cleanup other tables as well.
    So, what's the lab's answer to this?

    Cheers
    Roland

    ------------------------------
    Roland Schock
    Distinguished Engineer
    ARS Computer und Consulting GmbH
    Muenchen
    ------------------------------



  • 8.  RE: How to reduce the repository database for DMC

    Posted Thu September 30, 2021 06:15 AM

    Hi Team,

    I have got the update from PMR Team  . This issue  has been corrected in latest release Db2 Console v3.1.5.1 for better handling space issue.  
    We are planning to upgrade the same not checked yet..

    Kind Regards

    Prashant Shivmath
    Mobile : +91-9892691478
    Email : prashant.shivmath@in.ibm.com


    "IBM Community" ---30-09-2021 13:02:26---Hi Jason, I had the same issue. The older Docker image (migration failed to finish properly due to s






  • 9.  RE: How to reduce the repository database for DMC

    Posted Wed October 20, 2021 03:11 PM
    Hi,

    The configuration for our database was not set appropiately.
    logfilsiz, logprimary and logsecond
    Capacity planning - IBM Documentation

    Thanks for your help.

    ------------------------------
    Regards, Dennis
    ------------------------------