How to delete historical QOS data older than a specific date

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Customer wanted to know how to perform a database 'historical data reset' to delete all historical database performance data but not change nor interfere with the configurations of the probes and NAS (profiles).

Environment

• DX UIM, Any version

Cause

• Data administration for test environments

Resolution

IMPORTANT!!! This is meant for a Test/DEV environment NOT a Production environment...

In any case, the best practice is to take a backup/snapshot of the Database.

You can also test this on a small scale first/delete only 1 days' worth of data and then check to make sure it worked as expected.

But if this is a TEST/DEV environment, and you don't care if you lose the QOS data in the database, you can run through the following steps:

To delete historical QOS data older than a particular date:

1. Deactivate the data_engine

2. Run select * from s_qos_snapshot and examine the rows/sampletime and dates since the delete will be based on these.

2. Change/adjust the date to meet your historical data purge needs.

delete sqd
from s_qos_data sqd
inner join s_qos_snapshot sqs on sqd.table_id = sqs.table_id
where sqs.sampletime < '2023-12-31';

The raw data in the RN tables should age out over time automatically via data_engine nightly maintenance based on the retention settings.

Your DBA should make sure there is enough memory configured to handle any long running delete jobs in general.

To delete all QOS data
(e.g., in a test/dev/lab environment for the sake of QOS data cleanup):

Aside from what's described above to delete historical QOS data based on a date, if you want to take a more aggressive approach and delete ALL of the QOS data, not just historical, you can do the following:

- Deactivate data_engine
- DELETE FROM s_qos_snapshot;
- DELETE FROM s_qos_data;
- DELETE FROM s_qos_definition;
- Activate data_engine
- Restart every robot once to resend all QOS definitions

This will just wipe out everything if needed.

Neither approach affects the probe configurations or NAS (profiles).