How can I force a recalculation of a metric or contract, and when might this be needed?
Article ID: 10171
Updated On:
Products
Issue/Introduction
Under normal circumstances, BSI will automatically perform a recalculation of a metric or contract when it is required. For example, when a new version of a contract is committed, when a new business logic module is committed, or when old events are loaded into the system which require recalculation from the date of those events moving forward. Those are just some of the examples which require a recalculation of metrics or a full contract.
Sometimes, under certain circumstances, this automatic recalculation is not enough. For example, the automatic recalculation assumes that the old data is not corrupt and may rely on previous calculations being valid. This may occur if the ACE engine crashes for reasons like the database running out of space or bad network connections to a remote database. In that case the recalculation may not be able to complete. Another situation might be where your business logic scripts write to the T_SLALOM_OUTPUT table. The automatic recalculation does not cleanup user data that you write to these tables and if your script does not manage these entries properly this can sometimes cause duplicate records and other problems on an automatic recalculation.
When a case like this happens where the metrics are valid (and do not error out) but a recalculation is not completing, you can use the scripts below in order to force a full recalculation. These scripts differ from the system recalculation in that they cleanout all the old calculation data. This causes the ACE engine to run the recalculation on it's own. They also clear out the user data such as T_SLALOM_OUTPUT.
Please note that these scripts run directly against the database and it is recommended that you backup your database first.
Environment
Component:
Resolution
1) It is recommended that you backup your database first. These scripts should be run by a DBA or someone who is familiar with your Oracle installation and they should be run at your own risk.
2) Stop the PSLwriter services on your APP server. If you are using ACE2 then stop the PSLworker service as well. (note that ACE1 and ACE2 should not be running at the same time)
3) In SQLdeveloper, connect to the BSI database using your BSI admin account. This is usually an account called Oblicore.
4) If you are trying to force a recalc on a single metric or metrics, then you can run the below script. Be sure to run this in SQLdeveloper "as script" and not as a query. These are two different buttons in SQLdeveloper. Replace the name of the metric and the contract in the script before you run it. If your metric or contract name has any reserved Oracle characters in them, be sure you escape those characters with a backslash. Oracle reserved characters can be found listed here: https://docs.oracle.com/cd/B10501_01/text.920/a96518/cqspcl.htm
--Clear all for metric
declare
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 S.SLA_NAME = '' --Put the SLA_NAME (Contract name) here
AND R.RULE_NAME = '' ); -- put the metric name here
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;
delete from t_psl_0_week t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_month t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_quarter t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_year t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_hour t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_1_all t
where t.rule_id=cur_rec.rule_id;
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);
delete from t_slalom_outputs t
where t.rule_id=cur_rec.rule_id;
commit;
END LOOP;
end;
5) If you need to force a recalculation for an entire Contract, then you can instead use the script below. As previously mentioned, this should be run from sqlDeveloper using the Oblicore account (or equivalent), and must be run "as script". Replace the contract name where indicated and escape special characters if needed.
declare
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 s.sla_name = '' --Put the SLA_NAME here
AND V.SLA_ID = S.SLA_ID
AND R.SLA_VERSION_ID = V.SLA_VERSION_ID
AND V.STATUS in ('EFFECTIVE', 'NOT_EFFECTIVE'));
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;
delete from t_psl_0_week t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_month t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_quarter t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_year t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_0_hour t
where t.rule_id=cur_rec.rule_id;
delete from t_psl_1_all t
where t.rule_id=cur_rec.rule_id;
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);
delete from t_slalom_outputs t
where t.rule_id=cur_rec.rule_id;
END LOOP;
commit;
end;
6) Now restart the PSLwriters or PSLworker and they will start to recalculate the metrics or contracts. Keep in mind that this does a full recalc and if you have a contract with years worth of data, it's not impossible that this could take days to fully recalculate in a worst case scenario.
Feedback
