Rem Rem $Header: gensvc_tgt_pkgbody.sql 01-aug-2006.11:56:12 eporter Exp $ Rem Rem gensvc_pkgbody.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem gensvc_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem eporter 07/26/06 - Bug 5404472 Rem eporter 08/01/06 - Backport eporter_bug-5404472 from main Rem eporter 03/02/06 - add Business Metrics Rem eporter 06/29/06 - Bug 5198753: Add table join Rem eporter 07/11/06 - Backport eporter_bug-5198753 from main Rem rmarripa 09/08/05 - remove trace statements Rem andyao 08/17/05 - add definition to populate default test Rem properties Rem andyao 07/26/05 - use P_USE_DEF_TEST_TYPE_THRESH Rem andyao 07/21/05 - add is_renderable Rem andyao 07/21/05 - add operation guid to remove job Rem mfidanbo 07/18/05 - key value cleanup for promoted metrics Rem rmarripa 07/12/05 - add beacon type meta ver update callback Rem snakai 07/08/05 - add priv check to tmpl functions Rem mfidanbo 07/14/05 - set_promo_cli fix Rem andyao 07/01/05 - use collections table for showing promote Rem metrics Rem andyao 06/21/05 - add check before call remove_system_based_avail Rem or remove_test_based_avail Rem mfidanbo 05/25/05 - use deregister avail in pre_delete callback Rem andyao 05/13/05 - fix bug 4336176 Rem mfidanbo 05/06/05 - promotion cli support Rem mvajapey 04/27/05 - Check for empty array in SET_MNTR_LOOP. Rem rmarripa 04/26/05 - Rem andyao 04/22/05 - fix the operator not saving problem Rem mfidanbo 04/21/05 - fix collection removal bug Rem andyao 04/13/05 - fix bug 4294805 Rem mfidanbo 04/12/05 - promotion: cutover to using coll_name Rem mvajapey 04/05/05 - Remove SET_MNTR_STATES_FOR_TGT, add nullity Rem check. Rem rmarripa 04/07/05 - remove dbms_output Rem andyao 03/17/05 - add additional parameters to the service Rem creation Rem mfidanbo 04/04/05 - add post gensvc deletion script Rem mvajapey 02/28/05 - Add function to set template test monitoring Rem settings. Rem andyao 02/24/05 - fix bug 4192808 Rem andyao 02/15/05 - fix bug 4126754 Rem andyao 02/07/05 - fix bug 4168678 Rem ramalhot 02/02/05 - changed signature for delete_service_callback Rem mfidanbo 01/17/05 - add deleting the queue for delete target Rem callback Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid Rem andyao 01/06/05 - remove dbms output comment Rem andyao 01/04/05 - remove TEST/SYSTEM constant Rem andyao 12/29/04 - output availability state to system and Rem tests/bcns page Rem mfidanbo 12/16/04 - add callback for service deletion Rem mfidanbo 12/06/04 - user model changes Rem andyao 12/05/04 - add set local beacon method Rem rmarripa 12/05/04 - change add_repo_collection Rem andyao 12/01/04 - add getting system/tests/bcns for aggregate Rem metrics Rem rmarripa 11/30/04 - add save thresholds Rem rmarripa 11/24/04 - change remove test based avail Rem andyao 11/17/04 - modify set_svc_agg_thresholds interface to take Rem 6 values for default chart Rem andyao 11/08/04 - add RCA Configuration Rem andyao 11/05/04 - add check for no system case Rem andyao 10/25/04 - add method to save multiple transactions with Rem one PL/SQL Rem andyao 10/19/04 - add PL/SQL for availability page Rem andyao 10/06/04 - andyao_services2 Rem rmarripa 09/22/04 - fix remove thresholds Rem rmarripa 09/21/04 - add PL/SQL for system metric promotion Rem rmarripa 09/21/04 - add_set_system_and_props Rem rmarripa 09/17/04 - fix edit general page Rem rmarripa 09/15/04 - use metric thresholds view Rem rmarripa 09/14/04 - more sql Rem rmarripa 09/13/04 - Created Rem CREATE OR REPLACE PACKAGE BODY gensvc IS ---------------------PRIVATE PROCEDURES/FUNCTIONS -------------------------------- PROCEDURE LOG_BCN_TXN_KEY( p_key IN MGMT_BCN_THRESHOLD_KEY ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN IF ( p_key IS NULL ) THEN EMDW_LOG.INFO('KEY is NULL ', G_MODULE_NAME); ELSE EMDW_LOG.INFO('BCN_GUID '||p_key.BCN_GUID||', TXN_GUID '||p_key.TXN_GUID|| 'KEY_PART_3 '||p_key.KEY_PART_3||', '|| 'KEY_PART_4 '||p_key.KEY_PART_4 || 'KEY_PART_5 '||p_key.KEY_PART_5||', METRIC_NAME '||p_key.METRIC_NAME|| ', METRIC_COLUMN '||p_key.METRIC_COLUMN , G_MODULE_NAME) ; END IF ; END IF ; END; -------------------------------------------------------------------------- -- PURPOSE: Add or Edits metric dependencies and starts repository collection -- PROCEDURE: ADD_EDIT_METRIC_DEPS -- IN PARAMETERS -- p_source_metric: Source metric -- p_dep_metric_list: Dependency metrics -- p_eval_func : Evaluation function (Aggregate function) -- p_is_add : is add flag. True for add and FALSE for modify PROCEDURE ADD_EDIT_METRIC_DEPS( p_source_metric IN MGMT_METRIC_INSTANCE, p_dep_metric_list IN MGMT_METRIC_INSTANCE_ARRAY, p_eval_func VARCHAR2, p_is_add BOOLEAN, p_is_test_based_metric BOOLEAN) IS l_dep_met MGMT_METRIC_INSTANCE:=NULL; l_coll_names SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_txn_guid MGMT_BCN_TXN_DEFN.TXN_GUID%TYPE; l_bcn_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_min_interval NUMBER; l_min_interval_bcns NUMBER; l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_coll_name MGMT_COLLECTIONS.COLL_NAME%TYPE; l_bcn_guids GUID_ARRAY := GUID_ARRAY(); l_count PLS_INTEGER; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_edit_metric_deps: Enter', G_MODULE_NAME) ; END IF ; IF ( p_source_metric IS NULL ) OR ( p_dep_metric_list IS NULL ) OR ( p_dep_metric_list.COUNT <= 0 ) OR ( p_eval_func IS NULL ) THEN RETURN; END IF; l_count := 1; -- get the collection names for p_dep_metric_list IF(p_is_test_based_metric = TRUE) THEN FOR i IN p_dep_metric_list.FIRST .. p_dep_metric_list.LAST LOOP l_dep_met := p_dep_metric_list(i); l_coll_names.EXTEND; IF ( l_dep_met IS NOT NULL ) THEN SELECT txn.txn_guid, bcn.target_guid, svc.target_guid INTO l_txn_guid, l_bcn_guid, l_target_guid FROM MGMT_BCN_TXN_DEFN txn, MGMT_TARGETS bcn, MGMT_METRICS_COMPOSITE_KEYS keys, MGMT_TARGETS svc WHERE l_dep_met.key_value = keys.composite_key AND keys.key_part1_value = txn.name AND keys.key_part2_value = bcn.target_name AND bcn.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE AND svc.target_name = l_dep_met.target_name AND svc.target_type = l_dep_met.target_type AND txn.target_guid = svc.target_guid AND keys.target_guid = svc.target_guid AND rownum = 1; --should not need this l_coll_name := (RAWTOHEX(l_txn_guid) || RAWTOHEX(l_bcn_guid)); l_bcn_guids.EXTEND(); l_bcn_guids(l_count) := l_bcn_guid; l_count := l_count + 1; l_coll_names(i) := l_coll_name; END IF; END LOOP; SELECT num_value INTO l_min_interval FROM MGMT_BCN_TXN_PROPS WHERE target_guid = l_target_guid AND txn_guid = l_txn_guid AND name = 'Collection Interval'; -- now find the min among all bcns IF(l_bcn_guids IS NOT NULL) THEN BEGIN SELECT min(num_value) INTO l_min_interval_bcns FROM MGMT_BCN_BCNTXN_PROPS WHERE target_guid = l_target_guid AND txn_guid = l_txn_guid AND name = 'Collection Interval' AND bcn_guid IN (SELECT * FROM TABLE(CAST(l_bcn_guids AS GUID_ARRAY))) GROUP BY target_guid, txn_guid, name; IF(l_min_interval_bcns < l_min_interval) THEN l_min_interval := l_min_interval_bcns; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; --dont do anything l_min_interval stays the same END; l_bcn_guids.DELETE; END IF; END IF; --Following is the logging code for debugging IF EMDW_LOG.P_IS_INFO_SET THEN IF ( p_is_add = TRUE ) THEN EMDW_LOG.INFO('Adding a new metric', G_MODULE_NAME) ; ELSE EMDW_LOG.INFO('Modifying an existing metric', G_MODULE_NAME) ; END IF; EMDW_LOG.INFO('Dependency metric list is null', G_MODULE_NAME); EMDW_LOG.INFO('Source Metric '||p_source_metric.target_name ||', '|| p_source_metric.target_type||', '||p_source_metric.metric_name ||', '|| p_source_metric.metric_column|| ', '||p_source_metric.key_value, G_MODULE_NAME); IF ( p_dep_metric_list IS NOT NULL ) THEN FOR i IN p_dep_metric_list.FIRST .. p_dep_metric_list.LAST LOOP l_dep_met := p_dep_metric_list(i); IF(p_is_test_based_metric = TRUE) THEN l_coll_name := l_coll_names(i); END IF; IF ( l_dep_met IS NOT NULL ) THEN EMDW_LOG.INFO('Dependency Detail '||l_dep_met.target_name ||', '|| l_dep_met.target_type||', '||l_dep_met.metric_name ||', '|| l_dep_met.metric_column|| ', '||l_dep_met.key_value || ', COLL_NAME= ' || l_coll_name ||' Interval: ' || l_min_interval, G_MODULE_NAME); ELSE EMDW_LOG.INFO('Dependency metric instance at index '||i||' is null', G_MODULE_NAME); END IF; END LOOP; ELSE EMDW_LOG.INFO('Dependency metric list is null', G_MODULE_NAME); END IF; END IF; --END the logging code for debugging IF (p_is_test_based_metric = TRUE) THEN IF(p_is_add = TRUE) THEN em_rep_metric.add_repo_metric_collection( p_source_metric, p_eval_func, p_dep_metric_list, l_min_interval, l_coll_names, null); ELSE em_rep_metric.modify_repo_metric_collection( p_source_metric, p_eval_func, p_dep_metric_list, l_min_interval, l_coll_names, null); END IF; ELSE IF(p_is_add = TRUE) THEN mgmt_target.add_repo_metric_collection( p_source_metric, p_eval_func, p_dep_metric_list); ELSE mgmt_target.modify_repo_metric_collection( p_source_metric, p_eval_func, p_dep_metric_list); END IF; END IF; IF(l_coll_names IS NOT NULL) THEN l_coll_names.DELETE; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_edit_metric_deps: Exit', G_MODULE_NAME) ; END IF ; END ADD_EDIT_METRIC_DEPS; PROCEDURE SET_THRESHOLDS( p_service_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_agg_met_thresh IN MGMT_SVC_AGG_MET_THRESH_INST ) IS l_policy_key_val_list MGMT_POLICY_KEY_VAL_ARRAY := NULL; l_source_metric MGMT_METRIC_INSTANCE := NULL; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_thresholds: Enter', G_MODULE_NAME) ; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_thresholds: Exit', G_MODULE_NAME) ; END IF; IF ( p_service_name IS NULL ) OR ( p_service_target_type IS NULL ) OR (p_agg_met_thresh IS NULL ) THEN RETURN; END IF; l_source_metric := p_agg_met_thresh.source_metric; l_policy_key_val_list := MGMT_POLICY_KEY_VAL_ARRAY(); l_policy_key_val_list.extend; l_policy_key_val_list(1) := MGMT_POLICY_KEY_VAL.NEW( p_key_value => MGMT_POLICY_KEY_COL_COND_ARRAY( MGMT_POLICY_KEY_COL_COND.NEW( p_key_value => l_source_metric.key_value, p_has_wildcard => MGMT_GLOBAL.G_FALSE --p_key_column_name is not required for single key metrics ) ), p_param_values => MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => ' ', p_crit_threshold => p_agg_met_thresh.crit_threshold, p_warn_threshold => p_agg_met_thresh.warn_threshold) ), p_condition_operator => p_agg_met_thresh.key_operator ); mgmt_monitoring.save_target_metric_config( p_service_target_type, p_service_name, l_source_metric.metric_name, l_source_metric.metric_column, l_source_metric.key_value, 1, l_policy_key_val_list ); END SET_THRESHOLDS; PROCEDURE ADD_EDIT_AGG_METRIC_THRESH( p_service_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_target_guid IN RAW, p_agg_met_thresh IN MGMT_SVC_AGG_MET_THR_ARRAY, p_is_add IN BOOLEAN ) IS l_source_metric MGMT_METRIC_INSTANCE := NULL; l_dep_metric_list MGMT_METRIC_INSTANCE_ARRAY := NULL; l_dep_test_metric_key_list MGMT_BCN_THRESHOLD_KEY_ARRAY := NULL; l_test_metric_key MGMT_BCN_THRESHOLD_KEY := NULL; l_agg_metric_thresh MGMT_SVC_AGG_MET_THRESH_INST := NULL; l_count NUMBER := 0; l_comp_key VARCHAR2(64) := NULL; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_edit_agg_metric_thresh: Enter', G_MODULE_NAME) ; IF ( p_is_add = true ) THEN EMDW_LOG.INFO('Adding new metrics', G_MODULE_NAME); ELSE EMDW_LOG.INFO('Modifying existing metrics', G_MODULE_NAME); END IF; END IF ; IF ( p_target_guid IS NULL ) OR ( p_service_name IS NULL ) OR ( p_service_target_type IS NULL ) THEN RETURN; END IF; IF ( p_agg_met_thresh IS NOT NULL ) AND ( p_agg_met_thresh.COUNT > 0 ) THEN FOR i IN p_agg_met_thresh.FIRST .. p_agg_met_thresh.LAST LOOP IF ( p_is_add = true ) THEN EMDW_LOG.INFO('Processing metric '||i, G_MODULE_NAME); END IF; l_agg_metric_thresh := p_agg_met_thresh(i); IF ( l_agg_metric_thresh IS NULL ) OR ( l_agg_metric_thresh.source_metric IS NULL ) THEN raise_application_error( MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid metric definition object '); END IF; l_source_metric := l_agg_metric_thresh.source_metric; IF ( l_agg_metric_thresh.metric_deps.agg_met_type = G_SYSTEM_BASED_AGG_METRIC ) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Adding/editing SYSTEM based metric', G_MODULE_NAME) ; END IF ; l_dep_metric_list := l_agg_metric_thresh.metric_deps.dep_sys_metric_list; add_edit_metric_deps( l_source_metric, l_dep_metric_list , l_agg_metric_thresh.metric_deps.eval_func, p_is_add, FALSE); ELSIF ( l_agg_metric_thresh.metric_deps.agg_met_type = G_TEST_BASED_AGG_METRIC ) THEN l_dep_test_metric_key_list := l_agg_metric_thresh.metric_deps.dep_test_metric_key_list; IF ( l_dep_test_metric_key_list IS NOT NULL ) THEN l_dep_metric_list := MGMT_METRIC_INSTANCE_ARRAY(); l_count := 1; FOR j IN l_dep_test_metric_key_list.FIRST .. l_dep_test_metric_key_list.LAST LOOP l_test_metric_key := l_dep_test_metric_key_list(j); log_bcn_txn_key( l_test_metric_key ); l_comp_key := emd_bcntxn.emd_bcn_get_composite_key( p_target_guid, l_test_metric_key, l_agg_metric_thresh.metric_deps.test_metric_key_type); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('p_target_guid=|' || p_target_guid ||'|',G_MODULE_NAME); EMDW_LOG.INFO('l_test_metric_key=|'|| l_test_metric_key.metric_name || ',' || l_test_metric_key.metric_column ||'|',G_MODULE_NAME); EMDW_LOG.INFO('l_test_metric_key=|'|| l_test_metric_key.bcn_guid || ',' || l_test_metric_key.txn_guid ||'|'|| l_test_metric_key.key_part_3,G_MODULE_NAME); EMDW_LOG.INFO('test_metric_key_tye=|'|| (l_agg_metric_thresh.metric_deps.test_metric_key_type) ||'|',G_MODULE_NAME); EMDW_LOG.INFO('Composite Key '||l_comp_key, G_MODULE_NAME ); END IF; l_dep_metric_list.extend; l_dep_metric_list(l_count) := MGMT_METRIC_INSTANCE.NEW (p_service_name, p_service_target_type, l_test_metric_key.metric_name, l_test_metric_key.metric_column, l_comp_key); l_count := l_count + 1; END LOOP; add_edit_metric_deps( l_source_metric, l_dep_metric_list, l_agg_metric_thresh.metric_deps.eval_func, p_is_add, TRUE ); END IF; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN IF ( l_agg_metric_thresh.crit_threshold IS NULL ) THEN EMDW_LOG.INFO('Critical Threshold IS NULL ', G_MODULE_NAME); ELSE EMDW_LOG.INFO('Critical Threshold IS NOT NULL STRING ', G_MODULE_NAME); END IF; EMDW_LOG.INFO('Setting thresholds for '||p_service_target_type||', '|| p_service_name||', '||l_source_metric.metric_name||', '|| l_source_metric.metric_column||', '||l_source_metric.key_value||', '|| l_agg_metric_thresh.key_operator||', '||l_agg_metric_thresh.crit_threshold|| ', '||l_agg_metric_thresh.warn_threshold, G_MODULE_NAME ); END IF ; set_thresholds( p_service_name, p_service_target_type, l_agg_metric_thresh ); END LOOP; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_edit_agg_metric_thresh: Exit', G_MODULE_NAME) ; END IF ; END ADD_EDIT_AGG_METRIC_THRESH; -- -- PROCEDURE : GET_SYSCOMPS_AVAIL_SEL_PRIV -- -- PURPOSE -- Retrieves the list of member targets of system from flat list of system associations. -- This will igonre the targets that do not have availability, member targets of -- targets with "is_cluster" property set. -- -- IN PARAMETERS -- p_system_name : target name -- p_system_target_type : target type -- OUT PARAMETERS -- p_member_targets : List of member targets eligible to choose as avaialbility -- targets. -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- FUNCTION GET_SYSCOMPS_AVAIL_SEL_PRIV( sys_tgt_id IN RAW) RETURN SERVICE_ASSOC_SYS_MEM_CURSOR IS l_member_targets SERVICE_ASSOC_SYS_MEM_CURSOR; BEGIN -- IF ( sys_tgt_id IS NULL ) THEN -- RETURN l_member_targets; -- END IF; -- Retrieves the list of member targets of a system recursivley, exluding -- the targets, which do not have availability (Response/Status metric) -- defined. It also exludes the members of cluster targets. OPEN l_member_targets FOR SELECT distinct tgt.target_name, tgt.target_type FROM mgmt_targets tgt, mgmt_flat_target_assoc flat_assoc, mgmt_metrics met WHERE flat_assoc.source_target_guid = sys_tgt_id AND flat_assoc.is_membership = 1 AND flat_assoc.assoc_target_guid = tgt.target_guid AND tgt.target_type = met.target_type AND tgt.type_meta_ver = met.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 = ' ') AND met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN MINUS SELECT tgt3.target_name, tgt3.target_type FROM mgmt_flat_target_assoc flat_assoc2, mgmt_type_properties type_props, mgmt_Targets tgt2, mgmt_target_assocs assoc, mgmt_targets tgt3 WHERE flat_assoc2.source_target_guid = sys_tgt_id AND flat_assoc2.is_membership = 1 AND flat_assoc2.assoc_target_guid = tgt2.target_guid AND tgt2.target_type=type_props.target_type AND type_props.property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP AND type_props.property_value = '1' AND assoc.source_target_guid = tgt2.target_guid AND assoc.assoc_guid = mgmt_assoc.g_contains_guid AND assoc.assoc_target_guid = tgt3.target_guid; /*SELECT distinct tgt.target_name, tgt.target_type FROM mgmt_targets tgt, mgmt_flat_target_assoc assoc WHERE assoc.source_target_guid = sys_tgt_id AND assoc.assoc_target_guid = tgt.target_guid AND tgt.target_type IN ( SELECT distinct met.target_type FROM mgmt_metrics met, mgmt_type_properties props WHERE metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN AND met.target_type NOT IN ( SELECT target_type FROM mgmt_type_properties WHERE property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP AND property_value = '1' ) ) ; */ RETURN l_member_targets; END GET_SYSCOMPS_AVAIL_SEL_PRIV; -- PROCEDURE : GET_KEY_COMPS_INFO -- -- PURPOSE -- Retrieves the list of key member system components of a service. -- -- IN PARAMETERS -- p_tgt_id : service guid -- -- RETURN -- List of key member system components of a service. -- FUNCTION GET_KEY_COMPS_INFO ( p_tgt_id IN RAW) RETURN SERVICE_ASSOC_SYS_MEM_CURSOR IS l_key_comps_info SERVICE_ASSOC_SYS_MEM_CURSOR; BEGIN OPEN l_key_comps_info FOR SELECT tgt.target_name, tgt.target_type, NVL(num_tests, 0) FROM mgmt_target_assocs assoc, mgmt_targets tgt , ( SELECT target_name as tgt_name, target_type as tgt_type, count(*) as num_tests FROM mgmt_targets tgt2, mgmt_rca_metric_test rca WHERE rca.scope_guid = p_tgt_id AND rca.target_guid = tgt2.target_guid GROUP BY target_name, target_type )fault_tests WHERE assoc.source_target_guid = p_tgt_id AND assoc.assoc_guid = mgmt_assoc.g_depends_on_guid AND tgt.target_guid = assoc.assoc_target_guid AND tgt.target_name = fault_tests.tgt_name (+) AND tgt.target_type = fault_tests.tgt_type (+) GROUP BY tgt.target_name, tgt.target_type, num_tests; RETURN l_key_comps_info; END GET_KEY_COMPS_INFO; PROCEDURE GET_TESTS (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_service_tests OUT TESTS_CURSOR) IS BEGIN OPEN p_service_tests FOR SELECT txn_guid, name, txn_type, state, is_representative FROM mgmt_bcn_txn_defn txn, mgmt_targets tgt WHERE tgt.target_name = p_service_target_name AND tgt.target_type = p_service_target_type AND tgt.target_guid = txn.target_guid ORDER BY name; END GET_TESTS; PROCEDURE GET_MNTR_STATES_FOR_TMPL ( p_template_name IN VARCHAR2, p_target_type IN VARCHAR2, p_test_ids_states OUT STATES_IDS_CURSOR) IS BEGIN OPEN p_test_ids_states FOR SELECT txn_guid, state FROM mgmt_bcn_txn_defn txn, mgmt_templates tmpl WHERE tmpl.template_name = p_template_name AND tmpl.target_type = p_target_type AND tmpl.template_guid = txn.target_guid; END GET_MNTR_STATES_FOR_TMPL; -- Deprecated API -- --PROCEDURE SET_MNTR_LOOP ( p_target_guid IN VARCHAR2, -- p_template_test_names IN SMP_EMD_STRING_ARRAY, -- p_template_test_types IN SMP_EMD_STRING_ARRAY, -- p_template_test_states IN SMP_EMD_INTEGER_ARRAY) -- --IS -- l_state MGMT_BCN_TXN_DEFN.state%TYPE; -- --BEGIN -- -- IF ( p_template_test_names IS NULL ) -- OR ( p_template_test_types IS NULL ) -- OR ( p_template_test_states IS NULL) -- OR ( p_template_test_names.COUNT = 0 ) -- OR ( p_template_test_types.COUNT = 0 ) -- OR ( p_template_test_states.COUNT = 0 ) THEN -- RETURN; -- ELSE -- -- FOR i IN p_template_test_states.FIRST .. p_template_test_states.LAST -- LOOP -- -- IF ( p_template_test_states(i) = 1 ) THEN -- l_state := 'M'; -- ELSE -- l_state := 'NM'; -- END IF; -- -- UPDATE mgmt_bcn_txn_defn -- SET state = l_state -- WHERE target_guid = p_target_guid -- AND name = p_template_test_names(i) -- AND txn_type = p_template_test_types(i); -- -- END LOOP; -- END IF; -- -- END SET_MNTR_LOOP; -- -- Deprecated API -- --PROCEDURE SET_MNTR_STATES_FOR_TMPL ( p_template_name IN VARCHAR2, -- p_target_type IN VARCHAR2, -- p_template_test_names IN SMP_EMD_STRING_ARRAY, -- p_template_test_types IN SMP_EMD_STRING_ARRAY, -- p_template_test_states IN SMP_EMD_INTEGER_ARRAY ) -- --IS -- l_template_guid MGMT_TEMPLATES.template_guid%TYPE; -- l_state MGMT_BCN_TXN_DEFN.state%TYPE; -- l_cur_user MGMT_CREATED_USERS.user_name%TYPE; -- --BEGIN -- -- SELECT template_guid -- INTO l_template_guid -- FROM MGMT_TEMPLATES -- WHERE template_name = p_template_name -- AND target_type = p_target_type; -- -- l_cur_user := MGMT_USER.get_current_em_user(); -- IF MGMT_USER.has_priv(l_cur_user, MGMT_USER.FULL_TEMPLATE, l_template_guid) <> MGMT_USER.USER_HAS_PRIV THEN -- RAISE_APPLICATION_ERROR(MGMT_GLOBAL.insufficient_privileges_err, -- 'The user does not have sufficient privileges to modify this template'); -- END IF; -- -- SET_MNTR_LOOP(l_template_guid, p_template_test_names, -- p_template_test_types, p_template_test_states); -- --END SET_MNTR_STATES_FOR_TMPL; PROCEDURE SET_MNTR_LOOP ( p_target_guid IN RAW, p_tests IN SMP_EMD_NVPAIR_ARRAY, p_set IN BOOLEAN) IS l_state MGMT_BCN_TXN_DEFN.state%TYPE; l_unmon_key_tests NUMBER; BEGIN IF ( p_set ) THEN l_state := 'M'; ELSE l_state := 'NM'; -- check for tests that are marked as key so cannot be disabled. SELECT COUNT(*) INTO l_unmon_key_tests FROM MGMT_BCN_TXN_DEFN d, (SELECT name, value FROM TABLE(CAST(p_tests AS SMP_EMD_NVPAIR_ARRAY))) tests WHERE d.name = tests.name AND d.txn_type = tests.value AND d.target_guid = p_target_guid AND d.is_representative = 'Y'; IF (l_unmon_key_tests > 0) THEN RAISE_APPLICATION_ERROR(emd_bcntxn.p_bcn_err_reptxn, 'Key test can not be in unmonitored state'); RETURN; END IF; END IF; UPDATE mgmt_bcn_txn_defn SET state = l_state WHERE target_guid = p_target_guid AND txn_guid in (SELECT d.txn_guid FROM MGMT_BCN_TXN_DEFN d, (SELECT name, value FROM TABLE(CAST(p_tests AS SMP_EMD_NVPAIR_ARRAY))) tests WHERE d.name = tests.name AND d.txn_type = tests.value AND d.target_guid = p_target_guid); END SET_MNTR_LOOP; -- -- PROCEDURE: set_template_tests_mntr_state -- -- PURPOSE: Enable/Disable the specified list of template tests -- -- IN PARAMETERS: -- p_template_name: the name of the template -- p_template_type: the template type (target type off which the template is based) -- p_tests: an array of test name/type pairs -- p_set: if 'Y', enable the tests in p_tests -- otherwise, disable the tests p_tests PROCEDURE SET_TEMPLATE_TESTS_MNTR_STATE ( p_template_name IN VARCHAR2, p_template_type IN VARCHAR2, p_tests IN SMP_EMD_NVPAIR_ARRAY, p_set IN VARCHAR) IS l_template_guid MGMT_TEMPLATES.template_guid%TYPE; l_cur_user MGMT_CREATED_USERS.user_name%TYPE; l_enable BOOLEAN; BEGIN SELECT template_guid INTO l_template_guid FROM MGMT_TEMPLATES WHERE template_name = p_template_name AND target_type = p_template_type; l_cur_user := MGMT_USER.get_current_em_user(); IF MGMT_USER.has_priv(l_cur_user, MGMT_USER.FULL_TEMPLATE, l_template_guid) <> MGMT_USER.USER_HAS_PRIV THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.insufficient_privileges_err, 'The user does not have sufficient privileges to modify this template'); END IF; IF (p_set = 'Y') THEN l_enable := TRUE; ELSE l_enable := FALSE; END IF; SET_MNTR_LOOP(l_template_guid, p_tests, l_enable); END SET_TEMPLATE_TESTS_MNTR_STATE; PROCEDURE GET_ASSOC_BEACONS (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_assoc_beacons OUT BEACONS_CURSOR) IS BEGIN OPEN p_assoc_beacons FOR SELECT tgt2.target_guid, tgt2.target_name, bcn.participates_avail, bcn.is_local FROM mgmt_bcn_target bcn, mgmt_targets tgt, mgmt_targets tgt2 WHERE tgt.target_name = p_service_target_name AND tgt.target_type = p_service_target_type AND tgt.target_guid = bcn.target_guid AND tgt2.target_guid = bcn.beacon_target_guid AND tgt2.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE ORDER BY tgt2.target_name; END GET_ASSOC_BEACONS; PROCEDURE GET_SYSTEM_DTLS (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_system_name OUT VARCHAR2, p_system_type OUT VARCHAR2, p_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR) IS l_svc_id RAW(16):= NULL; l_sys_id RAW(16):= NULL; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_SYSTEM_DTLS: Enter', G_MODULE_NAME) ; END IF ; --RAJ TBD: It should also check if it is a "is_system" target. l_svc_id := mgmt_service.get_service_guid( p_service_target_name, p_service_target_type); mgmt_service.get_system( p_service_target_name, p_service_target_type, p_system_name, p_system_type); IF ( p_system_name IS NULL ) OR ( p_system_type IS NULL ) THEN RETURN; END IF; l_sys_id := mgmt_target.get_target_guid (p_system_name, p_system_type); OPEN p_system_members FOR SELECT distinct flat_assocs.tgt, flat_assocs.tgt_type, NVL(key_comps.is_key, 0) FROM ( SELECT tgt.target_name as tgt, tgt.target_type as tgt_type FROM mgmt_flat_target_assoc, mgmt_targets tgt WHERE source_target_guid = l_sys_id AND tgt.target_guid = assoc_target_guid ) flat_assocs , ( SELECT tgt.target_name as tgt, tgt.target_type as tgt_type, 1 as is_key FROM mgmt_target_assocs, mgmt_targets tgt WHERE source_target_guid = l_svc_id AND assoc_guid = MGMT_ASSOC.g_depends_on_guid AND tgt.target_guid = assoc_target_guid ) key_comps WHERE flat_assocs.tgt = key_comps.tgt (+) AND flat_assocs.tgt_type = key_comps.tgt_type (+); /* SELECT tgt.target_name, tgt.target_type, 0 as is_key FROM mgmt_flat_target_assoc, mgmt_targets tgt WHERE source_target_guid = l_sys_tgt_id AND tgt.target_guid = assoc_target_guid UNION SELECT tgt.target_name, tgt.target_type, 1 as is_key FROM mgmt_target_assocs, mgmt_targets tgt WHERE source_target_guid = l_svc_id AND assoc_guid = MGMT_ASSOC.g_depends_on_guid AND tgt.target_guid = assoc_target_guid; */ IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_SYSTEM_DTLS: Exit', G_MODULE_NAME) ; END IF ; END GET_SYSTEM_DTLS; PROCEDURE GET_AVAIL_DTLS (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_avail_mode OUT VARCHAR2, p_avail_eval_func OUT VARCHAR2) IS l_avail_comp_method VARCHAR2(256) := null; l_avail_logic NUMBER; BEGIN -- Get avaiilability computation mechanism l_avail_comp_method := mgmt_service.get_svc_avail_comp_method( p_service_target_name, p_service_target_type); IF ( l_avail_comp_method = mgmt_service.g_test_based_avail_comp ) THEN -- Test based availability p_avail_mode := l_avail_comp_method; l_avail_logic := mgmt_gensvc_avail.get_svc_eval_logic( p_service_target_name, p_service_target_type); IF ( l_avail_logic = mgmt_gensvc_avail.k_or_eval ) THEN p_avail_eval_func := mgmt_severity_helper.G_OR_AVAIL_EVAL_PROC_NAME; ELSIF ( l_avail_logic = mgmt_gensvc_avail.k_and_eval ) THEN p_avail_eval_func := mgmt_severity_helper.G_AND_AVAIL_EVAL_PROC_NAME; ELSE p_avail_eval_func := 'Unknown'; END IF; ELSIF ( l_avail_comp_method = mgmt_service.g_system_based_avail_comp ) THEN -- System based availability p_avail_mode := l_avail_comp_method; mgmt_service.get_avail_eval_func( p_service_target_name, p_service_target_type, p_avail_eval_func); ELSE p_avail_mode := 'Unknown'; p_avail_eval_func := 'Unknown'; END IF; END GET_AVAIL_DTLS; ---------------------END PRIVATE PROCEDURES/FUNCTIONS---------------------------------- -- -- PROCEDURE : GET_SYSTEM_COMPS_FOR_AVAIL_SEL -- -- PURPOSE -- Retrieves the list of member targets of system from flat list of system associations. -- This will igonre the targets that do not have availability, member targets of -- targets with "is_cluster" property set. -- -- IN PARAMETERS -- p_system_name : target name -- p_system_target_type : target type -- OUT PARAMETERS -- p_system_timezone : System time zone -- p_member_targets : List of member targets eligible to choose as avaialbility -- targets. -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- PROCEDURE GET_SYSTEM_COMPS_FOR_AVAIL_SEL( p_system_name IN VARCHAR2, p_system_target_type IN VARCHAR2, p_system_timezone OUT VARCHAR2, p_member_targets OUT SERVICE_ASSOC_SYS_MEM_CURSOR) IS l_sys_tgt_id RAW(16); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_SYSTEM_COMPS_FOR_AVAIL_SEL: Enter', G_MODULE_NAME) ; END IF ; --RAJ TBD: It should also check if it is a "is_system" target. l_sys_tgt_id := mgmt_target.get_target_guid(p_system_name, p_system_target_type); SELECT timezone_region INTO p_system_timezone FROM mgmt_targets WHERE target_guid = l_sys_tgt_id; -- Retrieves the list of member targets of a system recursivley, exluding -- the targets, which do not have availability (Response/Status metric) -- defined. It also exludes the members of cluster targets. p_member_targets := get_syscomps_avail_sel_priv( l_sys_tgt_id); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_SYSTEM_COMPS_FOR_AVAIL_SEL: Exit', G_MODULE_NAME) ; END IF ; END GET_SYSTEM_COMPS_FOR_AVAIL_SEL; -- PROCEDURE : GET_SYSTEM_COMPS_FOR_METR_PROM -- -- PURPOSE -- Retrieves the list of member targets of system from flat list of system associations. -- This will igonre the targets that do not have availability, member targets of -- targets with "is_cluster" property set. -- -- IN PARAMETERS -- p_service_name : service name -- p_service_target_type : service type -- OUT PARAMETERS -- p_system_name : system name -- p_system_system_type : system type -- p_member_targets : List of member targets including is key or not -- targets. -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- PROCEDURE GET_SYSTEM_COMPS_FOR_METR_PROM (p_service_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_system_name OUT VARCHAR2, p_system_target_type OUT VARCHAR2, p_member_targets OUT SERVICE_ASSOC_SYS_MEM_CURSOR) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_SYSTEM_COMPS_FOR_METR_PROM: Enter', G_MODULE_NAME) ; END IF ; get_system_dtls (p_service_name, p_service_target_type, p_system_name, p_system_target_type, p_member_targets); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_SYSTEM_COMPS_FOR_METR_PROM: Exit', G_MODULE_NAME) ; END IF ; END GET_SYSTEM_COMPS_FOR_METR_PROM; -- -- PROCEDURE : GET_ASSOC_SYS_COMP_DETAILS -- -- PURPOSE -- Retrieves the list of member targets of system from falt associations of system. -- This will igonre the targets that do not have availability, member targets of -- targets with "is_cluster" property set. This procedure also retrieves the -- number of fault tests defined in the scope of service. It also identifies -- previously selected "key" components. -- -- IN PARAMETERS -- p_service_name : target name -- p_service_target_type : target type -- OUT PARAMETERS -- p_service_timezone : Service time zone -- p_system_name : System target name -- p_system_target_type : System target type -- p_system_timezone : System time zone -- p_member_targets : List of member targets eligible to choose as avaialbility -- targets. -- p_key_comps_info : List of key components with the number of metric tests -- defined on each of them. -- p_avail_mode : The availability mode -- p_avail_eval_func : The availability eval function -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- PROCEDURE GET_SVC_ASSOC_SYS_COMP_DETAILS( p_service_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_service_timezone OUT VARCHAR2, p_service_props OUT PROPS_CURSOR, p_system_name OUT VARCHAR2, p_system_target_type OUT VARCHAR2, p_system_timezone OUT VARCHAR2, p_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR, p_key_comps_info OUT SERVICE_ASSOC_SYS_MEM_CURSOR, p_avail_mode OUT VARCHAR2, p_avail_eval_func OUT VARCHAR2) IS l_tgt_id mgmt_targets.target_guid%TYPE; l_sys_id mgmt_targets.target_guid%TYPE; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_assoc_sys_comp_details: Enter', G_MODULE_NAME) ; END IF ; l_tgt_id := mgmt_service.get_service_guid( p_service_name, p_service_target_type); SELECT timezone_region INTO p_service_timezone FROM mgmt_targets WHERE target_guid = l_tgt_id; --retrieving service properties OPEN p_service_props FOR SELECT property_name, property_value FROM MGMT_TARGET_PROPERTIES WHERE target_guid=l_tgt_id AND property_type='INSTANCE'; --retrieving system details BEGIN SELECT tgt.target_guid, tgt.target_name, tgt.target_type, tgt.timezone_region INTO l_sys_id, p_system_name, p_system_target_type, p_system_timezone FROM mgmt_targets tgt, mgmt_target_assocs assoc WHERE assoc.source_target_guid = l_tgt_id AND assoc.assoc_guid = mgmt_assoc.g_runs_on_guid AND assoc.assoc_target_guid = tgt.target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN --system may not be selected for the service NULL; END; p_system_members := get_syscomps_avail_sel_priv(l_sys_id); p_key_comps_info := get_key_comps_info(l_tgt_id); /* -- moved this code to GET_KEY_COMPS_INFO OPEN p_key_comps_info FOR SELECT tgt.target_name, tgt.target_type, NVL(num_tests, 0) FROM mgmt_target_assocs assoc, mgmt_targets tgt , ( SELECT target_name as tgt_name, target_type as tgt_type, count(*) as num_tests FROM mgmt_targets tgt2, mgmt_rca_metric_test rca WHERE rca.scope_guid = l_tgt_id AND rca.target_guid = tgt2.target_guid GROUP BY target_name, target_type )fault_tests WHERE assoc.source_target_guid = l_tgt_id AND assoc.assoc_guid = mgmt_assoc.g_depends_on_guid AND tgt.target_guid = assoc.assoc_target_guid AND tgt.target_name = fault_tests.tgt_name (+) AND tgt.target_type = fault_tests.tgt_type (+) GROUP BY tgt.target_name, tgt.target_type, num_tests; */ /* SELECT tgt.target_name, tgt.target_type, count(*) FROM mgmt_target_assocs assoc, mgmt_targets tgt, mgmt_rca_metric_test rca WHERE assoc.source_target_guid = l_tgt_id AND assoc.assoc_guid = mgmt_assoc.g_depends_on_guid AND tgt.target_guid = assoc.assoc_target_guid AND rca.scope_guid =l_tgt_id AND rca.target_guid = assoc.assoc_guid(+) GROUP BY tgt.target_name, tgt.target_type; */ get_avail_dtls ( p_service_name, p_service_target_type, p_avail_mode, p_avail_eval_func); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_assoc_sys_comp_details: Enter', G_MODULE_NAME) ; END IF ; END GET_SVC_ASSOC_SYS_COMP_DETAILS; -- -- PROCEDURE : SET_SVC_METRIC_THRESHOLDS -- -- PURPOSE -- Sets Service aggregate metrics with all dependency metrics. It creates -- metric dependencies, deletes old metrics and modify existing dependencies. -- It also sets thresholds for the metrics. -- -- IN PARAMETERS -- p_service_name : target name -- p_service_target_type : target type -- p_add_metrics : List of new metrics to be added -- p_delete_metrics : List of old metrics to be deleted -- p_modify_metrics : List of existing metrics with new dependenccy metrics list -- p_def_chart_perf_metric_name : Metric name of default performance chart in Service homepage -- p_def_chart_perf_metric_column : Metric column of default performance chart in Service homepage -- p_def_chart_perf_key_value : Key value of default usage chart in Service homepage -- p_def_chart_usge_metric_name : Metric name of default usage chart in Service homepage -- p_def_chart_usge_metric_column : Metric column of default usage chart in Service homepage -- p_def_chart_usge_key_value : Key value of default usage chart in Service homepage -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- INSUFFICIENT_PRIVILEGES: user doesn't have TARGET_OPERATOR privilidge on this service -- INVALID_PARAMS_ERR: Invalid parameters -- PROCEDURE SET_SVC_AGG_METRIC_THRESHOLDS( p_service_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_add_metrics IN MGMT_SVC_AGG_MET_THR_ARRAY, p_delete_metrics IN MGMT_METRIC_INSTANCE_ARRAY, p_modify_metrics IN MGMT_SVC_AGG_MET_THR_ARRAY, p_def_chart_perf_metric_name IN VARCHAR2, p_def_chart_perf_metric_column IN VARCHAR2, p_def_chart_perf_key_value IN VARCHAR2, p_def_chart_usge_metric_name IN VARCHAR2, p_def_chart_usge_metric_column IN VARCHAR2, p_def_chart_usge_key_value IN VARCHAR2) IS l_tgt_id mgmt_targets.target_guid%TYPE := NULL; met_instance MGMT_METRIC_INSTANCE := NULL; l_met_id mgmt_metrics.metric_guid%TYPE := NULL; l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); v_metric_guids SMP_EMD_STRING_ARRAY; l_bus_sessions SMP_EMD_STRING_ARRAY; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_svc_agg_metric_thresholds: Enter', G_MODULE_NAME) ; END IF ; IF ( p_service_name IS NULL ) OR ( p_service_target_type IS NULL ) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'One of Service Target name, Target type is Null'); END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Adding/editing thresholds : Enter', G_MODULE_NAME) ; IF ( p_add_metrics IS NULL ) OR ( p_add_metrics.COUNT = 0 ) THEN EMDW_LOG.INFO('New metrics to be added 0', G_MODULE_NAME) ; ELSE EMDW_LOG.INFO('New metrics to be added '||p_add_metrics.COUNT, G_MODULE_NAME) ; END IF; IF ( p_modify_metrics IS NULL ) OR ( p_modify_metrics.COUNT = 0 ) THEN EMDW_LOG.INFO('Existing metrics to be modified 0', G_MODULE_NAME) ; ELSE EMDW_LOG.INFO('Existing metrics to be modified '||p_modify_metrics.COUNT, G_MODULE_NAME) ; END IF; IF ( p_delete_metrics IS NULL ) OR ( p_delete_metrics.COUNT = 0 ) THEN EMDW_LOG.INFO('Metrics to be deleted 0', G_MODULE_NAME) ; ELSE EMDW_LOG.INFO('Metrics to be deleted '||p_delete_metrics.COUNT, G_MODULE_NAME) ; END IF; END IF ; --check if the target is a service l_tgt_id := mgmt_service.get_service_guid( p_service_name, p_service_target_type); IF( MGMT_USER.has_priv ( l_current_user, MGMT_USER.OPERATOR_TARGET, l_tgt_id) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'GENSVC.SET_SVC_AGG_METRIC_THRESHOLDS: The current user (' || l_current_user || ') does not have sufficient privileges to perform this operation'); END IF; add_edit_agg_metric_thresh( p_service_name, p_service_target_type, l_tgt_id, p_add_metrics, TRUE); add_edit_agg_metric_thresh( p_service_name, p_service_target_type, l_tgt_id, p_modify_metrics, FALSE); IF ( p_delete_metrics IS NOT NULL ) AND ( p_delete_metrics.COUNT > 0 ) THEN FOR i IN p_delete_metrics.FIRST .. p_delete_metrics.LAST LOOP IF ( p_delete_metrics(i) IS NOT NULL ) THEN -- em_rep_metric.delete_metric_severity_deps( p_delete_metrics(i), NULL ); em_rep_metric.delete_repo_metric_collection( p_delete_metrics(i) ); -- delete metric thresholds l_met_id := mgmt_metric.get_metric_guid_for_target(p_service_target_type, p_service_name, p_delete_metrics(i).metric_name, p_delete_metrics(i).metric_column ); mgmt_monitoring.delete_target_metric_config( p_service_target_type, p_service_name, p_delete_metrics(i).metric_name, p_delete_metrics(i).metric_column, p_delete_metrics(i).KEY_VALUE ); BEGIN v_metric_guids := SMP_EMD_STRING_ARRAY(); v_metric_guids.extend; v_metric_guids(1) := RAWTOHEX(l_met_id); EM_METRIC.EXEC_CBK_METRIC_KEYVAL(l_tgt_id, v_metric_guids, p_delete_metrics(i).key_value); v_metric_guids.delete; EXCEPTION WHEN OTHERS THEN IF(v_metric_guids IS NOT NULL) THEN v_metric_guids.DELETE; END IF; RAISE; END; END IF; END LOOP; END IF; IF ( has_promoted_metrics (p_service_name, p_service_target_type, mgmt_global.G_SERVICE_CATEGORY_BUSINESS)) THEN IF (NOT em_bam_service.is_business_data_enabled(p_target_name => p_service_name, p_target_type => p_service_target_type)) THEN em_bam_service.enable_business_data(p_target_name => p_service_name, p_target_type => p_service_target_type); END IF; ELSE IF (em_bam_service.is_business_data_enabled(p_target_name => p_service_name, p_target_type => p_service_target_type)) THEN l_bus_sessions := em_bam_service.get_target_datasessions(p_service_name, p_service_target_type); IF (l_bus_sessions IS NULL) OR (l_bus_sessions.COUNT = 0) THEN em_bam_service.disable_business_data(p_target_name => p_service_name, p_target_type => p_service_target_type); END IF; END IF; END IF; if ( p_def_chart_perf_metric_name IS NOT NULL) AND ( p_def_chart_perf_metric_column IS NOT NULL) AND ( p_def_chart_perf_key_value IS NOT NULL) THEN mgmt_service.set_homepage_perf_chart(p_service_name, p_service_target_type, p_def_chart_perf_metric_name, p_def_chart_perf_metric_column, p_def_chart_perf_key_value); ELSIF ( p_def_chart_perf_metric_name IS NOT NULL) AND ( p_def_chart_perf_metric_column IS NOT NULL) THEN mgmt_service.set_homepage_perf_chart(p_service_name, p_service_target_type, NULL, NULL, NULL); END IF; if ( p_def_chart_usge_metric_name IS NOT NULL) AND ( p_def_chart_usge_metric_column IS NOT NULL) AND ( p_def_chart_usge_key_value IS NOT NULL) THEN mgmt_service.set_homepage_usage_chart(p_service_name, p_service_target_type, p_def_chart_usge_metric_name, p_def_chart_usge_metric_column, p_def_chart_usge_key_value); ELSIF ( p_def_chart_usge_metric_name IS NOT NULL) AND ( p_def_chart_usge_metric_column IS NOT NULL) THEN mgmt_service.set_homepage_usage_chart(p_service_name, p_service_target_type, NULL, NULL, NULL); END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_svc_agg_metric_thresholds: Exit', G_MODULE_NAME) ; END IF ; END SET_SVC_AGG_METRIC_THRESHOLDS; -- -- PROCEDURE : GET_SVC_AGG_METRIC_THRESHOLDS -- -- PURPOSE -- Given service name, service target type and metric category, this procedures -- retrieves the list of metric thresholds and defaul chart metric for that -- category. -- -- IN PARAMETERS -- p_service_name : target name -- p_target_type : target type -- p_metric_category : Metric Category. Example, Usage/Reponse Time -- OUT PARAMETERS -- p_metric_thresh_list : List of metrics with thresholds -- p_system_name : The name of the associated system -- p_system_type : The type of the associated system -- p_system_members : A list of system components -- p_service_tests : A list of service tests -- p_assoc_beacons : A list of associated beacons -- p_def_chart_metric_name : Metric name of default chart in Service homepage -- p_def_chart_metric_column : Metric column of default chart in Service homepage -- p_def_chart_key_value : Key value of default chart in Service homepage -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- PROCEDURE GET_SVC_AGG_METRIC_THRESHOLDS( p_service_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_metric_category IN VARCHAR2, p_thresholds OUT SERVICE_AGG_MET_THRESH_CURSOR, p_system_name OUT VARCHAR2, p_system_type OUT VARCHAR2, p_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR, p_service_tests OUT TESTS_CURSOR, p_assoc_beacons OUT BEACONS_CURSOR, p_def_chart_metric_name OUT VARCHAR2, p_def_chart_metric_column OUT VARCHAR2, p_def_chart_key_value OUT VARCHAR2 ) IS l_tgt_id RAW(16); l_svc_metrics SMP_EMD_NVPAIR_ARRAY := NULL; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_svc_id MGMT_TARGETS.target_guid%TYPE; l_sys_id MGMT_TARGETS.target_guid%TYPE; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_agg_metric_thresholds: Enter', G_MODULE_NAME) ; END IF ; IF ( p_service_name IS NULL ) OR ( p_service_target_type IS NULL ) OR ( p_metric_category IS NULL ) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'One of Service Target name, Target type is Null'); END IF; --check if the target is a service l_tgt_id := mgmt_service.get_service_guid( p_service_name, p_service_target_type); -- Retrieve metric thresholds for given metric category OPEN p_thresholds FOR SELECT met.metric_name, met.metric_column, met_dep.key_value, critical_operator, thresH.critical_threshold, thresh.warning_threshold FROM mgmt_metrics met, mgmt_targets tgt, mgmt_category_map cat_map, mgmt_metric_dependency met_dep, mgmt_metric_thresholds thresh WHERE tgt.target_guid = l_tgt_id AND cat_map.target_type = tgt.target_type AND (cat_map.type_meta_ver = tgt.type_meta_ver OR tgt.type_meta_ver IS NULL) AND cat_map.object_guid = met.metric_guid AND cat_map.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC AND cat_map.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE AND cat_map.category_name = p_metric_category AND tgt.type_meta_ver = met.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 = ' ') AND met_dep.target_guid = tgt.target_guid AND met_dep.metric_guid = met.metric_guid AND thresh.target_guid = tgt.target_guid AND thresh.metric_guid = met.metric_guid AND thresh.key_value = met_dep.key_value ORDER BY met_dep.key_value; IF (p_metric_category = mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE) THEN mgmt_service.get_homepage_perf_chart(p_service_name, p_service_target_type, p_def_chart_metric_name, p_def_chart_metric_column, p_def_chart_key_value); get_tests (p_service_name, p_service_target_type, p_service_tests); get_assoc_beacons (p_service_name, p_service_target_type, p_assoc_beacons); get_system_dtls (p_service_name, p_service_target_type, p_system_name, p_system_type, p_system_members); ELSIF (p_metric_category = mgmt_global.G_SERVICE_CATEGORY_USAGE) THEN mgmt_service.get_homepage_usage_chart(p_service_name, p_service_target_type, p_def_chart_metric_name, p_def_chart_metric_column, p_def_chart_key_value); get_system_dtls (p_service_name, p_service_target_type, p_system_name, p_system_type, p_system_members); ELSIF (p_metric_category = mgmt_global.G_SERVICE_CATEGORY_BUSINESS) THEN -- no default metrics defined. p_def_chart_metric_name := NULL; p_def_chart_metric_column := NULL; p_def_chart_key_value := NULL; --RAJ TBD: It should also check if it is a "is_system" target. l_svc_id := mgmt_service.get_service_guid( p_service_name, p_service_target_type); mgmt_service.get_system( p_service_name, p_service_target_type, p_system_name, p_system_type); IF ( p_system_name IS NOT NULL ) AND ( p_system_type IS NOT NULL ) THEN GET_BUSINESS_SYS_MEMBERS(p_service_name, p_service_target_type, p_system_members); END IF; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_agg_metric_thresholds: Enter', G_MODULE_NAME) ; END IF ; END GET_SVC_AGG_METRIC_THRESHOLDS; -- -- PROCEDURE : GET_BUSINESS_SYS_MEMBERS -- -- PURPOSE -- For a given service, this function will return all of the member services -- having business metrics. For an aggregate service, all members are -- examined. For a generic service, only key components are examined. -- The customization framework is used. -- -- IN PARAMETERS -- p_service_name : target name -- p_service_target_type : target type -- OUT PARAMETERS -- p_member_targets : List of member targets having business data -- -- EXCEPTION -- INVALID_PARAMS_ERR: if target name or type is null -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- PROCEDURE GET_BUSINESS_SYS_MEMBERS( p_service_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR) IS l_svc_id MGMT_TARGETS.target_guid%TYPE; l_sys_id MGMT_TARGETS.target_guid%TYPE; l_system_name MGMT_TARGETS.target_name%TYPE; l_system_type MGMT_TARGETS.target_type%TYPE; BEGIN IF ( p_service_name IS NULL ) OR ( p_service_target_type IS NULL ) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'One of Service Target name, Target type is Null'); END IF; l_svc_id := mgmt_service.get_service_guid( p_service_name, p_service_target_type); mgmt_service.get_system( p_service_name, p_service_target_type, l_system_name, l_system_type); IF ( l_system_name IS NOT NULL ) AND ( l_system_type IS NOT NULL ) THEN l_sys_id := mgmt_target.get_target_guid (l_system_name, l_system_type); END IF; -- get all memebers that are -- 1. services and -- 2. have business metrics. OPEN p_system_members FOR SELECT members.target_name, members.target_type, members.is_key FROM ( SELECT distinct flat_assocs.target_guid as target_guid, flat_assocs.tgt as target_name, flat_assocs.tgt_type as target_type, NVL(key_comps.is_key, 0) as is_key FROM ( SELECT tgt.target_guid as target_guid, tgt.target_name as tgt, tgt.target_type as tgt_type FROM mgmt_flat_target_assoc, mgmt_targets tgt WHERE source_target_guid = l_sys_id AND tgt.target_guid = assoc_target_guid ) flat_assocs , ( SELECT tgt.target_guid as target_guid, tgt.target_name as tgt, tgt.target_type as tgt_type, 1 as is_key FROM mgmt_target_assocs, mgmt_targets tgt WHERE source_target_guid = l_svc_id AND assoc_guid = MGMT_ASSOC.g_depends_on_guid AND tgt.target_guid = assoc_target_guid ) key_comps WHERE flat_assocs.tgt = key_comps.tgt (+) AND flat_assocs.tgt_type = key_comps.tgt_type (+) AND flat_assocs.target_guid = key_comps.target_guid (+) ) members, mgmt_type_properties type_props, mgmt_target_properties props WHERE type_props.target_type = members.target_type AND type_props.property_value = '1' AND (type_props.property_name = MGMT_GLOBAL.G_IS_SERVICE_PROP OR type_props.property_name = MGMT_GLOBAL.G_IS_AGGREGATE_SERVICE_PROP) AND members.target_guid = props.target_guid AND props.property_name = EM_SERVICE.G_SVC_HAS_BUSINESS AND props.property_value = 'true'; END GET_BUSINESS_SYS_MEMBERS; -- -- PROCEDURE : GET_SVC_AGG_METRIC_DEPS -- -- PURPOSE -- Given service name, service target type, metric name, metric column and -- key value, this procedures returns the aggregate metric type, list -- of metric dependencies and the evaluation function. -- category. -- -- IN PARAMETERS -- p_source_metric : Source metric name -- OUT PARAMETERS -- p_metric_deps : Metric dependencies -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- INVALID_PARAMS_ERR: Invalid parameters -- PROCEDURE GET_SVC_AGG_METRIC_DEPS( p_source_metric IN MGMT_METRIC_INSTANCE, p_metric_deps OUT MGMT_SVC_AGG_METRIC_DEP) IS l_tgt_id mgmt_targets.target_guid%TYPE := NULL; l_met_id RAW(16):= NULL; l_eval_func mgmt_metric_dependency.eval_func%TYPE; l_dep_metric_details MGMT_METRIC_INSTANCE_ARRAY := NULL; l_test_metric_key_type NUMBER; l_dep_test_key_list MGMT_BCN_THRESHOLD_KEY_ARRAY := NULL; l_txn_guid VARCHAR2(64) := NULL; l_bcn_guid VARCHAR2(64) := NULL; l_step_or_grp_guid VARCHAR2(64) := NULL; l_ret NUMBER := -1; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_agg_metric_deps: Enter', G_MODULE_NAME) ; END IF ; IF ( p_source_metric IS NULL ) THEN RETURN; END IF; --check if the target is a service l_tgt_id := mgmt_service.get_service_guid( p_source_metric.target_name, p_source_metric.target_type); l_met_id := mgmt_metric.get_metric_guid_for_target( p_source_metric.target_type, p_source_metric.target_name, p_source_metric.metric_name, p_source_metric.metric_column); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('metric guid for '|| p_source_metric.target_type||', '|| p_source_metric.target_name ||', '|| p_source_metric.metric_name||', '|| p_source_metric.metric_column ||' is : '|| l_met_id,G_MODULE_NAME ); END IF ; SELECT eval_func INTO l_eval_func FROM mgmt_metric_dependency WHERE target_guid = l_tgt_id AND metric_guid = l_met_id AND key_value = p_source_metric.key_value; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Eval Func '|| l_eval_func, G_MODULE_NAME ); END IF ; SELECT MGMT_METRIC_INSTANCE(tgt.target_name, tgt.target_type, met.metric_name, met.metric_column, detail_dep.dep_key_value) BULK COLLECT INTO l_dep_metric_details FROM mgmt_metrics met, mgmt_targets tgt, mgmt_metric_dependency_details detail_dep WHERE detail_dep.target_guid = l_tgt_id AND detail_dep.metric_guid = l_met_id AND detail_dep.key_value = p_source_metric.key_value AND detail_dep.dep_target_guid = tgt.target_guid AND detail_dep.dep_metric_guid = met.metric_guid AND met.type_meta_ver = tgt.type_meta_ver; IF ( l_dep_metric_details IS NOT NULL ) AND ( l_dep_metric_details.COUNT > 0 ) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Dependencies exist '|| l_dep_metric_details.COUNT, G_MODULE_NAME ); END IF ; -- Checks if the first dependency metric is based on a different target -- If yes, it is considered as a system based metric. -- RAJ TBD: There should be a better way to identify a system based -- metric versus test based metric. IF ( l_dep_metric_details(1).target_name != p_source_metric.target_name ) OR ( l_dep_metric_details(1).target_type != p_source_metric.target_type ) THEN l_test_metric_key_type := -1; p_metric_deps := MGMT_SVC_AGG_METRIC_DEP( G_SYSTEM_BASED_AGG_METRIC, l_dep_metric_details, NULL, l_test_metric_key_type, l_eval_func); ELSE IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('This is a test based metric ', G_MODULE_NAME); END IF ; --RAJ TBD: Get test based metric dependencies --get key parts for each of the composite keys l_dep_test_key_list := MGMT_BCN_THRESHOLD_KEY_ARRAY(); FOR i IN l_dep_metric_details.FIRST .. l_dep_metric_details.LAST LOOP l_ret := emd_bcntxn.emd_bcn_get_comp_key_elements( l_tgt_id, l_dep_metric_details(i).key_value, l_txn_guid, l_bcn_guid, l_step_or_grp_guid ); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Return value '||l_ret, G_MODULE_NAME); EMDW_LOG.INFO('Dependencies: '||l_txn_guid ||', '||l_bcn_guid || ', '|| l_step_or_grp_guid, G_MODULE_NAME ); END IF ; l_dep_test_key_list.extend; IF ( l_ret = 1 ) THEN l_dep_test_key_list(i) := MGMT_BCN_THRESHOLD_KEY(l_bcn_guid, l_txn_guid, l_step_or_grp_guid, NULL, NULL, l_dep_metric_details(i).metric_name, l_dep_metric_details(i).metric_column); ELSE l_dep_test_key_list(i) := MGMT_BCN_THRESHOLD_KEY( NULL, NULL, NULL, NULL, NULL, l_dep_metric_details(i).metric_name, l_dep_metric_details(i).metric_column); END IF; END LOOP; --It is assuming the transactions type all the time. This has to be -- resolved in Java layer using Test Meta data API. l_test_metric_key_type := 1; p_metric_deps := MGMT_SVC_AGG_METRIC_DEP( G_TEST_BASED_AGG_METRIC, NULL, l_dep_test_key_list, l_test_metric_key_type, l_eval_func); END IF; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_agg_metric_deps: Exit', G_MODULE_NAME) ; END IF ; END GET_SVC_AGG_METRIC_DEPS; -- -- PROCEDURE : GET_SVC_AGG_MET_DTLS_FOR_EDIT -- -- PURPOSE -- Given service name, service target type, metric name, metric column and -- key value, this procedures returns the aggregate metric type, list -- of metric dependencies, evaluation function and the either the list of -- system components or list of all tests. Any one of system components or -- tests will be null depending on the aggregate metric type. -- category. -- -- IN PARAMETERS -- p_source_metric : Source metric name -- OUT PARAMETERS -- p_metric_deps : Metric dependencies -- p_system_members : System members with key components identified. -- p_service_tests : List of all tests with guids -- p_assoc_beacons : Associated beacons with key beacons identified -- p_dep_column_label : Dependency metric column label -- p_dep_column_nls_id: Dependency column nls id -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- INVALID_PARAMS_ERR: Invalid parameters -- PROCEDURE GET_SVC_AGG_MET_DTLS_FOR_EDIT( p_source_metric IN MGMT_METRIC_INSTANCE, p_system_name OUT VARCHAR2, p_system_type OUT VARCHAR2, p_metric_deps OUT MGMT_SVC_AGG_METRIC_DEP, p_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR, p_service_tests OUT TESTS_CURSOR, p_assoc_beacons OUT BEACONS_CURSOR, p_dep_column_label OUT VARCHAR2, p_dep_column_nls_id OUT VARCHAR2) IS l_sys_id RAW(16):= NULL; met_name mgmt_metrics.metric_name%TYPE; met_col mgmt_metrics.metric_column%TYPE; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_agg_met_dtls_for_edit: Enter', G_MODULE_NAME) ; END IF ; IF ( p_source_metric IS NULL ) THEN RETURN; END IF; get_svc_agg_metric_deps( p_source_metric, p_metric_deps); IF ( p_metric_deps IS NULL ) THEN RETURN; END IF; IF ( p_metric_deps.AGG_MET_TYPE = G_SYSTEM_BASED_AGG_METRIC) THEN IF ( p_metric_deps.dep_sys_metric_list IS NOT NULL ) AND (p_metric_deps.dep_sys_metric_list.COUNT > 0 ) THEN met_name := p_metric_deps.dep_sys_metric_list(1).metric_name; met_col := p_metric_deps.dep_sys_metric_list(1).metric_column; SELECT distinct column_label, column_label_nlsid INTO p_dep_column_label, p_dep_column_nls_id FROM mgmt_metrics met, mgmt_targets tgt WHERE tgt.target_name = p_metric_deps.dep_sys_metric_list(1).target_name AND tgt.target_type = p_metric_deps.dep_sys_metric_list(1).target_type AND tgt.target_type = met.target_type AND met.metric_name = met_name AND met.metric_column = met_col 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; get_system_dtls(p_source_metric.target_name, p_source_metric.target_type, p_system_name, p_system_type, p_system_members); /* OPEN p_dep_key_metrics FOR SELECT distinct keymet.metric_name, keymet.metric_column, keymet.metric_column_label, keymet.metric_column_nls_id FROM mgmt_metrics met, mgmt_metrics keymet WHERE met.metric_name = met_name AND met.metric_column = met_col AND met.metric_name = keymet.metric_name AND met.key_column = keymet.metric_column AND met.category_prop_1 = ' ' AND met.category_prop_2 = ' ' AND met.category_prop_3 = ' ' AND met.category_prop_4 = ' ' AND met.category_prop_5 = ' ' AND keymet.category_prop_1 = ' ' AND keymet.category_prop_2 = ' ' AND keymet.category_prop_3 = ' ' AND keymet.category_prop_4 = ' ' AND keymet.category_prop_5 = ' ' AND keymet.key_order != 0; */ ELSIF( p_metric_deps.AGG_MET_TYPE = G_TEST_BASED_AGG_METRIC ) THEN --RAJ TBD: Return all tests including steps and step groups without -- properties IF ( p_metric_deps.dep_test_metric_key_list IS NOT NULL ) AND (p_metric_deps.dep_test_metric_key_list.COUNT > 0 ) THEN met_name := p_metric_deps.dep_test_metric_key_list(1).metric_name; met_col := p_metric_deps.dep_test_metric_key_list(1).metric_column; SELECT distinct column_label, column_label_nlsid INTO p_dep_column_label, p_dep_column_nls_id FROM mgmt_metrics met, mgmt_targets tgt WHERE tgt.target_name = p_source_metric.target_name AND tgt.target_type = p_source_metric.target_type AND tgt.target_type = met.target_type AND met.metric_name = met_name AND met.metric_column = met_col 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; get_tests (p_source_metric.target_name, p_source_metric.target_type, p_service_tests); get_assoc_beacons (p_source_metric.target_name, p_source_metric.target_type, p_assoc_beacons); END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_agg_met_dtls_for_edit: Exit', G_MODULE_NAME) ; END IF ; END GET_SVC_AGG_MET_DTLS_FOR_EDIT; -- --PURPOSE: Associates system and critical components with the servie. Also -- change the properties --PROCEDURE: SET_SYSTEM_AND_PROPS -- PROCEDURE SET_SYSTEM_AND_PROPS(p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_system_name IN VARCHAR2, p_system_type IN VARCHAR2, p_critical_components IN SMP_EMD_NVPAIR_ARRAY, p_service_props IN MGMT_TARGET_PROPERTY_LIST) IS BEGIN mgmt_service.set_system(p_service_target_name, p_service_target_type, p_system_name, p_system_type, p_critical_components ); -- Store properties END SET_SYSTEM_AND_PROPS; -- --PURPOSE: Removes the service association with the system and critical -- components and also stores properties. --PROCEDURE: REMOVE_SYSTEM_AND_SAVE_PROPS -- PROCEDURE REMOVE_SYSTEM_AND_SAVE_PROPS(p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_service_props IN MGMT_TARGET_PROPERTY_LIST) IS BEGIN mgmt_service.remove_system( p_service_target_name, p_service_target_type); -- Store properties END REMOVE_SYSTEM_AND_SAVE_PROPS; /* CREATE OR REPLACE PACKAGE BODY gensvc IS -- -- PROCEDURE : GET_RESPONSE_METRIC_THRESHOLDS -- -- PURPOSE -- Returns the response metrics of a service target type -- PARAMETERS -- v_target_type : target type -- v_type_meta_ver : If type-meta ver is not passed then the response -- metrics across type meta vers is returned. -- RETURN -- An NV array containing metric names and columns. -- NULL if there are no response metrics. -- PROCEDURE GET_COMPONENTS_FROM_TARGET ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, l_components_cur OUT p_cursor_type, p_system_display OUT VARCHAR2, p_system_timezone OUT VARCHAR2) IS BEGIN OPEN l_components_cur FOR SELECT target_name, target_type FROM mgmt_targets; p_system_display := 'My OCS System'; p_system_timezone := 'Los Angeles (PST) -700'; END GET_COMPONENTS_FROM_TARGET; PROCEDURE GET_COMPONENTS_FROM_SYSTEM ( p_system_name IN VARCHAR2, l_components_cur OUT p_cursor_type, p_system_display OUT VARCHAR2, p_system_timezone OUT VARCHAR2) IS BEGIN OPEN l_components_cur FOR SELECT target_name, target_type FROM mgmt_targets; p_system_display := 'My OCS System'; p_system_timezone := 'Los Angeles (PST) -700'; END GET_COMPONENTS_FROM_SYSTEM; END gensvc; -- SELECT (metric_name, metric_column) -- BULK COLLECT INTO l_metrics -- FROM mgmt_metrics m, mgmt_metric_thresholds th -- mgmt_category_map c -- WHERE c.target_type = v_target_type -- AND (c.type_meta_ver = v_type_meta_ver OR -- v_type_meta_ver IS NULL) -- AND c.object_guid = m.metric_guid -- AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC -- AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE -- AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_RESPONSE; -- AND m.metric_gui = th.metric_guid; */ -- -- PROCEDURE : GET_SVC_DTLS_FOR_AVAIL -- -- PURPOSE -- Given service name, service type, this procedures returns -- all the information necessary to render the availability definition page. -- -- This includes the availability mode, avalability evaluation function, -- the system name, system type, system timezone, system members, -- a list of service tests (including name, type, status, key, txnId, and -- excluding the details such as properties) -- a list of associated beacons. -- -- IN PARAMETERS -- p_service_target_name: The service name -- p_service_target_type: The service type -- -- OUT PARAMETERS -- p_avail_mode: The availability mode -- p_avail_eval_func: The availability eval function -- p_system_name: The system name -- p_system_type: The system type -- p_system_timezone: The system time zone -- p_system_members: A list of component members of the associated system -- p_key_system_members: A list of key component members of the associated system -- p_service_tests: A list of service tests -- p_service_beacons: A list of associated beacons -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- INVALID_PARAMS_ERR: Invalid parameters -- PROCEDURE GET_SVC_DTLS_FOR_AVAIL (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_avail_mode OUT VARCHAR2, p_avail_eval_func OUT VARCHAR2, p_system_name OUT VARCHAR2, p_system_type OUT VARCHAR2, p_system_timezone OUT VARCHAR2, p_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR, p_key_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR, p_service_tests OUT TESTS_CURSOR, p_assoc_beacons OUT BEACONS_CURSOR) IS l_svc_id RAW(16):= NULL; l_sys_id RAW(16):= NULL; l_service_timezone mgmt_targets.timezone_region%TYPE; l_service_props PROPS_CURSOR; l_txn_count NUMBER:= 0; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_dtls_for_avail: Enter', G_MODULE_NAME) ; END IF ; l_svc_id := mgmt_service.get_service_guid( p_service_target_name, p_service_target_type); get_svc_assoc_sys_comp_details( p_service_target_name, p_service_target_type, l_service_timezone, l_service_props, p_system_name, p_system_type, p_system_timezone, p_system_members, p_key_system_members, p_avail_mode, p_avail_eval_func); get_tests (p_service_target_name, p_service_target_type, p_service_tests); get_assoc_beacons (p_service_target_name, p_service_target_type, p_assoc_beacons); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_dtls_for_avail: Exit', G_MODULE_NAME) ; END IF ; END GET_SVC_DTLS_FOR_AVAIL; PROCEDURE SETUP_KEY_TESTS_AND_BCNS (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2) IS CURSOR key_tests(tgt_id RAW) IS SELECT txn_guid, name, txn_type, is_representative, state FROM mgmt_bcn_Txn_defn WHERE target_guid = tgt_id; CURSOR key_beacons(tgt_id RAW) IS SELECT tgt.target_name as bcn_name FROM mgmt_bcn_target bcn, mgmt_targets tgt WHERE bcn.target_guid = tgt_id AND bcn.beacon_target_guid = tgt.target_guid AND bcn.participates_avail = 'Y' AND tgt.target_type = mgmt_global.G_BEACON_TARGET_TYPE; l_service_guid RAW(16); l_metric_guid RAW(16); l_key_test_list MGMT_GENSVC_AV_TEST_ARRAY := null; l_key_bcn_list MGMT_GENSVC_TGT_NAME_ARRAY := null; l_key_test MGMT_GENSVC_AV_TEST; l_met_name VARCHAR2(64) := null; l_met_col VARCHAR2(64) := null; l_state NUMBER; l_avail NUMBER; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('setup_test_based_avail: Enter', G_MODULE_NAME) ; END IF; IF ( p_service_target_name IS NULL ) OR (p_service_target_type IS NULL ) THEN RETURN; END IF; --NOTE to GAN, STREDDY - regressions should use service targets for sub services -- this call should check for service guid not just for target guid. l_service_guid := mgmt_target.get_target_guid(p_service_target_name, p_service_target_type); l_metric_guid := mgmt_target.get_metric_guid(p_service_target_type, MGMT_GLOBAL.G_AVAIL_METRIC_NAME, MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN); IF ( l_metric_guid IS NULL ) THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, MGMT_GLOBAL.INVALID_TARGET_ERR_M || p_service_target_name || ', ' || p_service_target_type || '. '||MGMT_GLOBAL.G_AVAIL_METRIC_NAME || '/' ||MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN || ' metric not found. '); END IF; --Register key tests and beacons with availability framework --prepare key beacon list FOR sub_rec in key_beacons(l_service_guid) LOOP IF ( l_key_bcn_list IS NULL ) THEN l_key_bcn_list := MGMT_GENSVC_TGT_NAME_ARRAY(); END IF; l_key_bcn_list.extend; l_key_bcn_list(key_beacons%ROWCOUNT) := sub_rec.bcn_name; END LOOP; --prepare key test list FOR sub_rec in key_tests(l_service_guid) LOOP IF ( l_key_test_list IS NULL ) THEN l_key_test_list := MGMT_GENSVC_AV_TEST_ARRAY(); END IF; l_key_test_list.extend; mgmt_test_metadata_read.get_avail_metric(sub_rec.txn_type, l_met_name, l_met_col ); IF ( sub_rec.state = 'M' ) THEN l_state := 1; ELSE l_state := 0; END IF; IF ( sub_rec.is_representative = 'Y' ) THEN l_avail := 1; ELSE l_avail := 0; END IF; l_key_test := MGMT_GENSVC_AV_TEST(sub_rec.name, sub_rec.txn_type, l_met_name, l_met_col, l_avail,l_state ); l_key_test_list(key_tests%ROWCOUNT) := l_key_test; END LOOP; -- setup key tests mgmt_gensvc_avail.set_tests( p_service_target_name, p_service_target_type, l_key_test_list); -- setup key beacons mgmt_gensvc_avail.set_beacons(p_service_target_name, p_service_target_type, l_key_bcn_list) ; END SETUP_KEY_TESTS_AND_BCNS; -- -- PROCEDURE : SET_SVC_DTLS_FOR_AVAIL -- -- PURPOSE -- Update the availability status of the given service, -- and replace the key tests and the key beacons with the latest set. -- -- IN PARAMETERS -- p_service_target_name: The service name -- p_service_target_type: The service type -- p_avail_mode: The availability mode -- p_avail_eval_func: The availability eval function -- p_service_key_comps: A list of key system component name/types associated with the service -- p_service_key_tests: A list of key test IDs associated with the service -- p_service_key_beacons: A list of key beacon IDs associated with the service -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- INVALID_PARAMS_ERR: Invalid parameters -- PROCEDURE SET_SVC_DTLS_FOR_AVAIL (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_avail_mode IN VARCHAR2, p_avail_eval_func IN VARCHAR2, p_service_key_comps IN SMP_EMD_NVPAIR_ARRAY, p_service_key_tests IN SMP_EMD_STRING_ARRAY, p_service_key_beacons IN SMP_EMD_STRING_ARRAY) IS l_svc_id RAW(16):= NULL; l_system_name mgmt_targets.target_name%TYPE; l_system_type mgmt_targets.target_type%TYPE; l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); l_old_avail_mode VARCHAR2(256) := null; l_old_avail_eval_func VARCHAR2(256) := null; BEGIN l_svc_id := mgmt_service.get_service_guid ( p_service_target_name, p_service_target_type); IF( MGMT_USER.has_priv ( l_current_user, MGMT_USER.OPERATOR_TARGET, l_svc_id) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'GENSVC.SET_SVC_DTLS_FOR_AVAIL: The current user (' || l_current_user || ') does not have sufficient privileges to perform this operation'); END IF; UPDATE mgmt_bcn_txn_defn SET is_representative='N' WHERE target_guid = l_svc_id; UPDATE mgmt_bcn_txn_defn SET is_representative='Y' WHERE target_guid = l_svc_id AND txn_guid IN (SELECT * FROM TABLE(CAST(p_service_key_tests AS SMP_EMD_STRING_ARRAY))); UPDATE mgmt_bcn_target SET participates_avail='N' WHERE target_guid = l_svc_id; UPDATE mgmt_bcn_target SET participates_avail='Y' WHERE target_guid = l_svc_id AND beacon_target_guid IN (SELECT * FROM TABLE(CAST(p_service_key_beacons AS SMP_EMD_STRING_ARRAY))); BEGIN SELECT tgt.target_name, tgt.target_type INTO l_system_name, l_system_type FROM mgmt_targets tgt, mgmt_target_assocs assoc WHERE assoc.source_target_guid = l_svc_id AND assoc.assoc_guid = mgmt_assoc.g_runs_on_guid AND assoc.assoc_target_guid = tgt.target_guid; mgmt_service.set_system(p_service_target_name, p_service_target_type, l_system_name, l_system_type, p_service_key_comps); EXCEPTION WHEN NO_DATA_FOUND THEN --system may not be selected for the service NULL; END; get_avail_dtls ( p_service_target_name, p_service_target_type, l_old_avail_mode, l_old_avail_eval_func); IF (p_avail_mode = mgmt_service.g_test_based_avail_comp ) THEN IF (l_old_avail_mode = mgmt_service.g_system_based_avail_comp) THEN mgmt_service.remove_system_based_avail ( p_service_target_name, p_service_target_type); END IF; mgmt_service.setup_test_based_avail ( p_service_target_name, p_service_target_type, p_avail_eval_func); ELSIF (p_avail_mode = mgmt_service.g_system_based_avail_comp ) THEN IF (l_old_avail_mode = mgmt_service.g_test_based_avail_comp) THEN mgmt_service.remove_test_based_avail ( p_service_target_name, p_service_target_type ); END IF; mgmt_service.setup_system_based_avail ( p_service_target_name, p_service_target_type, p_avail_eval_func); setup_key_tests_and_bcns (p_service_target_name, p_service_target_type); END IF; END SET_SVC_DTLS_FOR_AVAIL; PROCEDURE SET_TESTS_FOR_SERVICE ( p_tests IN MGMT_GENSVC_TXN_INST_ARRAY, p_new_version OUT INTEGER, p_result OUT INTEGER, p_err_desc OUT VARCHAR2) IS l_test MGMT_GENSVC_TXN_INST:=null; l_beacon_list MGMT_BCN_ARRAY:=null; BEGIN FOR element IN 1..p_tests.count LOOP l_test := p_tests(element); emd_bcntxn.emd_bcn_txn_modify(l_test.tgt_id, l_test.txn_defn_with_props, l_test.steps_defn_with_props, l_test.rm_steps, l_test.stepgroups_defn, l_test.rm_stepgroups, l_test.txn_thresholds, l_test.step_thresholds, l_test.stepgroup_thresholds, p_new_version, l_beacon_list, p_result, p_err_desc); END LOOP; END SET_TESTS_FOR_SERVICE; -- -- PROCEDURE: GET_SVC_DTLS_FOR_RCA_CONFIG -- -- PURPOSE -- Get the RCA Configuration status of the given service. -- -- IN PARAMETERS -- p_service_target_name: The service name -- p_service_target_type: The service type -- p_interactive_rca: Whether to perform RCA when service fails -- if 0 then box is checked, if 1 then box is unchecked -- p_system_name: The name of the associated system -- p_system_type: The type of the associated system -- p_key_system_members: A list of key component members of the associated system -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if the service does not exist -- INVALID_PARAMS_ERR: invalid parameters -- PROCEDURE GET_SVC_DTLS_FOR_RCA_CONFIG (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_interactive_rca OUT NUMBER, p_system_name OUT VARCHAR2, p_system_type OUT VARCHAR2, p_key_system_members OUT SERVICE_ASSOC_SYS_MEM_CURSOR) IS l_svc_id RAW(16):= NULL; l_sys_id RAW(16):= NULL; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_dtls_for_rca_config: Enter', G_MODULE_NAME) ; END IF ; --retrieving service id l_svc_id := mgmt_service.get_service_guid( p_service_target_name, p_service_target_type); mgmt_rca.get_interactive_test(p_service_target_name, p_service_target_type, p_interactive_rca); --retrieving system details BEGIN SELECT tgt.target_guid, tgt.target_name, tgt.target_type INTO l_sys_id, p_system_name, p_system_type FROM mgmt_targets tgt, mgmt_target_assocs assoc WHERE assoc.source_target_guid = l_svc_id AND assoc.assoc_guid = mgmt_assoc.g_runs_on_guid AND assoc.assoc_target_guid = tgt.target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN --system may not be selected for the service NULL; END; --retrieving key system components p_key_system_members := get_key_comps_info(l_svc_id); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_svc_dtls_for_rca_config: Exit', G_MODULE_NAME) ; END IF ; END GET_SVC_DTLS_FOR_RCA_CONFIG; -- -- PROCEDURE: SET_LOCAL_BEACON -- -- PURPOSE -- Set the specified beacon as the local beacon of the given service. -- -- IN PARAMETERS -- p_service_target_name: The service name -- p_service_target_type: The service type -- p_local_beacon_name: The name of the local beacon -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if the service does not exist -- PROCEDURE SET_LOCAL_BEACON (p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_local_beacon_name IN VARCHAR2) IS l_svc_id MGMT_TARGETS.target_guid%TYPE:= NULL; l_bcn_id MGMT_TARGETS.target_guid%TYPE:= NULL; l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); BEGIN l_svc_id := mgmt_service.get_service_guid( p_service_target_name, p_service_target_type); IF( MGMT_USER.has_priv ( l_current_user, MGMT_USER.OPERATOR_TARGET, l_svc_id) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'GENSVC.SET_LOCAL_BEACON: The current user (' || l_current_user || ') does not have sufficient privileges to perform this operation'); END IF; --set all beacons as non local beacon UPDATE mgmt_bcn_target bcn SET is_local = 'N' WHERE bcn.target_guid = l_svc_id; --set the selected beacon as the local beacon IF (p_local_beacon_name IS NOT NULL) THEN l_bcn_id := mgmt_target.get_target_guid (p_local_beacon_name, MGMT_GLOBAL.G_BEACON_TARGET_TYPE); IF (l_bcn_id IS NOT NULL) THEN UPDATE mgmt_bcn_target bcn SET is_local = 'Y' WHERE bcn.target_guid = l_svc_id AND bcn.beacon_target_guid = l_bcn_id; END IF; END IF; END SET_LOCAL_BEACON; -- -- PROCEDURE: GET_INFO_FOR_SYS_METRIC_PROM -- -- PURPOSE -- Retrieve metrics to render the System Based Metric Promotion -- metric drop down and the key selector popup. -- -- IN PARAMETERS -- p_target_names: A list of targets -- p_target_type: The target type -- p_metric_category: The metric category, Performance, Usage, Business etc -- p_show_enabled_only: 'Y' means show enabled collections only. 'N' means show all collections. -- p_metrics_columns: Information to render the metric columns -- p_key_metrics_columns: Information to render the key metric columns -- -- EXCEPTION -- PROCEDURE GET_INFO_FOR_SYS_METRIC_PROM ( p_target_names IN SMP_EMD_STRING_ARRAY, p_target_type IN VARCHAR2, p_metric_category IN VARCHAR2, p_show_enabled_only IN VARCHAR2, p_metrics_columns OUT METRIC_COLUMNS_CURSOR, p_key_metrics_columns OUT METRIC_KEY_COLUMNS_CURSOR) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_info_for_sys_metric_prom: Enter', G_MODULE_NAME) ; END IF ; OPEN p_metrics_columns FOR SELECT cm.metric_name, cm.metric_column, cm.metric_label, cm.metric_label_nlsid, cm.column_label, cm.column_label_nlsid, cm.key_column, cm.is_transposed, decode(cm.is_transposed, 0, null, 1, c.coll_name), cm.remote, t.target_name, cm.is_renderable FROM mgmt_targets t, mgmt_metrics cm, mgmt_metrics tm, mgmt_collections c, mgmt_collection_metric_tasks cmt, mgmt_category_map map WHERE (t.target_name IN (SELECT * FROM TABLE(CAST(p_target_names AS SMP_EMD_STRING_ARRAY)))) AND (t.target_type = p_target_type) AND (t.target_type = cm.target_type) AND (t.target_type = tm.target_type) AND (t.type_meta_ver = cm.type_meta_ver) AND (t.type_meta_ver = tm.type_meta_ver) AND (t.category_prop_1 = cm.category_prop_1 OR cm.category_prop_1 = ' ') AND (t.category_prop_2 = cm.category_prop_2 OR cm.category_prop_2 = ' ') AND (t.category_prop_3 = cm.category_prop_3 OR cm.category_prop_3 = ' ') AND (t.category_prop_4 = cm.category_prop_4 OR cm.category_prop_4 = ' ') AND (t.category_prop_5 = cm.category_prop_5 OR cm.category_prop_5 = ' ') AND (tm.category_prop_1 = cm.category_prop_1) AND (tm.category_prop_2 = cm.category_prop_2) AND (tm.category_prop_3 = cm.category_prop_3) AND (tm.category_prop_4 = cm.category_prop_4) AND (tm.category_prop_5 = cm.category_prop_5) AND (tm.metric_name = cm.metric_name) AND (tm.metric_column IS NULL OR tm.metric_column = ' ') AND (cm.metric_column IS NOT NULL AND cm.metric_column != ' ') AND (cm.metric_label != ' ') AND (cm.column_label != ' ') AND (tm.metric_guid = cmt.metric_guid) AND (cm.metric_type = mgmt_global.G_METRIC_TYPE_NUMBER) AND (cm.key_order = 0) /* the metric is not a key column */ AND (cm.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT) AND (c.coll_name = cmt.coll_name) AND (c.is_enabled = 1 OR p_show_enabled_only = 'N') /* collection is enabled or don't care */ AND (t.target_guid = c.object_guid) AND (t.target_guid = cmt.target_guid) AND ((p_metric_category != MGMT_GLOBAL.G_SERVICE_CATEGORY_BUSINESS) OR ((map.object_guid = cm.metric_guid) AND (map.target_type = cm.target_type) AND (map.type_meta_ver = cm.type_meta_ver) AND (map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC) AND (map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE) AND (map.category_name = MGMT_GLOBAL.G_SERVICE_CATEGORY_BUSINESS)) ); OPEN p_key_metrics_columns FOR SELECT distinct m.metric_name, m.metric_column, m.column_label, m.column_label_nlsid, m.is_renderable FROM mgmt_targets t, mgmt_metrics m WHERE t.target_name IN (SELECT * FROM TABLE(CAST(p_target_names AS SMP_EMD_STRING_ARRAY))) AND t.target_type = p_target_type AND t.target_type = m.target_type AND t.type_meta_ver = m.type_meta_ver AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ') AND (m.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT) AND (m.metric_column IS NOT NULL OR m.metric_column != '') AND (m.key_order != 0); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_info_for_sys_metric_prom: Exit', G_MODULE_NAME) ; END IF ; END GET_INFO_FOR_SYS_METRIC_PROM; -- -- PROCEDURE: GET_METRIC_INFO_FOR_PROM_CLI -- -- PURPOSE -- Retrieve metrics to validate the System Based Metric Promotion -- in CLI -- -- IN PARAMETERS -- p_target_names: A list of targets -- p_target_type: The target type -- p_metric_column: The metric column -- p_metric_name: The metric name -- p_metric_key_value: The metric key value (if transposed) -- p_show_enabled_only: "Y" means return only metrics that are currently collected. -- "N" means return all metrics registered for collection. -- p_metrics_columns: Information to render the metric columns -- -- EXCEPTION -- PROCEDURE GET_METRIC_INFO_FOR_PROM_CLI( p_target_names IN SMP_EMD_STRING_ARRAY, p_target_type IN VARCHAR2, p_metric_column IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_category IN VARCHAR2, p_show_enabled_only IN VARCHAR2, p_metrics_columns OUT METRIC_COLUMNS_CURSOR) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_METRIC_INFO_FOR_PROM_CLI: Enter', G_MODULE_NAME) ; END IF ; OPEN p_metrics_columns FOR SELECT distinct cm.metric_name, cm.metric_column, cm.key_column, cm.is_transposed, cm.remote, t.target_name FROM mgmt_targets t, mgmt_metrics cm, mgmt_metrics tm, mgmt_collections c, mgmt_collection_metric_tasks cmt, mgmt_category_map map WHERE (t.target_name IN (SELECT * FROM TABLE(CAST(p_target_names AS SMP_EMD_STRING_ARRAY)))) AND (t.target_type = p_target_type) AND (t.target_type = cm.target_type) AND (t.target_type = tm.target_type) AND (t.type_meta_ver = cm.type_meta_ver) AND (t.type_meta_ver = tm.type_meta_ver) AND (t.category_prop_1 = cm.category_prop_1 OR cm.category_prop_1 = ' ') AND (t.category_prop_2 = cm.category_prop_2 OR cm.category_prop_2 = ' ') AND (t.category_prop_3 = cm.category_prop_3 OR cm.category_prop_3 = ' ') AND (t.category_prop_4 = cm.category_prop_4 OR cm.category_prop_4 = ' ') AND (t.category_prop_5 = cm.category_prop_5 OR cm.category_prop_5 = ' ') AND (tm.category_prop_1 = cm.category_prop_1) AND (tm.category_prop_2 = cm.category_prop_2) AND (tm.category_prop_3 = cm.category_prop_3) AND (tm.category_prop_4 = cm.category_prop_4) AND (tm.category_prop_5 = cm.category_prop_5) AND (tm.metric_name = cm.metric_name) AND (tm.metric_column IS NULL OR tm.metric_column = ' ') AND (cm.metric_column IS NOT NULL AND cm.metric_column != ' ') AND (cm.metric_label != ' ') AND (cm.column_label != ' ') AND (cm.metric_column = p_metric_column) AND (cm.metric_name = p_metric_name OR p_metric_name IS NULL) AND (tm.metric_guid = cmt.metric_guid) AND (cm.metric_type = mgmt_global.G_METRIC_TYPE_NUMBER) AND (cm.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT) AND (cm.key_order = 0) /* the metric is not a key column */ AND (cm.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT) AND (c.coll_name = cmt.coll_name) AND (c.is_enabled = 1 OR p_show_enabled_only = 'N') /* collection is enabled or don't care */ AND (t.target_guid = c.object_guid) AND (t.target_guid = cmt.target_guid) AND ((p_metric_category != MGMT_GLOBAL.G_SERVICE_CATEGORY_BUSINESS) OR ((map.object_guid = cm.metric_guid) AND (map.target_type = cm.target_type) AND (map.type_meta_ver = cm.type_meta_ver) AND (map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC) AND (map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE) AND (map.category_name = MGMT_GLOBAL.G_SERVICE_CATEGORY_BUSINESS)) ) ORDER BY t.target_name; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('GET_METRIC_INFO_FOR_PROM_CLI: Exit', G_MODULE_NAME) ; END IF ; END GET_METRIC_INFO_FOR_PROM_CLI; -- IMPORTANT READ COMMENTS -- *************************************************************************** -- This API is being used for cleaning up the data for all leaf level services. -- This is a pre target deletion call back for all targets. This will do NOOP -- for non leaf level service targets. This will delete all pending beacon -- jobs for leaf level service targets. -- *************************************************************************** PROCEDURE DELETE_SERVICE_CALLBACK( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW) IS bcn_name MGMT_TARGETS.TARGET_NAME%TYPE; bcn_type MGMT_TARGETS.TARGET_TYPE%TYPE; bcn_guid MGMT_TARGETS.TARGET_GUID%TYPE; tgt_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_bcns BEACONS_CURSOR; l_ignored VARCHAR2(256); l_job_targets MGMT_JOB_TARGET_LIST; l_job_target_list MGMT_JOB_TARGET_LIST_ARRAY; l_job_params MGMT_JOB_PARAM_LIST; l_job_name MGMT_JOB.job_name%TYPE; l_job_id MGMT_JOB.job_id%TYPE; l_execution_ids MGMT_JOB_GUID_ARRAY; l_queue_names SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_schedule MGMT_JOB_SCHEDULE_RECORD; l_count NUMBER; l_operation_guid RAW(16); l_is_agg_svc NUMBER; BEGIN -- Retrieve the target guid of the target being deleted -- throws target_does_not_exist exception if the target -- is not a service target. BEGIN tgt_guid := mgmt_service.get_service_guid( target_name_in, target_type_in); EXCEPTION WHEN mgmt_global.target_does_not_exist THEN -- do nothing for non service targets RETURN; END; -- check if the target is an aggregate service l_is_agg_svc := mgmt_service.IS_AGGREGATE_SERVICE(target_type_in); IF ( l_is_agg_svc = 1 ) THEN -- do nothing for aggregate services RETURN; END IF; --get associated beacons for this service gensvc_view.get_bcns_for_svc(target_name_in, target_type_in, 'N', l_bcns); -- submit a collection remove job for each beacon LOOP FETCH l_bcns INTO bcn_guid, bcn_name, l_ignored, l_ignored; EXIT WHEN l_bcns%NOTFOUND; bcn_type := MGMT_GLOBAL.G_BEACON_TARGET_TYPE; -- Create the job parameters l_job_targets := MGMT_JOB_TARGET_LIST(); l_job_targets.EXTEND(1); l_job_targets(1) := MGMT_JOB_TARGET_RECORD(bcn_name, bcn_type); l_job_target_list := MGMT_JOB_TARGET_LIST_ARRAY(); l_job_target_list.EXTEND(1); l_job_target_list(1) := l_job_targets; l_operation_guid := SYS_GUID(); l_job_name := JOB_NAME_PREFIX || l_operation_guid; l_job_params := MGMT_JOB_PARAM_LIST(); l_job_params.extend(7); l_job_params(1) := MGMT_JOB_PARAM_RECORD('beacon_guid', 1, bcn_guid, null); l_job_params(2) := MGMT_JOB_PARAM_RECORD('beacon_name', 1, bcn_name, null); l_job_params(3) := MGMT_JOB_PARAM_RECORD('beacon_type', 1, bcn_type, null); l_job_params(4) := MGMT_JOB_PARAM_RECORD('target_name', 1, target_name_in, null); l_job_params(5) := MGMT_JOB_PARAM_RECORD('target_type', 1, target_type_in, null); l_job_params(6) := MGMT_JOB_PARAM_RECORD('typeOfBeaconJob', 1, 'deleteJob', null); l_job_params(7) := MGMT_JOB_PARAM_RECORD('operation_guid', 1, l_operation_guid, null); l_schedule := null; -- Create the queue if not already created l_queue_names.extend(1); l_queue_names(1) := JOB_NAME_PREFIX || tgt_guid || ' ' || bcn_guid; SELECT count(1) INTO l_count FROM MGMT_JOB_QUEUES WHERE queue_name = l_queue_names(1); IF l_count=0 THEN MGMT_JOB_ENGINE.create_job_queue(l_queue_names(1)); ELSE MGMT_JOB_ENGINE.cleanup_job_queue(l_queue_names(1), true); END IF; -- Submit the job MGMT_JOBS.submit_job( l_job_name, JOB_DESCRIPTION, 0, JOB_TYPE, l_job_target_list, l_job_params, l_schedule, -1, l_job_id, l_execution_ids, null, MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY, null, null, l_queue_names); END LOOP; -- deregister availability MGMT_GENSVC_AVAIL.DEREGISTER_AVAIL(target_name_in, target_type_in); EXCEPTION WHEN OTHERS THEN MGMT_LOG.LOG_ERROR(G_MODULE_NAME, NULL, 'Delete Service Callback Error: '||SQLERRM); END DELETE_SERVICE_CALLBACK; -- -- PROCEDURE: GET_SVC_TESTS_BCNS_DTLS -- -- Gets the service information for the tests and beacons page -- -- IN PARAMETERS -- p_service_target_name : The service name -- p_service_target_type : The service type -- -- OUT PARAMETERS -- p_service_tests : A list of service tests -- p_assoc_beacons : A list of service beacons -- p_avail_mode : The availability mode -- p_avail_eval_func : The availability eval function -- -- EXCEPTION -- TARGET_DOES_NOT_EXIST: if any sub service does not exist -- PROCEDURE GET_SVC_TESTS_BCNS_DTLS ( p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_service_tests OUT TESTS_CURSOR, p_assoc_beacons OUT BEACONS_CURSOR, p_avail_mode OUT VARCHAR2, p_avail_eval_func OUT VARCHAR2) IS BEGIN get_tests (p_service_target_name, p_service_target_type, p_service_tests); get_assoc_beacons (p_service_target_name, p_service_target_type, p_assoc_beacons); get_avail_dtls ( p_service_target_name, p_service_target_type, p_avail_mode, p_avail_eval_func); END GET_SVC_TESTS_BCNS_DTLS; PROCEDURE CREATE_SERVICE ( p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_target_display_name IN VARCHAR2, p_host_name IN VARCHAR2, p_agent_url IN VARCHAR2, p_tz_rgn IN VARCHAR2, p_svc_props IN MGMT_TARGET_PROPERTY_LIST, p_system IN VARCHAR2, p_system_type IN VARCHAR2, p_critical_components IN SMP_EMD_NVPAIR_ARRAY, p_avail_mode IN VARCHAR2, p_avail_eval_func IN VARCHAR2, p_test_defn_with_props IN MGMT_BCN_TXN_WITH_PROPS, p_steps_defn_with_props IN MGMT_BCN_STEP_WITH_PROPS_ARRAY, p_stepgroups_defn IN MGMT_BCN_STEPGROUP_ARRAY, p_test_thresholds IN MGMT_BCN_THRESHOLD_ARRAY, p_steps_thresholds IN MGMT_BCN_THRESHOLD_ARRAY, p_stepgroups_thresholds IN MGMT_BCN_THRESHOLD_ARRAY, p_use_def_test_type_thresh IN CHAR, p_assoc_bcn_list IN MGMT_BCN_ASSOC_ARRAY, p_use_default_met_prom IN CHAR, p_type_meta_ver IN VARCHAR2, p_category_prop_1 IN VARCHAR2, p_category_prop_2 IN VARCHAR2, p_category_prop_3 IN VARCHAR2, p_category_prop_4 IN VARCHAR2, p_category_prop_5 IN VARCHAR2, p_repository_only_target IN NUMBER) IS l_tgt_guid RAW(16) := NULL; l_new_txn_id VARCHAR2(32) := NULL; l_new_version INTEGER; l_bcn_list MGMT_BCN_ARRAY := NULL; BEGIN IF ( p_avail_mode = mgmt_service.g_test_based_avail_comp ) THEN mgmt_service.create_service( p_service_target_name, p_service_target_type, p_target_display_name, p_host_name, p_agent_url, p_tz_rgn, p_svc_props, p_system, p_system_type, p_critical_components, p_test_defn_with_props, p_steps_defn_with_props, p_stepgroups_defn, p_test_thresholds, p_steps_thresholds, p_stepgroups_thresholds, p_use_def_test_type_thresh, p_assoc_bcn_list, p_use_default_met_prom, p_type_meta_ver, p_category_prop_1, p_category_prop_2, p_category_prop_3, p_category_prop_4, p_category_prop_5, p_repository_only_target); -- By default availability function is set to AND in test based service creation. -- since we need to support emcli creating test based service with multiple -- tests and OR function. Additional tests will be added later. IF ( p_avail_eval_func = mgmt_severity_helper.G_OR_AVAIL_EVAL_PROC_NAME ) THEN mgmt_service.setup_test_based_avail ( p_service_target_name, p_service_target_type, p_avail_eval_func); END IF; ELSIF ( p_avail_mode = mgmt_service.g_system_based_avail_comp ) THEN mgmt_service.create_service( p_service_target_name, p_service_target_type, p_target_display_name, p_host_name, p_agent_url, p_tz_rgn, p_svc_props, p_system, p_system_type, p_critical_components, p_avail_eval_func, p_type_meta_ver, p_category_prop_1, p_category_prop_2, p_category_prop_3, p_category_prop_4, p_category_prop_5, p_repository_only_target); IF (p_test_defn_with_props IS NOT NULL) THEN SELECT target_guid INTO l_tgt_guid FROM mgmt_targets WHERE target_name=p_service_target_name AND target_type=p_service_target_type; -- Create Service Test emd_bcntxn.emd_bcn_txn_create( RAWTOHEX(l_tgt_guid), p_test_defn_with_props, p_steps_defn_with_props, p_stepgroups_defn, p_test_thresholds, p_steps_thresholds, p_stepgroups_thresholds, 'Y', l_new_txn_id, l_new_version, l_bcn_list); END IF; IF (p_assoc_bcn_list IS NOT NULL) THEN -- Associate Beacons emd_bcntxn.emd_bcn_associate_beacons( p_service_target_name, p_service_target_type, p_assoc_bcn_list ); END IF; END IF; END CREATE_SERVICE; -- PROCEDURE: GET_TEST_DEF_THRESH -- -- PURPOSE: Retrives an array of default test thresholds for a test. -- Currently, this method is only called in test based service creation, -- if the 'p_use_def_test_thresh' parameter is 'Y' and there are no default test thresholds. PROCEDURE GET_TEST_DEF_THRESH ( p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_test_name IN VARCHAR2, p_test_type IN VARCHAR2, p_thresholds OUT MGMT_BCN_THRESHOLD_ARRAY) IS l_svc_id MGMT_TARGETS.target_guid%TYPE; l_thresholds mgmt_test_thresholds_arr; l_threshold mgmt_test_thresholds_obj; l_type_meta_ver MGMT_TARGETS.type_meta_ver%TYPE; l_metric_name MGMT_METRICS.metric_name%TYPE; l_metric_column MGMT_METRICS.metric_column%TYPE; l_operator INTEGER; l_result INTEGER; l_err_desc VARCHAR2(256); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_def_thresh: Enter', G_MODULE_NAME) ; END IF ; l_svc_id := mgmt_service.get_service_guid( p_service_target_name, p_service_target_type); -- get the type meta ver BEGIN SELECT type_meta_ver INTO l_type_meta_ver FROM mgmt_targets WHERE target_name = p_service_target_name AND target_type = p_service_target_type; EXCEPTION WHEN OTHERS THEN RETURN; END; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_def_thresh: type_meta_ver: ' || l_type_meta_ver, G_MODULE_NAME) ; END IF ; -- for each test, read the default threshold information. mgmt_test_metadata_read.read_default_thresholds(p_test_type, l_thresholds, l_result, l_err_desc); IF l_thresholds IS NULL THEN RETURN; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_def_thresh: l_thresholds.count: '|| l_thresholds.count, G_MODULE_NAME) ; END IF ; p_thresholds := MGMT_BCN_THRESHOLD_ARRAY(); FOR i IN l_thresholds.FIRST .. l_thresholds.LAST LOOP l_threshold := l_thresholds(i); BEGIN SELECT m.metric_name, m.metric_column INTO l_metric_name, l_metric_column FROM mgmt_metrics m, mgmt_targets t WHERE t.target_name = p_service_target_name AND t.target_type = p_service_target_type AND t.type_meta_ver = l_type_meta_ver AND m.target_type = p_service_target_type AND m.type_meta_ver = l_type_meta_ver AND m.metric_name = l_threshold.metric_name AND m.metric_column = l_threshold.metric_column AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.BAD_THRESHOLD_ERR, MGMT_GLOBAL.BAD_THRESHOLD_ERR_M || p_service_target_type || '/' || l_type_meta_ver || '/' || p_test_type || '/' || l_threshold.metric_name || '/' || l_threshold.metric_column); END; -- operator decoding IF l_threshold.operator = 'EQ' THEN l_operator := mgmt_global.G_THRESHOLD_EQ; ELSIF l_threshold.operator = 'GE' THEN l_operator := mgmt_global.G_THRESHOLD_GE; ELSIF l_threshold.operator = 'GT' THEN l_operator := mgmt_global.G_THRESHOLD_GT; ELSIF l_threshold.operator = 'LE' THEN l_operator := mgmt_global.G_THRESHOLD_LE; ELSIF l_threshold.operator = 'LT' THEN l_operator := mgmt_global.G_THRESHOLD_LT; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_def_thresh: l_operator='|| l_operator, G_MODULE_NAME) ; END IF ; p_thresholds.extend(); p_thresholds(p_thresholds.count) := MGMT_BCN_THRESHOLD(MGMT_BCN_THRESHOLD_KEY(NULL,p_test_name,NULL,NULL,NULL, l_threshold.metric_name, l_threshold.metric_column), TO_CHAR(l_threshold.warning), l_operator, TO_CHAR(l_threshold.critical), l_operator, l_threshold.num_occurrences); END LOOP; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_def_thresh: Exit', G_MODULE_NAME) ; END IF ; END GET_TEST_DEF_THRESH; -- PROCEDURE: GET_TEST_DEF_PROPS -- -- PURPOSE: Retrieves an array of default test properties for a test. -- If there are no properties with default value, the corresponding array will be null. -- If there are already properties in the corresponding array, default values will be appended. -- -- Note: this method is not to be used for creating transaction properties in template mode. PROCEDURE GET_TEST_DEF_PROPS ( p_test_type IN VARCHAR2, p_def_test_props OUT MGMT_BCN_NVPAIR_ARRAY, p_def_step_props OUT MGMT_BCN_NVPAIR_ARRAY) IS l_test mgmt_test_metadata_obj; l_test_quals mgmt_test_qualifier_arr; l_groups mgmt_test_prop_group_arr; l_properties mgmt_test_prop_arr; l_prop_quals mgmt_test_prop_qualifier_arr; l_choices mgmt_test_choice_arr; l_levels mgmt_test_prop_level_arr; l_level mgmt_test_prop_level_obj; l_property mgmt_test_prop_obj; l_prop_value mgmt_bcn_nvpair; l_result INTEGER; l_err_desc VARCHAR2(256); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_test_def_props: Enter', G_MODULE_NAME) ; END IF; MGMT_TEST_METADATA_READ.READ_TEST_PROPERTIES_ALL (p_test_type, l_test, l_test_quals, l_groups, l_properties, l_prop_quals, l_choices, l_levels, l_result, l_err_desc); IF (l_properties IS NULL OR l_levels IS NULL) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_test_def_props: No properties or levels read, Exit', G_MODULE_NAME) ; END IF; RETURN; END IF; -- go through each property, level combination FOR i IN l_levels.FIRST .. l_levels.LAST LOOP l_level := l_levels(i); IF (l_level.LEVEL_NAME = 'TXN' OR l_level.LEVEL_NAME = 'STEP') THEN -- find the matching property FOR j IN l_properties.FIRST .. l_properties.LAST LOOP l_property := l_properties(j); IF (l_level.property = l_property.property_name) THEN l_prop_value := NULL; -- currently we don't support other kind of data types such as -- date_type and char_type, boolean_type -- unknown type and string type IF ((l_property.property_type = 0 OR l_property.property_type = 1) AND l_property.default_string_value IS NOT NULL) THEN l_prop_value := MGMT_BCN_NVPAIR(l_property.property_name, 0, l_property.default_string_value, NULL, NULL, NULL, l_property.property_type, l_property.encrypt, 'N'); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_test_def_props: ' || l_property.property_name || ',' || l_property.property_type || ',' || l_property.default_string_value, G_MODULE_NAME) ; END IF ; -- integer and float type ELSIF ((l_property.property_type = 2 OR l_property.property_type = 3) AND l_property.default_numeric_value IS NOT NULL) THEN l_prop_value := MGMT_BCN_NVPAIR(l_property.property_name, 0, NULL, l_property.default_numeric_value, NULL, NULL, l_property.property_type, l_property.encrypt, 'N'); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_test_def_props: ' || l_property.property_name || ',' || l_property.property_type || ',' || l_property.default_numeric_value, G_MODULE_NAME) ; END IF ; END IF; IF (l_prop_value IS NOT NULL) THEN IF (l_level.LEVEL_NAME = 'TXN') THEN -- append to the test level properties IF (p_def_test_props IS NULL) THEN p_def_test_props := MGMT_BCN_NVPAIR_ARRAY(); END IF; p_def_test_props.extend(); p_def_test_props(p_def_test_props.count) := l_prop_value; ELSIF (l_level.LEVEL_NAME = 'STEP') THEN -- append to the step level properties IF (p_def_step_props IS NULL) THEN p_def_step_props := MGMT_BCN_NVPAIR_ARRAY(); END IF; p_def_step_props.extend(); p_def_step_props(p_def_step_props.count) := l_prop_value; END IF; END IF; END IF; END LOOP; -- loop over properties END IF; END LOOP; -- loop over levels IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_test_def_props: Exit', G_MODULE_NAME) ; END IF ; END GET_TEST_DEF_PROPS; -- PROCEDURE: ADD_TEST_BASED_DEF_PROM -- -- PURPOSE: Adds test based default promotion to a service. -- Currently, this method is only called in test based service creation, -- if the 'p_use_default_met_prom' parameter is 'Y' and there are associated tests and beacons. PROCEDURE ADD_TEST_BASED_DEF_PROM ( p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2, p_test_name IN VARCHAR2, p_test_type IN VARCHAR2) IS l_promotions MGMT_TEST_PROMOTION_ARR; l_promotion MGMT_TEST_PROMOTION_OBJ; l_result INTEGER; l_err_desc VARCHAR2(256); l_source_metric MGMT_METRIC_INSTANCE; l_dep_test_metric_key_list MGMT_BCN_THRESHOLD_KEY_ARRAY; l_metric_deps MGMT_SVC_AGG_METRIC_DEP; l_added_metrics MGMT_SVC_AGG_MET_THR_ARRAY; l_agg_metric_key_value MGMT_METRICS.key_column%TYPE; l_beacon_guids SMP_EMD_STRING_ARRAY; l_test_guid MGMT_BCN_TXN_DEFN.txn_guid%TYPE; l_type_meta_ver MGMT_TARGETS.TYPE_META_VER%TYPE; l_operator INTEGER; l_def_chart_perf_metric_name MGMT_METRICS.metric_name%TYPE; l_def_chart_perf_metric_column MGMT_METRICS.metric_column%TYPE; l_def_chart_perf_key_value MGMT_METRICS.key_column%TYPE; l_eval_func VARCHAR2(100); l_metric_name MGMT_METRICS.metric_name%TYPE; l_metric_column MGMT_METRICS.metric_column%TYPE; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: Enter', G_MODULE_NAME) ; END IF ; -- get the type meta ver BEGIN SELECT type_meta_ver INTO l_type_meta_ver FROM mgmt_targets WHERE target_name = p_service_target_name AND target_type = p_service_target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR, MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M || p_service_target_name|| ' (' || p_service_target_type|| ')'); END; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: type_meta_ver: ' || l_type_meta_ver, G_MODULE_NAME) ; END IF ; -- get beacon guids SELECT bcn.beacon_target_guid BULK COLLECT INTO l_beacon_guids FROM mgmt_bcn_target bcn, mgmt_targets tgt WHERE tgt.target_name = p_service_target_name AND tgt.target_type = p_service_target_type AND tgt.target_guid = bcn.target_guid; IF l_beacon_guids IS NULL OR l_beacon_guids.count = 0 THEN RETURN; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: l_beacon_guids.count: ' || l_beacon_guids.count, G_MODULE_NAME) ; END IF ; BEGIN SELECT txn_guid INTO l_test_guid FROM mgmt_bcn_txn_defn txn, mgmt_targets tgt WHERE tgt.target_name = p_service_target_name AND tgt.target_type = p_service_target_type AND tgt.target_guid = txn.target_guid AND txn.name = p_test_name AND txn.txn_type = p_test_type; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; -- for each test, read the default promotion information. mgmt_test_metadata_read.read_default_promotions(p_test_type, l_promotions, l_result, l_err_desc); IF l_promotions IS NULL THEN RETURN; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: l_promotions.count: '|| l_promotions.count, G_MODULE_NAME) ; END IF ; l_added_metrics := MGMT_SVC_AGG_MET_THR_ARRAY(); FOR i IN l_promotions.FIRST .. l_promotions.LAST LOOP l_promotion := l_promotions(i); BEGIN SELECT m.metric_name, m.metric_column INTO l_metric_name, l_metric_column FROM mgmt_metrics m, mgmt_targets t WHERE t.target_name = p_service_target_name AND t.target_type = p_service_target_type AND t.type_meta_ver = l_type_meta_ver AND m.target_type = p_service_target_type AND m.type_meta_ver = l_type_meta_ver AND m.metric_name = l_promotion.dest_metric_name AND m.metric_column = l_promotion.dest_metric_column AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_METRIC_PROM_ERR, MGMT_GLOBAL.INVALID_METRIC_PROM_ERR_M || p_service_target_type || '/' || l_type_meta_ver || '/' || p_test_type || '/' || l_promotion.dest_metric_name || '/' || l_promotion.dest_metric_column); END; l_dep_test_metric_key_list := MGMT_BCN_THRESHOLD_KEY_ARRAY(); FOR j IN l_beacon_guids.FIRST .. l_beacon_guids.LAST LOOP l_dep_test_metric_key_list.extend(); l_dep_test_metric_key_list(l_dep_test_metric_key_list.count) := MGMT_BCN_THRESHOLD_KEY ( l_beacon_guids(j), l_test_guid, NULL, -- key part 3 NULL, -- key part 4 NULL, -- key part 5 l_promotion.src_metric_name, l_promotion.src_metric_column); END LOOP; -- operator decoding IF l_promotion.operator = 'EQ' THEN l_operator := mgmt_global.G_THRESHOLD_EQ; ELSIF l_promotion.operator = 'GE' THEN l_operator := mgmt_global.G_THRESHOLD_GE; ELSIF l_promotion.operator = 'GT' THEN l_operator := mgmt_global.G_THRESHOLD_GT; ELSIF l_promotion.operator = 'LE' THEN l_operator := mgmt_global.G_THRESHOLD_LE; ELSIF l_promotion.operator = 'LT' THEN l_operator := mgmt_global.G_THRESHOLD_LT; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: l_operator: '|| l_operator, G_MODULE_NAME) ; END IF ; -- select the display name for the key value. BEGIN SELECT short_name INTO l_agg_metric_key_value FROM mgmt_metrics WHERE metric_name = l_promotion.src_metric_name AND metric_column = l_promotion.src_metric_column AND target_type = p_service_target_type AND type_meta_ver = l_type_meta_ver; EXCEPTION WHEN NO_DATA_FOUND THEN l_agg_metric_key_value := l_promotion.src_metric_column; END; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: l_agg_metric_key_value: ' || l_agg_metric_key_value, G_MODULE_NAME) ; END IF; l_source_metric := MGMT_METRIC_INSTANCE ( p_service_target_name, p_service_target_type, l_promotion.dest_metric_name, l_promotion.dest_metric_column, l_agg_metric_key_value); -- set the metric as default_chart IF l_promotion.DEFAULT_CHART = 'Y' THEN l_def_chart_perf_metric_name := l_promotion.dest_metric_name; l_def_chart_perf_metric_column := l_promotion.dest_metric_column; l_def_chart_perf_key_value := l_agg_metric_key_value; END IF; -- decode eval function IF l_promotion.eval_func = 'EVAL_MIN_VALUE' THEN l_eval_func := MGMT_METRIC_HELPER.G_EVAL_MIN_VALUE_FUNC; ELSIF l_promotion.eval_func = 'EVAL_MAX_VALUE' THEN l_eval_func := MGMT_METRIC_HELPER.G_EVAL_MAX_VALUE_FUNC; ELSIF l_promotion.eval_func = 'EVAL_AVG_VALUE' THEN l_eval_func := MGMT_METRIC_HELPER.G_EVAL_AVG_VALUE_FUNC; ELSIF l_promotion.eval_func = 'EVAL_SUM_VALUE' THEN l_eval_func := MGMT_METRIC_HELPER.G_EVAL_SUM_VALUE_FUNC; ELSIF l_promotion.eval_func = 'EVAL_STDDEV_VALUE' THEN l_eval_func := MGMT_METRIC_HELPER.G_EVAL_STDDEV_VALUE_FUNC; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: l_eval_func: ' || l_eval_func, G_MODULE_NAME) ; END IF ; -- create a metric dependency l_metric_deps := MGMT_SVC_AGG_METRIC_DEP (2, -- test based NULL, -- l_dep_sys_metric_list l_dep_test_metric_key_list, 1, -- TXN l_eval_func); l_added_metrics.extend(); l_added_metrics(l_added_metrics.count) := MGMT_SVC_AGG_MET_THRESH_INST ( l_source_metric, mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE, l_metric_deps, l_promotion.critical, l_promotion.warning, l_operator); END LOOP; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: l_added_metrics.count: ' || l_added_metrics.count, G_MODULE_NAME) ; END IF ; -- add all test based metric promotion gensvc.set_svc_agg_metric_thresholds( p_service_target_name, p_service_target_type, l_added_metrics, NULL, NULL, l_def_chart_perf_metric_name, l_def_chart_perf_metric_column, l_def_chart_perf_key_value, NULL, NULL, NULL); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_test_based_def_prom: Exit', G_MODULE_NAME) ; END IF ; END ADD_TEST_BASED_DEF_PROM; PROCEDURE POST_DELETE_SERVICE_CALLBACK( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW) IS BEGIN DELETE FROM MGMT_BCN_TARGET WHERE target_guid = target_guid_in; END POST_DELETE_SERVICE_CALLBACK; -- -- PROCEDURE: BCN_TYPE_METAVER_UPD_CALLBACK -- -- Callback invoked by repository framework whenever a beacon type -- meta version is updated. If the beacon is updated from 1.0 or 2.0 -- to later versions, this callback will submit a sync job on all -- associated service targets. -- -- IN: tgt_type_meta_ver_obj -- PROCEDURE BCN_TYPE_METAVER_UPD_CALLBACK ( tgt_type_meta_ver_obj IN MGMT_TARGET_META_VER_CBK_OBJ ) IS CURSOR affected_svcs(bcn_guid RAW) IS SELECT t.target_name, t.target_type FROM MGMT_BCN_TARGET b, MGMT_TARGETS t WHERE b.beacon_target_guid = bcn_guid AND b.target_guid <> b.beacon_target_guid AND t.target_guid = b.target_guid; l_beacon_name mgmt_targets.target_name%TYPE := NULL; l_job_id mgmt_job.job_id%TYPE := NULL; BEGIN IF ( tgt_type_meta_ver_obj IS NULL ) OR ( tgt_type_meta_ver_obj.target_type != MGMT_GLOBAL.G_BEACON_TARGET_TYPE ) THEN RETURN; END IF; BEGIN SELECT target_name INTO l_beacon_name FROM mgmt_targets WHERE target_guid = HEXTORAW(tgt_type_meta_ver_obj.target_guid) AND target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE; EXCEPTION WHEN NO_DATA_FOUND THEN MGMT_LOG.LOG_ERROR(G_MODULE_NAME, NULL, 'Beacon type meta version updated call back error: Beacon target guid '||tgt_type_meta_ver_obj.target_guid||' not found.' ); RETURN; END; --Perform sync now FOR sub_rec IN affected_svcs(HEXTORAW(tgt_type_meta_ver_obj.target_guid)) LOOP mgmt_gensvc_updbcn.submit_sync_job(sub_rec.target_name, sub_rec.target_type, l_beacon_name, MGMT_GENSVC_UPDBCN.SYNC_BEACON, l_job_id); END LOOP; END BCN_TYPE_METAVER_UPD_CALLBACK; FUNCTION HAS_PROMOTED_METRICS ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_category IN VARCHAR2) RETURN BOOLEAN IS l_metrics_count INTEGER; BEGIN SELECT COUNT(*) INTO l_metrics_count FROM mgmt_metrics met, mgmt_targets tgt, mgmt_category_map cat_map, mgmt_metric_dependency met_dep WHERE tgt.target_name = p_target_name AND tgt.target_type = p_target_type AND cat_map.target_type = tgt.target_type AND (cat_map.type_meta_ver = tgt.type_meta_ver OR tgt.type_meta_ver IS NULL) AND cat_map.object_guid = met.metric_guid AND cat_map.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC AND cat_map.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE AND cat_map.category_name = p_category AND tgt.type_meta_ver = met.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 = ' ') AND met_dep.target_guid = tgt.target_guid AND met_dep.metric_guid = met.metric_guid; RETURN l_metrics_count > 0; END HAS_PROMOTED_METRICS; PROCEDURE ALLOW_BUSINESS_PROMOTION( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, allow_promotion OUT INTEGER) IS TYPE members_row_type IS RECORD ( target_name mgmt_targets.target_name%TYPE, target_type mgmt_targets.target_type%TYPE, is_key NUMBER); data_out SERVICE_ASSOC_SYS_MEM_CURSOR; rec_raw members_row_type; rows_found boolean; BEGIN IF EM_BAM_SERVICE.IS_BUSINESS_DATA_ENABLED(p_target_name, p_target_type) THEN allow_promotion := 1; return; END IF; GET_BUSINESS_SYS_MEMBERS(p_target_name, p_target_type, data_out); FETCH data_out INTO rec_raw; rows_found := data_out%found; CLOSE data_out; IF rows_found then allow_promotion := 1; ELSE allow_promotion := 0; END IF; END ALLOW_BUSINESS_PROMOTION; END gensvc; / show errors;