Manual maintenance for specific QOS_DATA tables

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

After executing the scripts provided in the KB article 34940 to check what tables have not been processed by the data_engine's automatic maintenance, i.e. data_engine is not deleting old raw and historic data properly from the database based on configured retention, some tables appear as showing unmaintained data:

https://knowledge.broadcom.com/external/article/34940

No unmaintained data found in RN tables
No unmaintained data found in HN tables
No unmaintained data found in DN tables

This is the expected result when maintenance completes without issues. However, there may be situations where some tables still contain rows with a sampletime older than the retention.

For example:

RN_QOS_DATA_0233 contains 21777 samples which are older than 37 days old.
RN_QOS_DATA_00235 contains 8 samples which are older than 37 days old.
No unmaintained data found in HN tables
No unmaintained data found in DN tables

This is a script provided by Sustaining Engineering to manually perform the maintenance on those specific QOS_DATA tables.

Environment

Release : 9.x, 20.x

Component : UIM - DATA_ENGINE

Cause

Sometimes the data_engine's automatic maintenance leaves some tables unprocessed.

Resolution

With below script we can do manual data maintenance for 2 tables. also please tell customer to not to execute below script when data maintenance job is running.

Upon completion of data purge for tables, in this example 233 and 235, please check the sampletime with below command to verify whether data purge happened as expected or not.

select min(sampletime) from RN_QOS_DATA_0233;

DECLARE    @return_value int,
    @pQosDefId int,
    @current_time datetime,
    @pErrorMessage ndtLongString
DECLARE qosid CURSOR FOR
    SELECT DISTINCT qos_def_id FROM S_QOS_DEFINITION where qos_def_id in(233,235);

OPEN qosid;
    FETCH NEXT FROM qosid INTO @pQosDefId
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SELECT  @current_time = GETDATE();
        EXEC    @return_value = [dbo].[spn_de_DataMaint]
            @pQosDefId = @pQosDefId,
            @pMode = 'all',
            @pCurrentTime = @current_time

        RAISERROR('qosid %d done. rc = %d. error = %s.',0,1,
            @pQosDefId,
            @return_value,
            @pErrorMessage) WITH NOWAIT
        FETCH NEXT FROM qosid INTO @pQosDefId
    END
CLOSE qosid;
DEALLOCATE qosid;

Additional Information

Note: Do not execute the script when the data_engine's automatic data maintenance job is running.