Question:

How can we verify that the data_engine is deleting old raw and historic data properly from the database?

Release:
Component: UIMDEG

Answer:

Below are the 2 stored procedures require to be created under CA_UIM Schema to verify data purge:
1. spn_verify_datapurge (Main Stored Procedure)
2. spn_validate_data
1.)
create or replace procedure spn_verify_datapurge AS
v_raw_days number;
v_hist_days number;
v_daily_days number;
v_tbl_num number;
v_index number;
v_count number;
v_rows_found number;
BEGIN
v_index :=1;
v_rows_found :=0;
select rawage,historyage,dailyavgage into v_raw_days,v_hist_days,v_daily_days from tbn_de_datamaintconfig;
select count(*) into v_tbl_num from s_qos_definition;
spn_validate_data('RN_QOS_DATA_',v_tbl_num,v_raw_days);
spn_validate_data('HN_QOS_DATA_',v_tbl_num,v_hist_days);
spn_validate_data('DN_QOS_DATA_',v_tbl_num,v_daily_days);
END spn_verify_datapurge;
2.)
create or replace procedure spn_validate_data(p_table_name IN varchar2,p_tbl_num IN number,p_retention_days IN number) AS
v_index number;
v_table_name varchar2(20);
v_count number;
v_rows_count number;
v_rows_found number;
BEGIN
v_index :=1;
v_rows_found := 0;
while(v_index <= p_tbl_num)
LOOP
v_table_name := p_table_name ||substr(('0000'||v_index),-4);
select count(*) into v_count from tab where tname=v_table_name;
if(v_count > 0) then
execute immediate
'select count(*) from '||v_table_name||' where sampletime < sysdate - '||p_retention_days into v_rows_count;
if(v_rows_count > 0) then
DBMS_OUTPUT.put_line(v_table_name || ' contains '||' samples which are older than '|| p_retention_days||' days');
v_rows_found := 1;
end if;
end if;
v_index := v_index + 1;
v_rows_count := 0;
v_count := 0;
END LOOP;
if(v_rows_found = 0) then
DBMS_OUTPUT.PUT_LINE('No unmaintained data found in ' || SUBSTR(p_table_name,0,2)||' table');
end if;
END spn_validate_data;
 
Below are the 2 ways we can execute stored procedure:
 
Execute spn_verify_datapurge
 
Or in some native clients below command will be used to execute stored procedure.
 
call spn_verify_datapurge
 
If the output from the script shows as follows then data_engine is honoring the retention policies configured in data_engine
No unmaintained data found in RN tables
No unmaintained data found in HN tables
No unmaintained data found in DN tables
 If, however you receive output that looks like the following, then a support case may need to be opened to fix the issue
RN_QOS_DATA_0001 contains  12 samples which are older than 30 days .
RN_QOS_DATA_0007 contains 25 samples which are older than 30 days.
RN_QOS_DATA_0008 contains 415 samples which are older than 30 days.