What are some options to delete QOS data?
search cancel

What are some options to delete QOS data?

book

Article ID: 277942

calendar_today

Updated On:

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 guidance on options to delete QOS data, e.g., to cleanup Test/DEV environment QOS or prune the data based on date.

Environment

  • Any DX UIM version

Cause

  • Guidance

Resolution

Yes, you can use the nas to run this script to delete QOS data but after adjusting the number of days.

Run the script via the nas. But as always, we recommend working with a DBA to assist and testing it out in a Test/DEV environment first and on a small scale, e.g., e.g., set the days to 1 and check the 'before and after' results.

---------------------------------------------------------------------------------------------------------------------------------------------------------

iDays = 30 -- delete qos-sources which did not deliver values in the past X days. Only specify FULL days here.
sDatabaseType = "sqlserver" -- specify the database type you're using. Valid values: sqlserver | mysql
-- if you have oracle, send support an e-mail
bHot = 0 -- set this to 0 if you do not want the script to perform any real modifications, set it to 1 to run it.

-- do not modify after this line unless you know what you're doing --
database.open("provider=nis;database=nis;driver=none") -- open the database

-- first, find all used RN_TABLES
sSql = "select distinct r_table from s_qos_data"
tRtables = database.query(sSql )

if bHot == 0 then
print( "-- table count: "..#tRtables.." RN-Tables" )
print( "-- the following output is valid SQL, you can copy this and execute it in your database shell manually" )
else
print( "housekeeping will delete data from "..#tRtables.." RN-Tables" )
end

-- now iterate over all rn_tables and determine orphaned qos-data
for iIx, tRow in pairs(tRtables) do
sDelQuery = ""
if sDatabaseType == "sqlserver" then
sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having datediff(day, max(sampletime), getdate() ) > "..iDays.." );"
end
if sDatabaseType == "mysql" then
sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having max(sampletime) < date_add( now(), INTERVAL -"..iDays.." DAY);"
end

if bHot ~= 1 then
print( sDelQuery )
else
print( "deleting orphaned qos-data for table "..tRow["r_table"] )
database.query(sDelQuery)
end

sDelUndeliveredQuery = "delete from s_qos_data where r_table='"..tRow["r_table"].."' and table_id not in (select distinct table_id from "..tRow["r_table"]..")"
if bHot ~= 1 then
print( sDelUndeliveredQuery )
else
print( "deleting never delivered qos-data for table "..tRow["r_table"] )
database.query( sDelUndeliveredQuery )
end
end

print( "-- housekeeping done." )

database.close()
--------------------------------------------------------------------------------------------------------------------------------------------------------

Another option in this case to delete QOS data using the snapshot table QOS data sampletime as a reference:

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. You can adjust the date.

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:

1. Deactivate the data_engine

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

3. Change/adjust the date to meet your historical data purge needs - this is just an example.

4. Run the query

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 data_engine retention settings.

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

---------------------------------------------------------------------------------------------------------------------------------------------------------

Other options:

If you want to take a more aggressive approach and delete ALL of the QOS data, not just raw/historical, please refer to the steps listed below

  1. Deactivate data_engine

  2. DELETE FROM s_qos_snapshot;

  3. DELETE FROM s_qos_data;

  4. DELETE FROM s_qos_snapshot;

  5. DELETE FROM s_qos_definition;

  6. Activate data_engine

  7. Restart every robot once to resend all QOS definitions

This will just wipe out everything if needed.

---------------------------------------------------------------------------------------------------------------------------------------------------------