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 for example the S_QOS_DATA table based on date.

Environment

  • Any DX UIM version

Cause

  • Guidance

Resolution

Option 1

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()
--------------------------------------------------------------------------------------------------------------------------------------------------------

Option 2

Another option in this case is 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.

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

Option 3 (wiping out all QOS data in a Lab environment)

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.

 

Option 4

orphans_query.sql

--- Instructions (Warning: TEST in a TEST environment first!) - USE AT YOUR OWN RISK!
--- Take a BACKUP of the S_QOS_DATA table to be safe
--  Note that after you run the query once, if it fails on the orphans table, for example with "Msg 208, Level 16, State 0, Line 1
--  Invalid object name '#orphans'" run DROP TABLE orphans and then you can run it again successfully.
--- deactivate data_engine
--- execute the query:  UPDATE S_QOS_DATA SET CI_METRIC_ID = NULL;
--- activate data_engine
--- WAIT at least 24 hours or longer, e.g., 3 days, a week, whatever makes sense to be safe/sure that all live data is being updated and no QOS collection has been disabled/temporarily turned off because if it has, you may end up deleting QOS data that you only temporarily disabled for some valid reason.
--- after 24 hours or longer, there will be several entries in S_QOS_DATA THAT REPRESENT METRICS THAT ARE NO LONGER BEING COLLECTED.
--- you can then run this script and it will go through all of these entries with a NULL ci_metric_id and check to see if there is any data in the RN_QOS_DATA associated with the entry.
--- If there is no data in RN_QOS_DATA_XXXX for the entry, an entry/entries will be created in a table called #orphans
--- this entry contains the rn_table and table_id 
--- ***You can then run this query to DELETE the 'orphaned' entries: DELETE from S_QOS_DATA WHERE table_id in (select table_id from #orphans)
--  Run select count(*) from S_QOS_DATA to see the row count, then after the query is run, run it again to see the count after the query completes.

--============================================================================

declare @table_id int,
@rn_table varchar(255),
@fstatus int,
@sql varchar(500);

IF NOT EXISTS (select * from information_schema.tables where table_name = 'orphans')
BEGIN
create table #orphans (
rn_table varchar(50),
table_id int
);
END

declare a cursor for
select table_id,r_table from s_qos_data where ci_metric_id is null;

open a;

fetch next from a into @table_id, @rn_table;

set @fstatus = (select top 1 fetch_status from sys.dm_exec_cursors(0) where name = 'a' order by statement_start_offset desc);
while (@fstatus = 0)
BEGIN

set @sql = 'IF NOT EXISTS (SELECT * FROM ' + @rn_table + ' WHERE TABLE_ID = ' + cast(@table_id as varchar) + ') INSERT INTO #orphans(rn_table,table_id) VALUES(' + CHAR(39) + @rn_table + CHAR(39) + ',' + cast(@table_id as varchar) +')';
--print(@sql)
exec(@sql)

fetch next from a into @table_id, @rn_table;
set @fstatus = (select top 1 fetch_status from sys.dm_exec_cursors(0) where name = 'a' order by statement_start_offset desc);
END

close a;
deallocate a;

--DROP TABLE orphans
--DELETE from S_QOS_DATA WHERE table_id in (select table_id from #orphans)
===============================================================================================================================

Powered by Wolken Software