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
------------------------------
Original Message:
Sent: Wed September 29, 2021 05:25 PM
From: Jason Sizto
Subject: How to reduce the repository database for DMC
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
Original Message:
Sent: Wed September 29, 2021 02:21 PM
From: Dennis Barg
Subject: How to reduce the repository database for DMC
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
Original Message:
Sent: Wed September 29, 2021 02:13 AM
From: Herbert Wirkner
Subject: How to reduce the repository database for DMC
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
Original Message:
Sent: Tue September 28, 2021 11:29 AM
From: Dennis Barg
Subject: How to reduce the repository database for DMC
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
------------------------------
#Db2