Force recalculation over a particular time range

Products

• CA Business Service Insight

Issue/Introduction

The force recalculation script in KB 10171 removes all data for a metric. While this is generally recommended, it may be necessary to delete only back to a particular date.

 

Environment

Release : 8.35

Component : CA Business Service Insight

Resolution

To implement this force recalculation,

1. Stop the PslWriter services.
2. Run the partial clearAllMetricScript passing on the custom date range parameters, which clears calculated data for the date range passed in and to do force recalculation for a specific time frame.
3. Start the Psl Writer services.

 

--Clear all for metric

 

declare
SLA_NAME varchar2(100) := 'SContract27'; --Put the SLA_NAME (Contract name) here for which data need to be deleted
--MetricList constant  varchar2(400) := ('Metrc271','Metrc271_Duplicate2','MetricDomain');
Delete_Time_Stamp varchar2(100) := '31-DEC-17 00:00:00'; -- Add the date here for which more than that Time_stamp month/day/year the previously calculated data to be deleted and trigger recalculations.
CURSOR cur IS
 select distinct t.rule_id
  from t_rules_time_units t
 where t.rule_id in
       (SELECT r.psl_rule_id
          FROM T_RULES R, T_SLA_VERSIONS V, T_SLAS S
         WHERE V.SLA_ID = S.SLA_ID
           AND R.SLA_VERSION_ID = V.SLA_VERSION_ID
           AND V.STATUS in ('EFFECTIVE', 'NOT_EFFECTIVE')
           AND upper(S.SLA_NAME) =  upper(SLA_NAME)
           AND R.RULE_NAME in ('M1','M2')); -- put the comma separated metric names for many or single metricName in apostrophe here for which data need to be deleted
begin

FOR cur_rec IN cur LOOP

     update t_rules_time_units t 
     set t.last_complete_psl_record_date=null, 
         t.last_psl_record_date=null,
         t.last_psl_cycle_date=null, 
         t.first_complete_psl_record_date=null,
         t.first_complete_modify_date=null,
         t.min_time_new_raw_data=null,
         t.min_time_of_correction=null,
         t.min_time_not_used=null,
         t.min_time_of_exception=null,
         t.min_time_of_version=null,
         t.last_rt_error_code=null,
         t.last_rt_error=null 
     where t.rule_id=cur_rec.rule_id;
           
     update t_formulas t
     set t.last_infra_processor_date=null,
         t.initial_digest=null
     where t.formula_id in (select r.formula_id from t_rules r where r.rule_id=cur_rec.rule_id);
     
     delete from t_psl_corrections t 
     where t.rule_id=cur_rec.rule_id;
     
     delete from t_psl_exceptions t
     where t.rule_id=cur_rec.rule_id;
     
     delete from t_psl_0_day t
     where t.rule_id=cur_rec.rule_id and time_stamp_utc >= Delete_Time_Stamp; -- Time_stamp value greater than that data will get deleted for the metrics names of the contract specified at the beginning of the procedure
  delete from t_psl_0_week t
     where t.rule_id=cur_rec.rule_id and time_stamp_utc >= Delete_Time_Stamp;
  delete from t_psl_0_month t
     where t.rule_id=cur_rec.rule_id and time_stamp_utc >= Delete_Time_Stamp;
  delete from t_psl_0_quarter t
     where t.rule_id=cur_rec.rule_id and time_stamp_utc >= Delete_Time_Stamp;
  delete from t_psl_0_year t
     where t.rule_id=cur_rec.rule_id and time_stamp_utc >= Delete_Time_Stamp;
  delete from t_psl_0_hour t
     where t.rule_id=cur_rec.rule_id and time_stamp_utc >= Delete_Time_Stamp;
  delete from t_psl_1_all t
     where t.rule_id=cur_rec.rule_id and time_stamp_utc >= Delete_Time_Stamp;
     
     delete from t_slalom_outputs t
     where t.rule_id=cur_rec.rule_id and record_timestamp >= Delete_Time_Stamp;
     
     delete from t_rule_state_lines t
     where t.state_id in (select x.state_id from t_rule_states x where x.rule_id=cur_rec.rule_id);
     
     delete from t_rule_states t
     where t.rule_id=cur_rec.rule_id;
     
     delete from t_formula_events t 
     where t.formula_id in (select x.formula_id from t_rules x where x.rule_id=cur_rec.rule_id);
     
     delete from t_formula_intermediate_events t
     where t.formula_id in (select x.formula_id from t_rules x where x.rule_id=cur_rec.rule_id);
     
     delete from t_formula_metrics t
     where t.formula_id in (select x.formula_id from t_rules x where x.rule_id=cur_rec.rule_id);
     
     delete from t_intermediate_data t
     where t.metric_id = cur_rec.rule_id;
     
     delete from t_formula_cluster_item_events t 
     where t.formula_id in (select x.formula_id from t_rules x where x.rule_id=cur_rec.rule_id);
     
     delete from t_formula_infrastruct_versions t 
     where t.formula_id in (select x.formula_id from t_rules x where x.rule_id=cur_rec.rule_id);
         
     
     commit;
   END LOOP;
    

 end;