Rem drv: Rem Rem $Header: baselines_pkgbodys.sql 01-jul-2005.21:25:06 gsbhatia Exp $ Rem Rem baselines_pkgbodys.sql Rem Rem Copyright (c) 2002, 2005, Oracle. All rights reserved. Rem Rem NAME Rem baselines_pkgbodys.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem ktlaw 01/11/05 - add repmgr header Rem jsoule 07/02/04 - add wrapper for baselineable check Rem njuillar 11/06/03 - perf fixes Rem njuillar 10/01/03 - Fixed bug 3146069 Rem njuillar 07/23/03 - Fixed delete_past_changes procedure Rem yaofeng 06/05/03 - fix wrong condition Rem jpyang 04/28/03 - fix sql Rem ancheng 04/04/03 - change get_edit_metric_thresholds Rem njuillar 04/23/03 - Added get_src_dest_tgt_diff_counts function Rem ancheng 03/26/03 - support 5 composite keys Rem aholser 04/08/03 - return emd_url from saveThresholds Rem jpyang 03/31/03 - nls support Rem ancheng 02/13/03 - target version fix Rem ancheng 12/20/02 - target version support Rem ancheng 11/07/02 - add get_edit_metric_thresholds Rem tjaiswal 09/03/02 - Add composite key support Rem tjaiswal 08/13/02 - Edit thresholds enhancements Rem tjaiswal 07/18/02 - Add plsql for copy metric settings Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE body emd_mntr_baseline AS -- ============================================================================ -- a private util function to check if the current user has the privilege -- privilege_in on target - target_guid_in -- ============================================================================ PROCEDURE check_target_priv_error( target_guid_in IN mgmt_targets.target_guid%TYPE, privilege_in VARCHAR2 ) IS l_user_name VARCHAR2(64) := ''; l_has_priv_val NUMBER(1) := MGMT_GLOBAL.G_FALSE; BEGIN -- get the current user name l_user_name := mgmt_user.get_current_em_user(); -- check for the privilege l_has_priv_val := mgmt_user.has_priv(l_user_name, privilege_in, target_guid_in); IF(l_has_priv_val=MGMT_GLOBAL.G_FALSE) THEN -- user does not have the proper privileges RAISE MGMT_GLOBAL.insufficient_privileges; END IF; END check_target_priv_error; -- a private util function to determine if baseline - baseline_name_in of target -- target_guid_in exists -- returns G_FALSE if the rule does not exist, G_TRUE if rule exists FUNCTION baseline_exists (baseline_name_in IN VARCHAR2, target_guid_in IN mgmt_targets.target_guid%TYPE) RETURN NUMBER IS l_baseline_name VARCHAR2(256) := ''; BEGIN SELECT baseline_name INTO l_baseline_name FROM mgmt_target_baselines WHERE baseline_name=baseline_name_in AND target_guid=target_guid_in AND rownum=1; l_baseline_name := NVL(l_baseline_name, ''); IF( length(trim(l_baseline_name)) <> 0 ) THEN RETURN MGMT_GLOBAL.G_TRUE; END IF; RETURN MGMT_GLOBAL.G_FALSE; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN MGMT_GLOBAL.G_FALSE; END baseline_exists; -- a private function to de-activate(set is_active to 0) all the baselines of -- a target PROCEDURE deactive_baselines( target_guid_in IN mgmt_targets.target_guid%TYPE ) IS BEGIN UPDATE mgmt_target_baselines b SET b.is_active = 0 WHERE b.target_guid = target_guid_in AND b.is_active = 1; END deactive_baselines; -- a private function to add a target baseline PROCEDURE add_baseline( baseline_name_in IN VARCHAR2, target_guid_in IN mgmt_targets.target_guid%TYPE, baseline_date_in IN DATE DEFAULT TRUNC(SYSDATE, 'DD') ) IS BEGIN INSERT INTO mgmt_target_baselines(baseline_name, target_guid, baseline_date) VALUES (baseline_name_in, target_guid_in, baseline_date_in); END add_baseline; -- a private function to set the target baseline properties PROCEDURE set_baseline_properties( baseline_name_in IN VARCHAR2, target_guid_in IN mgmt_targets.target_guid%TYPE, baseline_date_in IN VARCHAR2, date_format_in IN VARCHAR2, baseline_active_in IN NUMBER, mode_in IN NUMBER ) IS BEGIN -- for a given target_guid, there can be only one baseline that is active -- it is okay if none of the baselines for the given target_guid is active -- however, not more than one baseline for a given target_guid can be active -- at any given time ... -- ensuring that this is followed -- if baseline_active_in = 1, then first deactive the active baseline for this -- target_guid ... IF( baseline_active_in = 1 ) THEN deactive_baselines( target_guid_in ); END IF; -- now set the properties for baseline - baseline_name_in of target_guid - -- target_guid_int IF( mode_in=CREATE_MODE ) THEN -- in this case always use "0" for is_active UPDATE mgmt_target_baselines b SET b.baseline_date = to_date(baseline_date_in, date_format_in), b.is_active = 0 WHERE b.baseline_name = baseline_name_in AND b.target_guid = target_guid_in; ELSIF( mode_in=EDIT_MODE ) THEN -- in this case, baseline_active_in can be: 0, 1, -1 -- if baseline_active_in is -1(meaning ignore) then do not update the -- the is_active value IF( baseline_active_in=IGNORE_ACTIVATE_VALUE ) THEN UPDATE mgmt_target_baselines b SET b.baseline_date = to_date(baseline_date_in, date_format_in) WHERE b.baseline_name = baseline_name_in AND b.target_guid = target_guid_in; ELSE UPDATE mgmt_target_baselines b SET b.baseline_date = to_date(baseline_date_in, date_format_in), b.is_active = baseline_active_in WHERE b.baseline_name = baseline_name_in AND b.target_guid = target_guid_in; END IF; END IF; END set_baseline_properties; -- a private function to delete the baseline thresholds data PROCEDURE delete_baseline_thr_data( baseline_name_in IN VARCHAR2, target_guid_in IN mgmt_targets.target_guid%TYPE ) IS BEGIN DELETE FROM mgmt_target_baselines_data WHERE target_guid=target_guid_in AND baseline_name=baseline_name_in; END delete_baseline_thr_data; -- a private function to set the baseline thresholds data PROCEDURE set_baseline_thr_data( baseline_name_in IN VARCHAR2, target_guid_in IN mgmt_targets.target_guid%TYPE, baseline_thr_data_in MGMT_BASELINE_OBJ_ARRAY ) IS L_NO_METRIC_GUID constant raw(16) := '0000000000000000'; l_comp_key_guid RAW(16) := L_NO_METRIC_GUID; l_processed_key_value RAW(16) := L_NO_METRIC_GUID; BEGIN -- NOTE: delete_baseline_thr_data should be called before this procedure is -- called if editing the baseline ... FOR record IN ( SELECT baseline_name_in, target_guid_in, b.metric_guid, b.key_value, b.value_average, b.value_minimum, b.value_maximum, b.warning_operator, b.warning_threshold, b.critical_operator, b.critical_threshold, b.num_keys, b.key_part1_value, b.key_part2_value, b.key_part3_value, b.key_part4_value, b.key_part5_value FROM TABLE(CAST(baseline_thr_data_in AS MGMT_BASELINE_OBJ_ARRAY) ) b ) LOOP -- insert the row data into target baselines data table INSERT INTO mgmt_target_baselines_data( baseline_name, target_guid, metric_guid, key_value, value_average, value_minimum, value_maximum, warning_operator, warning_threshold, critical_operator, critical_threshold) VALUES (record.baseline_name_in, record.target_guid_in, record.metric_guid, NVL(record.key_value, ' '), record.value_average, record.value_minimum, record.value_maximum, record.warning_operator, record.warning_threshold, record.critical_operator, record.critical_threshold); -- if this is a composite key, update the mgmt metrics composite keys -- table also IF( record.num_keys > 1 AND record.key_value IS NOT NULL AND length(trim(record.key_value)) <> 0 ) THEN -- this is a composite key and is a row with a key value. -- record.key_value will have the composite key -- find out if this composite key for the l_target_guid exists. -- also note that record.key_value will have the key value -- as string. so convert it to raw l_processed_key_value := HEXTORAW(record.key_value); BEGIN SELECT compkeys.composite_key INTO l_comp_key_guid FROM mgmt_metrics_composite_keys compkeys WHERE compkeys.target_guid = target_guid_in AND compkeys.composite_key = l_processed_key_value; EXCEPTION WHEN NO_DATA_FOUND THEN l_comp_key_guid := L_NO_METRIC_GUID; END; IF( l_comp_key_guid = L_NO_METRIC_GUID ) THEN -- if the composite key does not exist in the mgmt metrics composite -- keys table, insert a row for this composite key along with the -- key part value INSERT INTO mgmt_metrics_composite_keys(target_guid, composite_key, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value) VALUES (target_guid_in, l_processed_key_value, record.key_part1_value, record.key_part2_value, record.key_part3_value, record.key_part4_value, record.key_part5_value); END IF; END IF; END LOOP; END set_baseline_thr_data; -- a private function to delete the baseline thresholds data PROCEDURE delete_baseline( baseline_name_in IN VARCHAR2, target_guid_in IN mgmt_targets.target_guid%TYPE ) IS BEGIN -- first delete the data from mgmt_target_baselines DELETE FROM mgmt_target_baselines WHERE target_guid=target_guid_in AND baseline_name=baseline_name_in; -- next delete the data from mgmt_target_baselines_data DELETE FROM mgmt_target_baselines_data WHERE target_guid=target_guid_in AND baseline_name=baseline_name_in; END delete_baseline; -- -- Name: get_edit_metric_thresholds -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get the metric thresholds data for the given target. -- PROCEDURE get_edit_metric_thresholds(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2 DEFAULT NULL, metric_column_in IN VARCHAR2 DEFAULT NULL, target_guid_out OUT RAW, has_priv_out OUT NUMBER, metric_thresholds_cur_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; target_guid_out := l_target_guid; -- check if the user has at least OPERATOR_TARGET privilege has_priv_out := mgmt_user.has_priv( mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, l_target_guid); IF(metric_name_in is NOT NULL AND metric_column_in is NOT NULL) THEN OPEN metric_thresholds_cur_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.column_label, met.key_column, met.metric_type, thr.coll_name, thr.key_value, thr.eval_order, thr.warning_threshold, thr.critical_threshold, thr.warning_operator, thr.critical_operator, thr.num_occurences, thr.fixit_job, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, met.column_label_nlsid FROM mgmt_metrics met, mgmt_metric_thresholds thr, (SELECT composite_key, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value FROM mgmt_metrics_composite_keys WHERE target_guid = l_target_guid) compkeys WHERE thr.target_guid = l_target_guid AND met.target_type = target_type_in AND met.metric_guid = thr.metric_guid AND thr.key_value = compkeys.composite_key (+) AND met.metric_name = metric_name_in AND met.metric_column = metric_column_in AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' '); ELSE OPEN metric_thresholds_cur_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.column_label, met.key_column, met.metric_type, thr.coll_name, thr.key_value, thr.eval_order, thr.warning_threshold, thr.critical_threshold, thr.warning_operator, thr.critical_operator, thr.num_occurences, thr.fixit_job, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, met.column_label_nlsid FROM mgmt_metrics met, mgmt_metric_thresholds thr, (SELECT composite_key, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value FROM mgmt_metrics_composite_keys WHERE target_guid = l_target_guid) compkeys WHERE thr.target_guid = l_target_guid AND met.target_type = target_type_in AND met.metric_guid = thr.metric_guid AND thr.key_value = compkeys.composite_key (+) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') ORDER BY met.column_label, thr.eval_order, thr.key_value; END IF; END get_edit_metric_thresholds; -- -- Name: get_metric_threshold_data -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get the metric thresholds data for the given target: wrapper for the -- get_edit_metric_thresholds procedure. -- PROCEDURE get_metric_threshold_data(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2 DEFAULT NULL, metric_column_in IN VARCHAR2 DEFAULT NULL, target_guid_out OUT RAW, has_priv_out OUT NUMBER, is_baselineable_out OUT VARCHAR2, metric_thresholds_cur_out OUT cursorType) IS BEGIN emd_mntr_baseline.get_edit_metric_thresholds(target_name_in, target_type_in, metric_name_in, metric_column_in, target_guid_out, has_priv_out, metric_thresholds_cur_out); -- Determine if this target is baselineable. -- The enclosed cursor ought to produce 0 or 1 row. is_baselineable_out := 'false'; FOR l_target IN (SELECT DECODE(LOWER(p.property_value), 'true', 'true', 'false') is_baselineable FROM mgmt_target_properties p WHERE p.target_guid = target_guid_out AND p.property_type = 'DYNAMICINSTANCE' AND p.property_name = 'IsBaselineable') LOOP is_baselineable_out := l_target.is_baselineable; END LOOP; END get_metric_threshold_data; -- -- Name: get_target_baselines -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get the baselines for the given target. -- PROCEDURE get_target_baselines( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, baselines_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- get the target guid l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); -- now get the target baselines ... OPEN baselines_out FOR SELECT baseline_name, baseline_date, to_char(baseline_date, mgmt_global.G_INTERNAL_DATE_FORMAT) "baseline_date_string", is_active, target_guid FROM mgmt_target_baselines WHERE target_guid=l_target_guid; END get_target_baselines; -- -- Name: edit_baseline -- -- Package: emd_mntr_baseline -- -- Purpose: -- Create/Edit the baseline -- PROCEDURE edit_baseline( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, baseline_name_in IN VARCHAR2, baseline_date_in IN VARCHAR2, date_format_in IN VARCHAR2, baseline_active_in IN NUMBER, baseline_thr_data_in IN MGMT_BASELINE_OBJ_ARRAY, mode_in IN NUMBER ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_user_name VARCHAR2(64) := ''; l_priv_result NUMBER := 0; l_baseline_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE; BEGIN -- get the target guid l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); -- first get the current user name from sys context l_user_name := mgmt_user.get_current_em_user(); -- now figure out if the current user has the privileges to create/edit -- a target baseline for the given target_guid l_priv_result := MGMT_USER.HAS_PRIV( l_user_name, 'OPERATOR_TARGET', l_target_guid ); IF( l_priv_result = 0 ) THEN -- user does not have the proper privileges RAISE MGMT_GLOBAL.insufficient_privileges; END IF; -- find out if the baseline - baseline_name_in for target_guid - target_guid_in -- exists ... l_baseline_exists_val := baseline_exists(baseline_name_in, l_target_guid); -- if this is create, check to see if this baseline name is a duplicate of -- an existing baseline IF( mode_in=CREATE_MODE ) THEN IF( l_baseline_exists_val=MGMT_GLOBAL.G_TRUE ) THEN RAISE MGMT_GLOBAL.duplicate_baseline_name; END IF; END IF; -- if this is edit, check to see if this baseline name for the given target_guid -- exists IF( mode_in=EDIT_MODE ) THEN IF( l_baseline_exists_val=MGMT_GLOBAL.G_FALSE ) THEN RAISE MGMT_GLOBAL.baseline_does_not_exist; END IF; END IF; -- all the error conditions have been taken care of. create/edit the baseline IF( mode_in=CREATE_MODE ) THEN add_baseline( baseline_name_in, l_target_guid ); END IF; -- set the baseline properties set_baseline_properties( baseline_name_in, l_target_guid, baseline_date_in, date_format_in, baseline_active_in, mode_in ); IF( mode_in=EDIT_MODE ) THEN delete_baseline_thr_data( baseline_name_in, l_target_guid ); END IF; -- add the baseline thresholds data set_baseline_thr_data( baseline_name_in, l_target_guid, baseline_thr_data_in ); END edit_baseline; -- -- Name: delete_baseline -- -- Package: emd_mntr_baseline -- -- Purpose: -- Delete the baseline -- PROCEDURE delete_baseline( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, baseline_name_in IN VARCHAR2 ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_user_name VARCHAR2(64) := ''; l_priv_result NUMBER := 0; l_baseline_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE; BEGIN -- get the target guid l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); -- first get the current user name from sys context l_user_name := mgmt_user.get_current_em_user(); -- now figure out if the current user has the privileges to delete -- a target baseline for the given target_guid l_priv_result := MGMT_USER.HAS_PRIV( l_user_name, 'OPERATOR_TARGET', l_target_guid ); IF( l_priv_result = 0 ) THEN -- user does not have the proper privileges RAISE MGMT_GLOBAL.insufficient_privileges; END IF; -- find out if the baseline - baseline_name_in for target_guid - target_guid_in -- exists ... l_baseline_exists_val := baseline_exists(baseline_name_in, l_target_guid); IF( l_baseline_exists_val=MGMT_GLOBAL.G_FALSE ) THEN RAISE MGMT_GLOBAL.baseline_does_not_exist; END IF; -- all error conditions have been taken care of. now delete the baseline delete_baseline( baseline_name_in, l_target_guid ); END delete_baseline; -- -- Name: get_baseline_data -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get the baseline data for a given baseline name. -- PROCEDURE get_baseline_data( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, baseline_name_in IN VARCHAR2, baseline_date_out OUT DATE, baseline_date_string_out OUT VARCHAR2, baseline_active_out OUT NUMBER, baseline_data_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; l_baseline_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN -- initialize OUT parameters baseline_date_out := SYSDATE; baseline_date_string_out := ''; baseline_active_out := 0; -- get the target guid and category property columns BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; -- now check to see if the baseline - baseline_name_in for target_guid - -- l_target_guid exists ... l_baseline_exists_val := baseline_exists(baseline_name_in, l_target_guid); IF( l_baseline_exists_val=MGMT_GLOBAL.G_FALSE ) THEN RAISE MGMT_GLOBAL.baseline_does_not_exist; END IF; -- error conditions have been taken care of. now get the baseline data ... -- no need to handle the exception in the query below because the existence -- of the baseline has already been done above ... SELECT baseline_date, to_char(baseline_date, mgmt_global.G_INTERNAL_DATE_FORMAT) "baseline_date_string", is_active INTO baseline_date_out, baseline_date_string_out, baseline_active_out FROM mgmt_target_baselines WHERE baseline_name = baseline_name_in AND target_guid= l_target_guid; -- get the baseline thresholds data ... OPEN baseline_data_out FOR select met.metric_name, met.metric_column, met.metric_label, met.column_label, met.key_column, met.metric_type, bas.key_value, bas.value_average, bas.value_minimum, bas.value_maximum, bas.warning_threshold, bas.critical_threshold, bas.warning_operator, bas.critical_operator, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, bas.metric_guid, bas.target_guid, met.metric_label_nlsid, met.column_label_nlsid, met.target_type from mgmt_target_baselines_data bas, mgmt_metrics met, mgmt_metrics_composite_keys compkeys where bas.target_guid=l_target_guid and bas.baseline_name=baseline_name_in and met.target_type=target_type_in and met.metric_guid=bas.metric_guid and bas.target_guid=compkeys.target_guid(+) and bas.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') order by met.column_label; END get_baseline_data; -- -- Name: get_baseline_data_for_date -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get the baseline data for the given date. This data will be used in creating -- or editing a baseline -- PROCEDURE get_baseline_data_for_date( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, baseline_date_in IN VARCHAR2, date_format_in IN VARCHAR2, rollup_mode_in IN NUMBER, baseline_data_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; IF( rollup_mode_in = DAY_ROLLUP_MODE ) THEN -- get the data from 1day metrics rollup table OPEN baseline_data_out FOR select met.metric_name, met.metric_column, met.metric_label, met.column_label, met.key_column, met.metric_type, thr_data.coll_name, thr_data.key_value, thr_data.eval_order, NVL(mday_data.value_average, -1) "value_average", NVL(mday_data.value_minimum, -1) "value_minimum", NVL(mday_data.value_maximum, -1) "value_maximum", thr_data.warning_threshold, thr_data.critical_threshold, thr_data.warning_operator, thr_data.critical_operator, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, thr_data.metric_guid, thr_data.target_guid, met.metric_label_nlsid, met.column_label_nlsid, met.target_type from (select /*+ INDEX(thr) */ thr.coll_name, thr.key_value, thr.eval_order, thr.warning_threshold, thr.critical_threshold, thr.warning_operator, thr.critical_operator, thr.metric_guid, thr.target_guid from mgmt_metric_thresholds thr where thr.target_guid=l_target_guid) thr_data, (select /*+ INDEX(mday) */ mday.key_value, mday.value_average, mday.value_minimum, mday.value_maximum, mday.metric_guid, mday.target_guid from mgmt_metrics_1day mday where mday.target_guid=l_target_guid and mday.rollup_timestamp=to_date(baseline_date_in, date_format_in)) mday_data, (select ck.* from mgmt_metrics_composite_keys ck where ck.target_guid=l_target_guid ) compkeys, mgmt_metrics met where met.target_type=target_type_in and met.metric_guid=thr_data.metric_guid and thr_data.metric_guid=mday_data.metric_guid(+) and thr_data.key_value=mday_data.key_value(+) and thr_data.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') order by met.column_label, thr_data.eval_order, thr_data.key_value; ELSE -- get the data from 1hour metrics rollup table OPEN baseline_data_out FOR select met.metric_name, met.metric_column, met.metric_label, met.column_label, met.key_column, met.metric_type, thr_data.coll_name, thr_data.key_value, thr_data.eval_order, NVL(mhour_data.value_average, -1) "value_average", NVL(mhour_data.value_minimum, -1) "value_minimum", NVL(mhour_data.value_maximum, -1) "value_maximum", thr_data.warning_threshold, thr_data.critical_threshold, thr_data.warning_operator, thr_data.critical_operator, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, thr_data.metric_guid, thr_data.target_guid, met.metric_label_nlsid, met.column_label_nlsid, met.target_type from (select /*+ INDEX(thr) */ thr.coll_name, thr.key_value, thr.eval_order, thr.warning_threshold, thr.critical_threshold, thr.warning_operator, thr.critical_operator, thr.metric_guid, thr.target_guid from mgmt_metric_thresholds thr where thr.target_guid=l_target_guid) thr_data, (select /*+ INDEX(mhour) */ mhour.key_value, mhour.value_average, mhour.value_minimum, mhour.value_maximum, mhour.metric_guid, mhour.target_guid from mgmt_metrics_1hour mhour where mhour.target_guid=l_target_guid and mhour.rollup_timestamp=to_date(baseline_date_in, date_format_in)) mhour_data, (select ck.* from mgmt_metrics_composite_keys ck where ck.target_guid=l_target_guid ) compkeys, mgmt_metrics met where met.target_type=target_type_in and met.metric_guid=thr_data.metric_guid and thr_data.metric_guid=mhour_data.metric_guid(+) and thr_data.key_value=mhour_data.key_value(+) and thr_data.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') order by met.column_label, thr_data.eval_order, thr_data.key_value; END IF; END get_baseline_data_for_date; -- -- Name: get_baseline_data_for_thr -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get the baseline data for the given date and passed in metric thresholds data. -- This data will be used in creating or editing a baseline -- PROCEDURE get_baseline_data_for_thr( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, baseline_date_in IN VARCHAR2, date_format_in IN VARCHAR2, rollup_mode_in IN NUMBER, met_thr_data_in IN MGMT_METRIC_THR_OBJ_ARRAY, baseline_data_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; IF( rollup_mode_in = DAY_ROLLUP_MODE ) THEN -- get the data from 1day metrics rollup table OPEN baseline_data_out FOR select met.metric_name, met.metric_column, met.metric_label, met.column_label, met.key_column, met.metric_type, thr_data.coll_name, thr_data.key_value, thr_data.eval_order, NVL(mday_data.value_average, -1) "value_average", NVL(mday_data.value_minimum, -1) "value_minimum", NVL(mday_data.value_maximum, -1) "value_maximum", thr_data.warning_threshold, thr_data.critical_threshold, thr_data.warning_operator, thr_data.critical_operator, compkeys.composite_key, NVL(compkeys.key_part1_value, thr_data.key_part1_value) "key_part1_value", NVL(compkeys.key_part2_value, thr_data.key_part2_value) "key_part2_value", NVL(compkeys.key_part3_value, thr_data.key_part3_value) "key_part3_value", NVL(compkeys.key_part4_value, thr_data.key_part4_value) "key_part4_value", NVL(compkeys.key_part5_value, thr_data.key_part5_value) "key_part5_value", met.num_keys, thr_data.metric_guid, thr_data.target_guid, met.metric_label_nlsid, met.column_label_nlsid, met.target_type from TABLE(CAST(met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY)) thr_data, (select /*+ INDEX(mday) */ mday.key_value, mday.value_average, mday.value_minimum, mday.value_maximum, mday.metric_guid, mday.target_guid from mgmt_metrics_1day mday where mday.target_guid=l_target_guid and mday.rollup_timestamp=to_date(baseline_date_in, date_format_in)) mday_data, (select ck.* from mgmt_metrics_composite_keys ck where ck.target_guid=l_target_guid ) compkeys, mgmt_metrics met where met.target_type=target_type_in and met.metric_guid=thr_data.metric_guid and thr_data.metric_guid=mday_data.metric_guid(+) and thr_data.key_value=mday_data.key_value(+) and thr_data.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') order by met.column_label, thr_data.eval_order, thr_data.key_value; ELSE -- get the data from 1hour metrics rollup table OPEN baseline_data_out FOR select met.metric_name, met.metric_column, met.metric_label, met.column_label, met.key_column, met.metric_type, thr_data.coll_name, thr_data.key_value, thr_data.eval_order, NVL(mhour_data.value_average, -1) "value_average", NVL(mhour_data.value_minimum, -1) "value_minimum", NVL(mhour_data.value_maximum, -1) "value_maximum", thr_data.warning_threshold, thr_data.critical_threshold, thr_data.warning_operator, thr_data.critical_operator, compkeys.composite_key, NVL(compkeys.key_part1_value, thr_data.key_part1_value) "key_part1_value", NVL(compkeys.key_part2_value, thr_data.key_part2_value) "key_part2_value", NVL(compkeys.key_part3_value, thr_data.key_part3_value) "key_part3_value", NVL(compkeys.key_part4_value, thr_data.key_part4_value) "key_part4_value", NVL(compkeys.key_part5_value, thr_data.key_part5_value) "key_part5_value", met.num_keys, thr_data.metric_guid, thr_data.target_guid, met.metric_label_nlsid, met.column_label_nlsid, met.target_type from TABLE(CAST(met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY)) thr_data, (select /*+ INDEX(mhour) */ mhour.key_value, mhour.value_average, mhour.value_minimum, mhour.value_maximum, mhour.metric_guid, mhour.target_guid from mgmt_metrics_1hour mhour where mhour.target_guid=l_target_guid and mhour.rollup_timestamp=to_date(baseline_date_in, date_format_in)) mhour_data, (select ck.* from mgmt_metrics_composite_keys ck where ck.target_guid=l_target_guid ) compkeys, mgmt_metrics met where met.target_type=target_type_in and met.metric_guid=thr_data.metric_guid and thr_data.metric_guid=mhour_data.metric_guid(+) and thr_data.key_value=mhour_data.key_value(+) and thr_data.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') order by met.column_label, thr_data.eval_order, thr_data.key_value; END IF; END get_baseline_data_for_thr; -- -- Name: save_thresholds -- -- Package: emd_mntr_baseline -- -- Purpose: -- Save the thresholds data for a given target -- PROCEDURE save_thresholds( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, met_thr_data_in IN MGMT_METRIC_THR_OBJ_ARRAY, emd_url_out OUT VARCHAR2, is_multi_agent_target_out OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; l_has_key_column NUMBER := 0; l_tmp_value VARCHAR2(256) := ''; l_key_value VARCHAR2(256) := ''; l_num_occurences NUMBER := 1; L_NO_METRIC_GUID constant raw(16) := '0000000000000000'; l_metric_guid RAW(16) := L_NO_METRIC_GUID; l_comp_key_guid RAW(16) := L_NO_METRIC_GUID; l_processed_key_value RAW(16) := L_NO_METRIC_GUID; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid, emd_url, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, emd_url_out, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; -- verify that current user has operator target privilege on this target check_target_priv_error( l_target_guid, MGMT_USER.OPERATOR_TARGET); -- Determine if the current target is multi-agent or not: is_multi_agent_target_out := mgmt_target.is_multi_agent_target(target_name_in, target_type_in); -- -- use the following steps to save the thresholds data in met_thr_data_in -- -- first, delete metric indexes of all metrics in met_thr_data_in -- and also of metrics in mgmt metrics thresholds table and not in -- met_thr_data_in for target - l_target_guid. this removes the requirement -- that the rows in met_thr_data_in should be in a specific order. -- -- for each row in met_thr_data_in, do: -- if the metric does not have a key column, this is a simple metric. -- so update this row's values -- -- if the metric has a key column and no key value, this is a parent row -- meaning that there could be filters for this metric and thresholds on -- those filters -- update the parent row's values. -- deleting of key value rows for this parent row is not needed because -- the delete statement at the beginning of this procedure should take -- care of this. -- -- if the metric has a key column and a key value, this is a child row. -- deleting of this key value row is not needed here because the delete -- statement at the beginning of this procedure should take care of this. -- insert this row with the num_occurences and other values irrespective -- of whether this metric has composite key or not. -- if this metric has composite keys then -- if the composite key guid does not exist in mgmt metrics composite -- keys table, insert a row there with the appropriate key part values. -- if the composite key guid already exists in mgmt metrics composite -- keys table, do nothing. -- -- NOTE: it is not required that the rows in met_thr_data_in be in a -- specific order. this is because the first delete statement above -- deletes the metric indexes for the metrics in met_thr_data_in and in -- the thresholds table. -- -- delete the metric indexes of all metrics for the target - l_target_guid -- they will be added later on anyway -- the deletion is done(and needed) here so that the list of metric indexes -- in met_thr_data_in and the ones finally saved in mgmt metrics thresholds -- table are the same DELETE FROM mgmt_metric_thresholds thr WHERE thr.target_guid = l_target_guid AND thr.metric_guid IN ( SELECT thr.metric_guid FROM mgmt_metric_thresholds thr, mgmt_targets tgt, mgmt_metrics met WHERE tgt.target_guid = l_target_guid AND NOT (met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN) AND met.target_type = tgt.target_type AND thr.target_guid = tgt.target_guid AND thr.metric_guid = met.metric_guid AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') UNION SELECT DISTINCT thr_data.metric_guid FROM TABLE(CAST(met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY)) thr_data ) AND thr.coll_name IN ( SELECT DISTINCT thr.coll_name FROM mgmt_metric_thresholds thr, mgmt_targets tgt, mgmt_metrics met WHERE tgt.target_guid = l_target_guid AND NOT (met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN) AND met.target_type = tgt.target_type AND thr.target_guid = tgt.target_guid AND thr.metric_guid = met.metric_guid AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') UNION SELECT DISTINCT thr_data.coll_name FROM TABLE(CAST(met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY)) thr_data ) AND trim(thr.key_value) IS NOT NULL; -- process each row of data in met_thr_data_in FOR i in 1..met_thr_data_in.COUNT LOOP l_has_key_column := met_thr_data_in(i).has_key_column; l_tmp_value := met_thr_data_in(i).key_value; l_key_value := trim(l_tmp_value); l_metric_guid := L_NO_METRIC_GUID; IF( l_has_key_column = 0 ) THEN -- this row is for a metric without a key column. -- update the values for this row in mgmt metric thresholds table ... UPDATE mgmt_metric_thresholds thr SET thr.warning_threshold = met_thr_data_in(i).warning_threshold, thr.critical_threshold = met_thr_data_in(i).critical_threshold, thr.eval_order = met_thr_data_in(i).eval_order, thr.fixit_job = met_thr_data_in(i).fixit_job WHERE thr.target_guid = met_thr_data_in(i).target_guid AND thr.metric_guid = met_thr_data_in(i).metric_guid AND thr.coll_name = met_thr_data_in(i).coll_name; ELSIF( l_has_key_column = 1 AND l_key_value IS NULL ) THEN -- this row is for a metric with a key column and no key value -- update the values for this row in mgmt metric thresholds table ... UPDATE mgmt_metric_thresholds thr SET thr.warning_threshold = met_thr_data_in(i).warning_threshold, thr.critical_threshold = met_thr_data_in(i).critical_threshold, thr.eval_order = met_thr_data_in(i).eval_order, thr.fixit_job = met_thr_data_in(i).fixit_job WHERE thr.target_guid = met_thr_data_in(i).target_guid AND thr.metric_guid = met_thr_data_in(i).metric_guid AND thr.coll_name = met_thr_data_in(i).coll_name AND trim(thr.key_value) IS NULL; ELSIF( l_has_key_column = 1 AND l_key_value IS NOT NULL ) THEN -- this row is for a metric with a key column and a key value -- note that if this row existed before, it will have been deleted in the -- delete statement at the beginning of this procedure. so insert -- a row in thresholds table for this metric(with key value). -- insert this row with the num_occurences and other values irrespective -- of whether this metric has composite key or not ... INSERT INTO mgmt_metric_thresholds(target_guid, metric_guid, coll_name, key_value, warning_operator, warning_threshold, critical_operator, critical_threshold, num_occurences, eval_order, fixit_job) VALUES (met_thr_data_in(i).target_guid, met_thr_data_in(i).metric_guid, met_thr_data_in(i).coll_name, met_thr_data_in(i).key_value, met_thr_data_in(i).warning_operator, met_thr_data_in(i).warning_threshold, met_thr_data_in(i).critical_operator, met_thr_data_in(i).critical_threshold, met_thr_data_in(i).num_occurences, met_thr_data_in(i).eval_order, met_thr_data_in(i).fixit_job); -- if this is a composite key, update the mgmt metrics composite keys -- table also IF( met_thr_data_in(i).num_keys > 1 ) THEN -- this is a composite key -- met_thr_data_in(i).key_value will have the composite key guid as -- string. find out if this composite key for the l_target_guid exists -- also since met_thr_data_in(i).key_value will have the key value -- as string, so convert it to raw l_processed_key_value := HEXTORAW(met_thr_data_in(i).key_value); BEGIN SELECT compkeys.composite_key INTO l_comp_key_guid FROM mgmt_metrics_composite_keys compkeys WHERE compkeys.target_guid = l_target_guid AND compkeys.composite_key = l_processed_key_value; EXCEPTION WHEN NO_DATA_FOUND THEN l_comp_key_guid := L_NO_METRIC_GUID; END; IF( l_comp_key_guid = L_NO_METRIC_GUID ) THEN -- if the composite key does not exist in the mgmt metrics composite -- keys table, insert a row for this composite key along with the -- key part values INSERT INTO mgmt_metrics_composite_keys(target_guid, composite_key, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value) VALUES (l_target_guid, l_processed_key_value, met_thr_data_in(i).key_part1_value, met_thr_data_in(i).key_part2_value, met_thr_data_in(i).key_part3_value, met_thr_data_in(i).key_part4_value, met_thr_data_in(i).key_part5_value); END IF; END IF; END IF; END LOOP; END save_thresholds; -- -- Name: copy_thresholds -- -- Package: emd_mntr_baseline -- -- Purpose: -- Copy the thresholds data of the source target to the destination target -- PROCEDURE copy_thresholds( source_target_name_in IN VARCHAR2, source_target_type_in IN VARCHAR2, destination_target_name_in IN VARCHAR2, destination_target_type_in IN VARCHAR2, source_tgt_met_thr_data_in IN MGMT_METRIC_THR_OBJ_ARRAY, copy_common_only_in IN NUMBER, dest_tgt_met_thr_data_out OUT cursorType, non_applicable_metric_list OUT SMP_EMD_STRING_ARRAY, non_applicable_kvalue_list OUT SMP_EMD_STRING_ARRAY, is_multi_agent_target_out OUT NUMBER) IS l_source_target_guid mgmt_targets.target_guid%TYPE; l_destination_target_guid mgmt_targets.target_guid%TYPE; l_current_index NUMBER := 0; l_metric_name_label VARCHAR2(64) := ''; l_metric_column_label VARCHAR2(64) := ''; l_has_key_column NUMBER := 0; l_tmp_value VARCHAR2(256) := ''; l_key_value VARCHAR2(256) := ''; L_NO_METRIC_GUID constant raw(16) := '0000000000000000'; l_metric_guid mgmt_metrics.metric_guid%TYPE := L_NO_METRIC_GUID; l_warning_operator NUMBER := 0; l_critical_operator NUMBER := 0; l_num_occurences NUMBER := 6; l_comp_key_guid RAW(16) := L_NO_METRIC_GUID; l_processed_key_value RAW(16) := L_NO_METRIC_GUID; l_source_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_source_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_source_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_source_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_source_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_source_category_prop_5 mgmt_targets.category_prop_5%TYPE; l_destination_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_destination_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_destination_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_destination_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_destination_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_destination_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN -- initialize OUT parameters non_applicable_metric_list := SMP_EMD_STRING_ARRAY(); non_applicable_kvalue_list := SMP_EMD_STRING_ARRAY(); -- get the target guids of the source target and the destination target BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_source_target_guid, l_source_type_meta_ver, l_source_category_prop_1, l_source_category_prop_2, l_source_category_prop_3, l_source_category_prop_4, l_source_category_prop_5 FROM mgmt_targets WHERE target_name = source_target_name_in AND target_type = source_target_type_in; SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_destination_target_guid, l_destination_type_meta_ver, l_destination_category_prop_1, l_destination_category_prop_2, l_destination_category_prop_3, l_destination_category_prop_4, l_destination_category_prop_5 FROM mgmt_targets WHERE target_name = destination_target_name_in AND target_type = destination_target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; -- verify that current user has operator target privilege on the source target check_target_priv_error( l_source_target_guid, MGMT_USER.OPERATOR_TARGET); -- verify that current user has operator target privilege on the destination -- target check_target_priv_error( l_destination_target_guid, MGMT_USER.OPERATOR_TARGET ); is_multi_agent_target_out := mgmt_target.is_multi_agent_target(destination_target_name_in, destination_target_type_in); -- copy the metric thresholds data of the source target to the destination -- target -- -- use the following steps to copy the thresholds data if copy_common_only_in -- is 1 i.e true -- -- for each row in source_tgt_met_thr_data_in, do: -- if the metric does not have a key column, this is a simple metric -- or a metric with a key column but no key value. in either cases, -- first figure out if a corresponding metric is available for the -- destination target. if it is found, copy the thresholds and fixit job -- if it is not found, save this metric in the array to be returned back. -- -- if the metric has key value, this is a metric with a key column and -- key value. find out if a corresponding metric and key value is found -- in the destination target. if it is found, copy the thresholds and fixit -- job -- -- use the following steps to copy the thresholds data if copy_common_only_in -- is 0 i.e false -- -- first, delete metric indexes of all metrics in source_tgt_met_thr_data_in -- and also of metrics in mgmt metrics thresholds table and not in -- source_tgt_met_thr_data_in for target - l_destination_target_guid. this -- removes the requirement that the rows in source_tgt_met_thr_data_in should -- be in a specific order. -- -- for each row in source_tgt_met_thr_data_in, do: -- if the metric does not have a key column, this is a simple metric. -- find out if a corresponding metric is found for the destination target. -- if it is found, copy this metric's values to the corresponding metric -- of destination target. -- if it is not found, save this metric in the array to be returned back. -- -- if the metric has a key column and no key value, this is a parent row -- meaning that there could be filters for this metric and thresholds on -- those filters. -- find out if a corresponding metric is found for the destination target. -- if it is found, first copy this metric's values to the corresponding -- metric of the destination target. -- if it is not found, save this metric in the array to be returned back. -- deleting of key value rows for this parent row on the destination -- target is not needed because the delete statement at the beginning of -- this procedure should take care of this. -- -- if the metric has a key column and a key value, this is a child row. -- deleting of this key value row on the destination target is not needed -- here because the delete statement at the beginning of this procedure -- should take care of this. -- insert this row for the destination target irrespective of whether this -- metric has composite key or not. get the num_occurrences and other -- values from the parent row of the destination target. -- if this metric has composite keys then -- if the composite key guid does not exist in mgmt metrics composite -- keys table for the destination target, insert a row there with the -- appropriate key part values. -- if the composite key guid already exists in mgmt metrics composite -- keys table for the destination target, do nothing. -- -- NOTE: it is not required that the rows in source_tgt_met_thr_data_in be -- in a specific order. this is because the first delete statement above -- deletes the metric indexes for the metrics in source_tgt_met_thr_data_in -- and in the thresholds table. -- IF( copy_common_only_in = MGMT_GLOBAL.G_TRUE ) THEN -- only the metrics(with or without key values) common to both the source -- and destination target will be copied FOR i in 1..source_tgt_met_thr_data_in.COUNT LOOP l_tmp_value := source_tgt_met_thr_data_in(i).key_value; l_key_value := trim(l_tmp_value); l_metric_guid := L_NO_METRIC_GUID; IF( l_key_value IS NULL ) THEN -- this row is for a metric with a key column and no key value -- or for a metric with no key column -- find out if this metric is applicable to the destination target BEGIN SELECT thr.metric_guid INTO l_metric_guid FROM mgmt_metric_thresholds thr WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND thr.coll_name = source_tgt_met_thr_data_in(i).coll_name AND trim(thr.key_value) IS NULL; EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_guid := L_NO_METRIC_GUID; END; IF( l_metric_guid <> L_NO_METRIC_GUID ) THEN -- metric found, so copy the thresholds and fixit job UPDATE mgmt_metric_thresholds thr SET thr.warning_threshold = source_tgt_met_thr_data_in(i).warning_threshold, thr.critical_threshold = source_tgt_met_thr_data_in(i).critical_threshold, thr.fixit_job = source_tgt_met_thr_data_in(i).fixit_job WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND thr.coll_name = source_tgt_met_thr_data_in(i).coll_name AND trim(thr.key_value) IS NULL; ELSE -- save this metric data into the string array to be returned back. -- this array will consist of all the source target metrics -- which do not apply to the destination target and so could not -- be copied -- get the metric name and column label for this metric of the source -- target BEGIN SELECT metric_label, column_label INTO l_metric_name_label, l_metric_column_label FROM mgmt_metrics met WHERE met.target_type = source_target_type_in AND met.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND met.type_meta_ver = l_source_type_meta_ver AND (met.category_prop_1 = l_source_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_source_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_source_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_source_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_source_category_prop_5 OR met.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_name_label := ''; l_metric_column_label := ''; END; non_applicable_metric_list.extend(1); non_applicable_kvalue_list.extend(1); l_current_index := l_current_index + 1; non_applicable_metric_list(l_current_index) := l_metric_column_label; non_applicable_kvalue_list(l_current_index) := ' '; END IF; ELSIF( l_key_value IS NOT NULL ) THEN -- this row is for a metric with a key column and a key value -- find out if this metric is applicable to the destination target BEGIN SELECT thr.metric_guid INTO l_metric_guid FROM mgmt_metric_thresholds thr WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND thr.coll_name = source_tgt_met_thr_data_in(i).coll_name AND thr.key_value = source_tgt_met_thr_data_in(i).key_value; EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_guid := L_NO_METRIC_GUID; END; IF( l_metric_guid <> L_NO_METRIC_GUID ) THEN -- metric found, so copy the thresholds and fixit job UPDATE mgmt_metric_thresholds thr SET thr.warning_threshold = source_tgt_met_thr_data_in(i).warning_threshold, thr.critical_threshold = source_tgt_met_thr_data_in(i).critical_threshold, thr.fixit_job = source_tgt_met_thr_data_in(i).fixit_job WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND thr.coll_name = source_tgt_met_thr_data_in(i).coll_name AND thr.key_value = source_tgt_met_thr_data_in(i).key_value; END IF; -- NOTE: if the metric is not found, do not return this metric because -- the user has already chosen to copy only the common metrics. -- Only the metrics without key column or metrics with key -- column and no key key value that are not found on the -- destination should be returned back ... END IF; END LOOP; ELSE -- a "clone" of the source target metrics will be performed on the -- destination target metrics. -- delete the metric indexes of all metrics for the target - -- l_destination_target_guid. they will be added later on anyway. -- the deletion is done(and needed) here so that the list of metric indexes -- in source_tgt_met_thr_data_in and the ones finally saved in mgmt -- metrics thresholds table for target - l_destination_target_guid are the -- same DELETE FROM mgmt_metric_thresholds thr WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid IN ( SELECT thr.metric_guid FROM mgmt_metric_thresholds thr, mgmt_targets tgt, mgmt_metrics met WHERE tgt.target_guid = l_destination_target_guid AND NOT (met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN) AND met.target_type = tgt.target_type AND thr.target_guid = tgt.target_guid AND thr.metric_guid = met.metric_guid AND met.type_meta_ver = l_destination_type_meta_ver AND (met.category_prop_1 = l_destination_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_destination_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_destination_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_destination_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_destination_category_prop_5 OR met.category_prop_5 = ' ') UNION SELECT DISTINCT thr_data.metric_guid FROM TABLE(CAST(source_tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY)) thr_data ) AND thr.coll_name IN ( SELECT DISTINCT thr.coll_name FROM mgmt_metric_thresholds thr, mgmt_targets tgt, mgmt_metrics met WHERE tgt.target_guid = l_destination_target_guid AND NOT (met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN) AND met.target_type = tgt.target_type AND thr.target_guid = tgt.target_guid AND thr.metric_guid = met.metric_guid AND met.type_meta_ver = l_destination_type_meta_ver AND (met.category_prop_1 = l_destination_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_destination_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_destination_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_destination_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_destination_category_prop_5 OR met.category_prop_5 = ' ') UNION SELECT DISTINCT thr_data.coll_name FROM TABLE(CAST(source_tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY)) thr_data ) AND trim(thr.key_value) IS NOT NULL; -- process each row of data in source_tgt_met_thr_data_in FOR i in 1..source_tgt_met_thr_data_in.COUNT LOOP l_has_key_column := source_tgt_met_thr_data_in(i).has_key_column; l_tmp_value := source_tgt_met_thr_data_in(i).key_value; l_key_value := trim(l_tmp_value); l_metric_guid := L_NO_METRIC_GUID; IF( l_key_value IS NULL ) THEN -- this row is for a metric without a key column or a metric with a -- key column and a key value -- find out if there is a corresponding metric on the destination target BEGIN SELECT thr.metric_guid INTO l_metric_guid FROM mgmt_metric_thresholds thr WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND thr.coll_name = source_tgt_met_thr_data_in(i).coll_name AND trim(thr.key_value) IS NULL; EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_guid := L_NO_METRIC_GUID; END; IF( l_metric_guid <> L_NO_METRIC_GUID ) THEN -- metric found, so copy the thresholds and fixit job UPDATE mgmt_metric_thresholds thr SET thr.warning_threshold = source_tgt_met_thr_data_in(i).warning_threshold, thr.critical_threshold = source_tgt_met_thr_data_in(i).critical_threshold, thr.eval_order = source_tgt_met_thr_data_in(i).eval_order, thr.fixit_job = source_tgt_met_thr_data_in(i).fixit_job WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND thr.coll_name = source_tgt_met_thr_data_in(i).coll_name AND trim(thr.key_value) IS NULL; ELSE -- metric not found -- save this metric data into the string array to be returned back -- this array will consist of all the source target metrics -- which do not apply to the destination target and so could not -- be copied -- get the metric name and column label for this metric of the source -- target BEGIN SELECT metric_label, column_label INTO l_metric_name_label, l_metric_column_label FROM mgmt_metrics met WHERE met.target_type = source_target_type_in AND met.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND met.type_meta_ver = l_source_type_meta_ver AND (met.category_prop_1 = l_source_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_source_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_source_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_source_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_source_category_prop_5 OR met.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_name_label := ''; l_metric_column_label := ''; END; non_applicable_metric_list.extend(1); non_applicable_kvalue_list.extend(1); l_current_index := l_current_index + 1; non_applicable_metric_list(l_current_index) := l_metric_column_label; non_applicable_kvalue_list(l_current_index) := ' '; END IF; ELSIF( l_has_key_column = 1 AND l_key_value IS NOT NULL ) THEN -- this row is for a metric with a key column and a key value -- note that if this row existed before, it will have been deleted in -- the delete statement at the beginning of this procedure. so insert -- a row in thresholds table for this metric(with key value) of the -- destination target. -- insert this row with the num_occurences and other values from the -- parent row of the destination target irrespective of whether this -- metric has composite key or not ... BEGIN SELECT thr.warning_operator, thr.critical_operator, thr.num_occurences INTO l_warning_operator, l_critical_operator, l_num_occurences FROM mgmt_metric_thresholds thr WHERE thr.target_guid = l_destination_target_guid AND thr.metric_guid = source_tgt_met_thr_data_in(i).metric_guid AND thr.coll_name = source_tgt_met_thr_data_in(i).coll_name AND trim(thr.key_value) IS NULL; EXCEPTION WHEN NO_DATA_FOUND THEN -- if an exception occurred, use the values of the source -- target l_warning_operator := source_tgt_met_thr_data_in(i).warning_operator; l_critical_operator := source_tgt_met_thr_data_in(i).critical_operator; l_num_occurences := source_tgt_met_thr_data_in(i).num_occurences; END; INSERT INTO mgmt_metric_thresholds(target_guid, metric_guid, coll_name, key_value, warning_operator, warning_threshold, critical_operator, critical_threshold, num_occurences, eval_order, fixit_job) VALUES (l_destination_target_guid, source_tgt_met_thr_data_in(i).metric_guid, source_tgt_met_thr_data_in(i).coll_name, source_tgt_met_thr_data_in(i).key_value, l_warning_operator, source_tgt_met_thr_data_in(i).warning_threshold, l_critical_operator, source_tgt_met_thr_data_in(i).critical_threshold, l_num_occurences, source_tgt_met_thr_data_in(i).eval_order, source_tgt_met_thr_data_in(i).fixit_job); -- if this is a composite key, update the mgmt metrics composite keys -- table also IF( source_tgt_met_thr_data_in(i).num_keys > 1 ) THEN -- this is a composite key -- source_tgt_met_thr_data_in(i).key_value will have the composite -- key guid as string. find out if this composite key for the -- l_destination_target_guid exists. also since -- source_tgt_met_thr_data_in(i).key_value will have the key value -- as string, so convert it to raw l_processed_key_value := HEXTORAW(source_tgt_met_thr_data_in(i).key_value); BEGIN SELECT compkeys.composite_key INTO l_comp_key_guid FROM mgmt_metrics_composite_keys compkeys WHERE compkeys.target_guid = l_destination_target_guid AND compkeys.composite_key = l_processed_key_value; EXCEPTION WHEN NO_DATA_FOUND THEN l_comp_key_guid := L_NO_METRIC_GUID; END; IF( l_comp_key_guid = L_NO_METRIC_GUID ) THEN -- if the composite key does not exist in the mgmt metrics composite -- keys table, insert a row for this composite key along with the -- key part values INSERT INTO mgmt_metrics_composite_keys(target_guid, composite_key, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value) VALUES (l_destination_target_guid, l_processed_key_value, source_tgt_met_thr_data_in(i).key_part1_value, source_tgt_met_thr_data_in(i).key_part2_value, source_tgt_met_thr_data_in(i).key_part3_value, source_tgt_met_thr_data_in(i).key_part4_value, source_tgt_met_thr_data_in(i).key_part5_value); END IF; END IF; END IF; END LOOP; END IF; -- get the cursor for the thresholds data of the destination target OPEN dest_tgt_met_thr_data_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_label, met.column_label, met.key_column, met.metric_type, thr.coll_name, thr.key_value, thr.eval_order, thr.warning_threshold, thr.critical_threshold, thr.warning_operator, thr.critical_operator, thr.num_occurences, thr.fixit_job, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, thr.target_guid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_destination_target_guid AND met.target_type=destination_target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_destination_type_meta_ver AND (met.category_prop_1 = l_destination_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_destination_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_destination_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_destination_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_destination_category_prop_5 OR met.category_prop_5 = ' ') ORDER BY met.column_label, thr.eval_order, thr.key_value; END copy_thresholds; FUNCTION get_additions_count(source_target_name_in IN VARCHAR2, destination_target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, copy_mode_in IN VARCHAR2, tgt_met_thr_data_in IN MGMT_METRIC_THR_OBJ_ARRAY) RETURN NUMBER IS l_additions_count NUMBER := 0; BEGIN IF(copy_mode_in = COPY_TO_MODE) THEN -- get the metrics with key values that have thresholds set only on the -- source target SELECT COUNT(*) INTO l_additions_count FROM( SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=source_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=destination_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')); ELSIF(copy_mode_in = COPY_FROM_MODE) THEN SELECT COUNT(*) INTO l_additions_count FROM( SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=source_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=destination_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')); END IF; RETURN l_additions_count; END get_additions_count; FUNCTION get_deletions_count(source_target_name_in IN VARCHAR2, destination_target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, copy_mode_in IN VARCHAR2, tgt_met_thr_data_in IN MGMT_METRIC_THR_OBJ_ARRAY) RETURN NUMBER IS l_deletions_count NUMBER := 0; BEGIN IF(copy_mode_in = COPY_TO_MODE) THEN -- get the metrics with key values that have thresholds set only on the -- destination target SELECT COUNT(*) INTO l_deletions_count FROM( SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=destination_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=source_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')); ELSIF(copy_mode_in = COPY_FROM_MODE) THEN SELECT COUNT(*) INTO l_deletions_count FROM( SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=destination_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ thr.key_value, met.metric_guid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys, mgmt_targets tgt WHERE tgt.target_name=source_target_name_in AND tgt.target_type=target_type_in AND thr.target_guid=tgt.target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND met.metric_name <> 'UDM' AND met.metric_type <> 5 AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')); END IF; RETURN l_deletions_count; END get_deletions_count; PROCEDURE get_metric_indices_changes(source_target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, dest_target_data_in IN SMP_EMD_STRING_ARRAY, tgt_met_thr_data_in IN MGMT_METRIC_THR_OBJ_ARRAY, copy_mode_in IN VARCHAR2, data_cursor_out OUT cursorType) IS BEGIN OPEN data_cursor_out FOR SELECT dest.column_value as target_name, emd_mntr_baseline.get_additions_count(source_target_name_in, dest.column_value, target_type_in, copy_mode_in, tgt_met_thr_data_in) as additions_count, emd_mntr_baseline.get_deletions_count(source_target_name_in, dest.column_value, target_type_in, copy_mode_in, tgt_met_thr_data_in) as deletions_count FROM TABLE(CAST(dest_target_data_in AS SMP_EMD_STRING_ARRAY)) dest; END get_metric_indices_changes; PROCEDURE get_number_of_pending_changes(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, pending_changes_count OUT NUMBER) IS l_pending_changes_from_count NUMBER := 0; l_pending_changes_to_count NUMBER := 0; l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- get the target guid BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; SELECT COUNT(*) INTO l_pending_changes_from_count FROM (SELECT DISTINCT op.job_id FROM mgmt_update_operations op, mgmt_update_operations_data dat, mgmt_update_operations_details det WHERE op.operation_guid= dat.operation_guid AND dat.data_set_type=mgmt_target_update.TARGET_OP_THRESHOLDS AND dat.source_target_guid=l_target_guid AND dat.operation_guid=det.operation_guid AND dat.source_target_guid<>det.destination_target_guid AND det.execution_status IN (1,2,6)); SELECT COUNT(*) INTO l_pending_changes_to_count FROM (SELECT DISTINCT op.job_id FROM mgmt_update_operations op, mgmt_update_operations_data dat, mgmt_update_operations_details det WHERE op.operation_guid=dat.operation_guid AND dat.data_set_type=mgmt_target_update.TARGET_OP_THRESHOLDS AND dat.operation_guid=det.operation_guid AND det.destination_target_guid=l_target_guid AND det.execution_status IN (1,2,6)); pending_changes_count := l_pending_changes_from_count + l_pending_changes_to_count; END get_number_of_pending_changes; PROCEDURE get_pending_jobs_from_target(source_target_name_in IN VARCHAR2, source_target_type_in IN VARCHAR2, data_cursor_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_name = source_target_name_in AND target_type = source_target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; OPEN data_cursor_out FOR SELECT tgt.target_name, op.job_owner, op.submission_timestamp, op.operation_guid, emd.target_name agent_name, det.execution_status FROM mgmt_targets tgt, mgmt_targets emd, mgmt_update_operations op, mgmt_update_operations_details det, mgmt_update_operations_data dat WHERE dat.source_target_guid=l_target_guid AND dat.data_set_type=mgmt_target_update.TARGET_OP_THRESHOLDS AND dat.operation_guid=op.operation_guid AND dat.operation_guid=det.operation_guid AND det.destination_target_guid<>dat.source_target_guid AND det.destination_target_guid=tgt.target_guid AND det.agent_guid=emd.target_guid AND det.execution_status IN (1,2,6); END get_pending_jobs_from_target; PROCEDURE get_pending_jobs_to_target(destination_target_name_in IN VARCHAR2, destination_target_type_in IN VARCHAR2, data_cursor_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_name = destination_target_name_in AND target_type = destination_target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; OPEN data_cursor_out FOR SELECT tgt.target_name, op.job_owner, op.submission_timestamp, op.operation_guid, emd.target_name agent_name, det.execution_status FROM mgmt_targets tgt, mgmt_targets emd, mgmt_update_operations op, mgmt_update_operations_details det, mgmt_update_operations_data dat WHERE det.destination_target_guid=l_target_guid AND det.operation_guid=dat.operation_guid AND dat.data_set_type=mgmt_target_update.TARGET_OP_THRESHOLDS AND dat.source_target_guid=tgt.target_guid AND det.agent_guid=emd.target_guid AND det.operation_guid=op.operation_guid AND det.execution_status IN (1,2,6); END get_pending_jobs_to_target; PROCEDURE get_past_changes(current_target_name_in IN VARCHAR2, current_target_type_in IN VARCHAR2, data_cursor_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_name = current_target_name_in AND target_type = current_target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; OPEN data_cursor_out FOR SELECT tgt.target_name, op.job_owner, op.submission_timestamp, op.operation_guid, emd.target_name agent_name FROM mgmt_targets tgt, mgmt_targets emd, mgmt_update_operations op, mgmt_update_operations_details det, mgmt_update_operations_data dat WHERE det.destination_target_guid=l_target_guid AND det.operation_guid=dat.operation_guid AND dat.data_set_type=mgmt_target_update.TARGET_OP_THRESHOLDS AND dat.source_target_guid=tgt.target_guid AND det.agent_guid=emd.target_guid AND det.operation_guid=op.operation_guid AND det.execution_status IN (3,4,5); END get_past_changes; PROCEDURE get_threshold_update_data(operation_guid_in IN VARCHAR2, target_type_in IN VARCHAR2, data_cursor_out OUT cursorType) IS BEGIN OPEN data_cursor_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_label, met.column_label, met.key_column, met.metric_type, dat.coll_name, dat.key_value, dat.eval_order, dat.warning_threshold, dat.critical_threshold, dat.warning_operator, dat.critical_operator, dat.fixit_job, dat.key_part1_value, dat.key_part2_value, dat.key_part3_value, dat.key_part4_value, dat.key_part5_value, met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid, met.target_type FROM mgmt_metrics met, mgmt_update_thresholds_data dat, mgmt_update_operations_data op WHERE op.operation_guid=operation_guid_in AND op.data_set_guid=dat.data_set_guid AND op.data_set_type=mgmt_target_update.TARGET_OP_THRESHOLDS AND met.target_type = target_type_in AND met.metric_guid = dat.metric_guid ORDER BY met.column_label, dat.eval_order, dat.key_value; END get_threshold_update_data; PROCEDURE delete_past_changes(operation_guids_in IN SMP_EMD_STRING_ARRAY, agents_in IN SMP_EMD_STRING_ARRAY) IS l_data_set_guid mgmt_update_operations_data.data_set_guid%TYPE; BEGIN FOR i IN 1..operation_guids_in.count LOOP BEGIN SELECT dat.data_set_guid INTO l_data_set_guid FROM mgmt_targets tgt, mgmt_update_operations_data dat, mgmt_update_operations_details det WHERE dat.operation_guid=operation_guids_in(i) AND dat.operation_guid=det.operation_guid AND det.agent_guid=tgt.target_guid AND tgt.target_name=agents_in(i); --DELETE -- FROM mgmt_update_operations_details -- WHERE operation_guid=operation_guids_in(i) -- AND emd_url=agents_in(i); -- DELETE -- FROM mgmt_update_thresholds_data -- WHERE data_set_guid=l_data_set_guid; END; END LOOP; DELETE FROM mgmt_update_operations_data WHERE data_set_guid=l_data_set_guid; END delete_past_changes; PROCEDURE is_valid_target(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, result_out OUT NUMBER) IS l_target_count NUMBER := 1; l_target_guid mgmt_targets.target_guid%TYPE := NULL; l_user_name VARCHAR2(64) := ''; l_has_priv_val NUMBER(1) := MGMT_GLOBAL.G_FALSE; BEGIN BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_target_count := 0; END; IF (l_target_count > 0) THEN -- get the current user name l_user_name := mgmt_user.get_current_em_user(); -- check for the privilege l_has_priv_val := mgmt_user.has_priv(l_user_name, MGMT_USER.OPERATOR_TARGET, l_target_guid); IF (l_has_priv_val = MGMT_GLOBAL.G_TRUE) THEN result_out := 0; ELSIF (l_has_priv_val = MGMT_GLOBAL.G_FALSE) THEN result_out := 1; END IF; ELSIF (l_target_count = 0) THEN result_out := 2; END IF; END is_valid_target; -- -- Name: get_src_dest_tgt_diff_met -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get the diff metrics(with key values) between the source and the -- destination target -- -- PROCEDURE get_src_dest_tgt_diff_met( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, selected_target_name_in IN VARCHAR2, selected_target_type_in IN VARCHAR2, copy_mode_in IN VARCHAR2, tgt_met_thr_data_in IN MGMT_METRIC_THR_OBJ_ARRAY, source_tgt_only_met_out OUT cursorType, dest_tgt_only_met_out OUT cursorType, common_met_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; l_selected_target_guid mgmt_targets.target_guid%TYPE; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_category_prop_5 mgmt_targets.category_prop_5%TYPE; l_selected_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_selected_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_selected_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_selected_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_selected_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_selected_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN -- get the target guid and category property columns BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_selected_target_guid, l_selected_type_meta_ver, l_selected_category_prop_1, l_selected_category_prop_2, l_selected_category_prop_3, l_selected_category_prop_4, l_selected_category_prop_5 FROM mgmt_targets WHERE target_name = selected_target_name_in AND target_type = selected_target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; IF( copy_mode_in = COPY_TO_MODE ) THEN -- source target is target_name_in and destination target is -- selected_target_name_in -- get the metrics with key values that have thresholds set only on the -- source target OPEN source_tgt_only_met_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, NVL(compkeys.key_part1_value, thr.key_part1_value) "key_part1_value", NVL(compkeys.key_part2_value, thr.key_part2_value) "key_part2_value", NVL(compkeys.key_part3_value, thr.key_part3_value) "key_part3_value", NVL(compkeys.key_part4_value, thr.key_part4_value) "key_part4_value", NVL(compkeys.key_part5_value, thr.key_part5_value) "key_part5_value", met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_selected_target_guid AND met.target_type=selected_target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_selected_type_meta_ver AND (met.category_prop_1 = l_selected_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_selected_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_selected_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_selected_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_selected_category_prop_5 OR met.category_prop_5 = ' '); -- get the metrics with key values that have thresholds set only on the -- destination target OPEN dest_tgt_only_met_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_selected_target_guid AND met.target_type=selected_target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_selected_type_meta_ver AND (met.category_prop_1 = l_selected_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_selected_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_selected_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_selected_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_selected_category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, NVL(compkeys.key_part1_value, thr.key_part1_value) "key_part1_value", NVL(compkeys.key_part2_value, thr.key_part2_value) "key_part2_value", NVL(compkeys.key_part3_value, thr.key_part3_value) "key_part3_value", NVL(compkeys.key_part4_value, thr.key_part4_value) "key_part4_value", NVL(compkeys.key_part5_value, thr.key_part5_value) "key_part5_value", met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' '); ELSE -- source target is selected_target_name_in and destination target is -- target_name_in -- get the metrics with key values that have thresholds set only on the -- source target OPEN source_tgt_only_met_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_selected_target_guid AND met.target_type=selected_target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_selected_type_meta_ver AND (met.category_prop_1 = l_selected_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_selected_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_selected_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_selected_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_selected_category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, NVL(compkeys.key_part1_value, thr.key_part1_value) "key_part1_value", NVL(compkeys.key_part2_value, thr.key_part2_value) "key_part2_value", NVL(compkeys.key_part3_value, thr.key_part3_value) "key_part3_value", NVL(compkeys.key_part4_value, thr.key_part4_value) "key_part4_value", NVL(compkeys.key_part5_value, thr.key_part5_value) "key_part5_value", met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' '); -- get the metrics with key values that have thresholds set only on the -- destination target OPEN dest_tgt_only_met_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, NVL(compkeys.key_part1_value, thr.key_part1_value) "key_part1_value", NVL(compkeys.key_part2_value, thr.key_part2_value) "key_part2_value", NVL(compkeys.key_part3_value, thr.key_part3_value) "key_part3_value", NVL(compkeys.key_part4_value, thr.key_part4_value) "key_part4_value", NVL(compkeys.key_part5_value, thr.key_part5_value) "key_part5_value", met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') MINUS SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_selected_target_guid AND met.target_type=selected_target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND trim(thr.key_value) IS NOT NULL AND met.type_meta_ver = l_selected_type_meta_ver AND (met.category_prop_1 = l_selected_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_selected_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_selected_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_selected_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_selected_category_prop_5 OR met.category_prop_5 = ' '); END IF; -- irrespective of the copy mode, now get the list of metrics with -- thresholds that are common to both the source and destination target OPEN common_met_out FOR SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, NVL(compkeys.key_part1_value, thr.key_part1_value) "key_part1_value", NVL(compkeys.key_part2_value, thr.key_part2_value) "key_part2_value", NVL(compkeys.key_part3_value, thr.key_part3_value) "key_part3_value", NVL(compkeys.key_part4_value, thr.key_part4_value) "key_part4_value", NVL(compkeys.key_part5_value, thr.key_part5_value) "key_part5_value", met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, TABLE(CAST(tgt_met_thr_data_in AS MGMT_METRIC_THR_OBJ_ARRAY) ) thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_target_guid AND met.target_type=target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') INTERSECT SELECT /* INDEX(met) INDEX(thr) */ met.metric_name, met.metric_column, met.metric_type, met.metric_label, met.column_label, thr.key_value, compkeys.composite_key, compkeys.key_part1_value, compkeys.key_part2_value, compkeys.key_part3_value, compkeys.key_part4_value, compkeys.key_part5_value, met.num_keys, met.metric_guid, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, mgmt_metric_thresholds thr, mgmt_metrics_composite_keys compkeys WHERE thr.target_guid=l_selected_target_guid AND met.target_type=selected_target_type_in AND met.metric_guid=thr.metric_guid AND thr.target_guid=compkeys.target_guid(+) AND thr.key_value=compkeys.composite_key(+) AND met.type_meta_ver = l_selected_type_meta_ver AND (met.category_prop_1 = l_selected_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_selected_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_selected_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_selected_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_selected_category_prop_5 OR met.category_prop_5 = ' '); END get_src_dest_tgt_diff_met; -- -- Name: get_targets_of_type -- -- Package: emd_mntr_baseline -- -- Purpose: -- Get all the targets of the type -- -- FUNCTION get_targets_of_type( target_type_in IN VARCHAR2 ) RETURN cursorType IS targets_cursor_out cursorType; BEGIN OPEN targets_cursor_out FOR SELECT target_name, target_type, target_guid FROM mgmt_targets WHERE target_type = target_type_in; RETURN targets_cursor_out; END get_targets_of_type; end emd_mntr_baseline; / show errors;