Reporter Database never been purged what is the best approach to reduce its size

Products

• CA Automic Dollar Universe

Issue/Introduction

Reporter Server was installed ages ago and the Dollar Universe data purge had never been performed or scheduled to run on a daily/weekly/monthly basis.

As a result, the Database size increased to 400GB resulting in a disk space issue on the Server hosting the Database.

What would be the best strategy to reduce the size to a reasonable amount (50GB or less) without losing all the data?

Environment

Release : 6.x

Component: Reporter Server

Cause

Recurrent Purge to keep only X monts of data from the Dollar Universe Nodes had never been set.

Resolution

Since the purge had never been done before, a regular purge would take several weeks to complete and require at least twice of the current size of the Reporter Database to be able to run succesfully.

In such case, it is better to adopt a different strategy for the Purge that requires the help of a DBA.

This would be the fastest approach for purging the most important table UNI_EVT_RELAUNCHES by a DBA:

1. Decide how many months/days you want to keep and as such, you will use this value to calculate the value for the STARTING_EVT_TIME parameter (ie. if it's June 2023 and you want to keep just 6 months, you could just set "2023-01-01 00:00:00" as value.
2. Stop the Reporter Extractions
3. Create a table named UNI_EVT_RELAUNCHES2 with a select from UNI_EVT_RELAUNCHES having STARTING_EVT_TIME>=(January_2023)
4. Perform a truncate of the table UNI_EVT_RELAUNCHES
5. Rename the table UNI_EVT_RELAUNCHES2 into UNI_EVT_RELAUNCHES and perform a reindexation