Rem drv: Rem Rem $Header: service_level_pkgbody.sql 03-jul-2006.05:37:51 skotha Exp $ Rem Rem service_level_pkgbody.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem service_level_pkgbody.sql - Package body for SL rule Rem Rem DESCRIPTION Rem Package body for SL rule Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem skotha 07/03/06 - Service Dashboard enhancements for BAM Integration Rem mkm 07/06/05 - commenting the exception in delete_service_level Rem verangan 06/28/05 - use the current username of the context Rem mkm 06/22/05 - fix for non-sysman users to access the page Rem mkm 06/16/05 - chagne of key_val deletin callback signature Rem mkm 06/10/05 - keyvalue deletion call back Rem gsbhatia 07/01/05 - New repmgr header impl Rem nanand 05/12/05 - pushing key_value also to history table Rem nanand 05/11/05 - load_timestamp in history tables to be in target Rem timezone Rem mkm 03/31/05 - adding service name/type check Rem mkm 03/16/05 - transposed metrics fix Rem mswamyt 04/04/05 - Fix errors Rem mswamyt 03/31/05 - Callback changes for reporting elements Rem jmenon 02/25/05 - fix Rem jmenon 01/27/05 - Changes for callback fxns Rem mswamyt 01/24/05 - SL callback Rem mswamyt 01/12/05 - Add call back Rem mswamyt 01/11/05 - handling no rule Rem mswamyt 12/15/04 - key_value fix Rem mswamyt 12/08/04 - Change from Response to Performance Rem mswamyt 11/18/04 - Fix oratst files Rem mswamyt 11/18/04 - changing NULL check of p_metric_name_in Rem verangan 11/11/04 - Fix condition Rem verangan 11/01/04 - Added key_value Rem snathan 10/21/04 - snathan_svclvl_merge Rem verangan 10/06/04 - Created Rem CREATE OR REPLACE PACKAGE BODY EM_SL IS -- set_service_rule -- -- PURPOSE : Procedure that will be invoked to create/update -- service level rule for the service/target -- -- This procedure inturn will call set_service_coretimes and -- set_service_characterstics procedures. -- PROCEDURE set_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_date_sequence_in IN VARCHAR2, p_start_time_in IN VARCHAR2, p_end_time_in IN VARCHAR2, p_avail_includes_in IN NUMBER, p_metric_name_column_in IN SMP_EMD_NVPAIR_ARRAY, p_key_values_in IN VARCHAR2_TABLE, p_expected_sl_in IN NUMBER ) IS l_metric_guids MGMT_USER_GUID_ARRAY; l_metric_guid_key_value SMP_EMD_NVPAIR_ARRAY; l_key_value mgmt_current_metrics.key_value%TYPE; l_count NUMBER; l_index NUMBER; BEGIN IF p_metric_name_column_in IS NOT NULL AND p_key_values_in IS NOT NULL THEN IF p_metric_name_column_in.COUNT != p_key_values_in.COUNT THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Count of metricname/column differs from keyvalues'); END IF; END IF; get_metricguids_from_names(p_target_name_in, p_target_type_in, p_metric_name_column_in, l_metric_guids); IF l_metric_guids.COUNT > 0 THEN l_metric_guid_key_value := SMP_EMD_NVPAIR_ARRAY(); l_index := 0; FOR l_count IN l_metric_guids.FIRST .. l_metric_guids.LAST LOOP IF l_metric_guids(l_count) IS NOT NULL THEN l_metric_guid_key_value.extend(); l_index := l_index + 1; IF p_key_values_in IS NULL OR p_key_values_in(l_count) IS NULL THEN l_key_value := ' '; ELSE l_key_value := p_key_values_in(l_count); END IF; l_metric_guid_key_value(l_index) := SMP_EMD_NVPAIR(l_metric_guids(l_count), l_key_value); END IF; END LOOP; END IF; set_service_level(p_target_name_in, p_target_type_in, p_date_sequence_in, p_start_time_in, p_end_time_in, p_avail_includes_in, l_metric_guid_key_value, p_expected_sl_in); END set_service_level; PROCEDURE set_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_date_sequence_in IN VARCHAR2, p_start_time_in IN VARCHAR2, p_end_time_in IN VARCHAR2, p_avail_includes_in IN NUMBER, p_metric_guid_key_value_in IN SMP_EMD_NVPAIR_ARRAY, p_expected_sl_in IN NUMBER ) IS l_count NUMBER; l_colon_index NUMBER; l_index NUMBER; l_metric_guid MGMT_METRICS.metric_guid%TYPE; l_save_date DATE; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_key_value mgmt_current_metrics.key_value%TYPE; l_cuser VARCHAR2(256); BEGIN -- Input validation begin. --- Error out on the following conditions --- p_target_name_in and p_target_type_in invalid invalid -- invalid date sequence --- invalid start and end_time --- avail_includes not in 1,2,3,4 --- exp_svc_level > 100 SELECT COUNT(*) INTO l_count FROM mgmt_targets WHERE target_name=p_target_name_in AND target_type=p_target_type_in; IF (l_count != 1) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid Service name/type passed: ' || p_target_name_in || '/' || p_target_type_in); END IF; l_cuser := MGMT_USER.GET_CURRENT_EM_USER; IF MGMT_USER.has_priv(l_cuser, MGMT_USER.OPERATOR_TARGET, p_target_name_in, p_target_type_in) != MGMT_USER.USER_HAS_PRIV THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR_M); END IF; SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_name=p_target_name_in AND target_type=p_target_type_in; -- If Date sequence is > 7 chars then error out. IF (LENGTH(p_date_sequence_in) != NUM_DAYS_WEEK) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, ' Invalid Date Seq Passed: ' || p_date_sequence_in ); END IF; IF (p_start_time_in > p_end_time_in OR p_start_time_in < 0 OR p_start_time_in > END_HOUR or p_end_time_in > END_HOUR) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid start and end time passed : ' || p_start_time_in || ' ' || p_end_time_in ); END IF; IF (p_expected_sl_in > HUNDRED OR p_expected_sl_in < 0 ) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Inavlid expected sl passed : ' || p_expected_sl_in ); END IF; IF (p_avail_includes_in < G_NOT_BLACKOUT_NOT_AGENT_INCL OR p_avail_includes_in > G_BLACKOUT_AGENT_INCL) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Inavlid availability include passed : ' || p_avail_includes_in ); END IF; -- input validation END -- All ok proceed to insert. -- If the SLA rule for this target already exists copy -- the entries into the Audit table IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Inserting SL rule' || l_target_guid,MODULE_NAME); END IF; -- Save the date that will be saved in history table l_save_date := mgmt_target.sysdate_target(l_target_guid); INSERT INTO mgmt_sl_rules_history( target_guid,date_sequence,start_time,end_time, avail_includes, expected_sl,load_timestamp) (SELECT target_guid,date_sequence,start_time,end_time, avail_includes,expected_sl,l_save_date FROM mgmt_sl_rules WHERE target_guid = l_target_guid); DELETE FROM mgmt_sl_rules WHERE target_guid=l_target_guid; INSERT INTO mgmt_sl_metrics_history( target_guid,metric_guid,key_value,load_timestamp) (SELECT target_guid,metric_guid,key_value,l_save_date FROM mgmt_sl_metrics WHERE target_guid = l_target_guid); DELETE FROM mgmt_sl_metrics WHERE target_guid=l_target_guid; -- insert into mgmt_sl_rules and mgmt_sl_metrics. INSERT INTO mgmt_sl_rules (target_guid,date_sequence, start_time,end_time,avail_includes,expected_sl) values ( l_target_guid,p_date_sequence_in,p_start_time_in, p_end_time_in,p_avail_includes_in,p_expected_sl_in); -- If metric_guids_in is NULL then just return IF (p_metric_guid_key_value_in IS NOT NULL AND p_metric_guid_key_value_in.count > 0 ) THEN -- Cannot do bulk insert because of PLSQL limitation FOR i in p_metric_guid_key_value_in.FIRST .. p_metric_guid_key_value_in.LAST LOOP IF p_metric_guid_key_value_in(i).value IS NULL THEN l_key_value := ' '; ELSE l_key_value := p_metric_guid_key_value_in(i).value; END IF; INSERT INTO mgmt_sl_metrics (target_guid, metric_guid, key_value) VALUES (l_target_guid,p_metric_guid_key_value_in(i).name, l_key_value); END LOOP; END IF; END set_service_level; PROCEDURE get_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_date_sequence_out OUT VARCHAR2, p_start_time_out OUT VARCHAR2, p_end_time_out OUT VARCHAR2, p_timezone_out OUT MGMT_TARGETS.timezone_region%TYPE, p_avail_includes_out OUT NUMBER, p_metric_guid_key_value_out OUT SMP_EMD_NVPAIR_ARRAY, p_metric_name_column_out OUT SMP_EMD_NVPAIR_ARRAY, p_bmetric_guid_key_value_out OUT SMP_EMD_NVPAIR_ARRAY, p_bmetric_name_column_out OUT SMP_EMD_NVPAIR_ARRAY, p_expected_sl_out OUT NUMBER) IS l_row_count NUMBER; l_metric_guid VARCHAR2(64); l_index NUMBER; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_count NUMBER; l_cuser VARCHAR2(256); BEGIN SELECT COUNT(*) INTO l_count FROM mgmt_targets WHERE target_name=p_target_name_in AND target_type=p_target_type_in; IF (l_count != 1) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid Service name/type passed: ' || p_target_name_in || '/' || p_target_type_in); END IF; l_cuser := MGMT_USER.GET_CURRENT_EM_USER; IF MGMT_USER.has_priv(l_cuser, MGMT_USER.OPERATOR_TARGET, p_target_name_in, p_target_type_in) != MGMT_USER.USER_HAS_PRIV THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR_M); END IF; SELECT target_guid, timezone_region INTO l_target_guid, p_timezone_out FROM mgmt_targets WHERE target_name=p_target_name_in AND target_type=p_target_type_in; BEGIN SELECT date_sequence,start_time,end_time,avail_includes, expected_sl INTO p_date_sequence_out,p_start_time_out, p_end_time_out,p_avail_includes_out,p_expected_sl_out FROM mgmt_sl_rules where target_guid=l_target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; p_metric_guid_key_value_out := SMP_EMD_NVPAIR_ARRAY(); p_metric_name_column_out := SMP_EMD_NVPAIR_ARRAY(); SELECT SMP_EMD_NVPAIR(slm.metric_guid, slm.key_value), SMP_EMD_NVPAIR(met.metric_name, metric_column) BULK COLLECT INTO p_metric_guid_key_value_out, p_metric_name_column_out FROM mgmt_sl_metrics slm, mgmt_metrics met, mgmt_targets tgt WHERE slm.target_guid=l_target_guid AND tgt.target_guid=slm.target_guid AND tgt.target_type=met.target_type AND tgt.type_meta_ver=met.type_meta_ver AND em_dashboard_service.verify_metric_type(met.metric_guid, MGMT_GLOBAL.G_SERVICE_CATEGORY_PERFORMANCE, l_target_guid) = 1 AND met.metric_guid=slm.metric_guid 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 = ' ' ); -- Initializing the bus metrics to empty array p_bmetric_guid_key_value_out := SMP_EMD_NVPAIR_ARRAY(); p_bmetric_name_column_out := SMP_EMD_NVPAIR_ARRAY(); SELECT SMP_EMD_NVPAIR(slm.metric_guid, slm.key_value), SMP_EMD_NVPAIR(met.metric_name, metric_column) BULK COLLECT INTO p_bmetric_guid_key_value_out, p_bmetric_name_column_out FROM mgmt_sl_metrics slm, mgmt_metrics met, mgmt_targets tgt WHERE slm.target_guid=l_target_guid AND tgt.target_guid=slm.target_guid AND tgt.target_type=met.target_type AND tgt.type_meta_ver=met.type_meta_ver AND em_dashboard_service.verify_metric_type(met.metric_guid, MGMT_GLOBAL.G_SERVICE_CATEGORY_BUSINESS, l_target_guid) = 1 AND met.metric_guid=slm.metric_guid 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 get_service_level; PROCEDURE get_metricguids_from_names( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_metric_name_column_in IN SMP_EMD_NVPAIR_ARRAY, p_metric_guids_out OUT MGMT_USER_GUID_ARRAY) IS l_count NUMBER; BEGIN l_count := 0; p_metric_guids_out := MGMT_USER_GUID_ARRAY(); IF p_metric_name_column_in IS NULL THEN RETURN; END IF; FOR l_count IN p_metric_name_column_in.FIRST .. p_metric_name_column_in.LAST LOOP p_metric_guids_out.extend(); BEGIN p_metric_guids_out(l_count) := mgmt_metric.get_metric_guid_for_target(p_target_type_in, p_target_name_in, p_metric_name_column_in(l_count).name, p_metric_name_column_in(l_count).value); EXCEPTION WHEN NO_DATA_FOUND THEN p_metric_guids_out(l_count) := NULL; END; END LOOP; END get_metricguids_from_names; PROCEDURE delete_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE) IS l_count NUMBER; l_target_guid MGMT_TARGETS.target_guid%TYPE; BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_name=p_target_name_in AND target_type=p_target_type_in; /* SELECT count(*) INTO l_count FROM mgmt_sl_rules where target_guid = l_target_guid; IF (l_count != 1) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Wrong Target_guid passed for purge :' || l_target_guid ); END IF; */ DELETE FROM mgmt_sl_rules WHERE target_guid=l_target_guid; DELETE FROM mgmt_sl_metrics where target_guid= l_target_guid; DELETE FROM mgmt_sl_rules_history WHERE target_guid=l_target_guid; DELETE FROM mgmt_sl_metrics_history WHERE target_guid= l_target_guid; END; PROCEDURE update_sl( p_target_name in MGMT_TARGETS.target_name%TYPE, p_target_type in MGMT_TARGETS.target_type%TYPE, p_target_guid in MGMT_TARGETS.target_guid%TYPE ) IS l_count NUMBER; BEGIN IF (emdw_log.p_is_info_set)THEN emdw_log.info('update_sl:Entry p_target_name='|| p_target_name||' , p_target_type='|| p_target_type||' , p_target_guid='||p_target_guid ,MODULE_NAME); END IF; SELECT count(*) INTO l_count FROM mgmt_targets tgt, mgmt_type_properties typ WHERE tgt.target_guid = p_target_guid AND tgt.target_type = typ.target_type AND typ.property_name = MGMT_GLOBAL.G_IS_SERVICE_PROP AND typ.property_value = 1; IF (l_count = 1) THEN EM_SL.set_service_level( p_target_name,p_target_type, STANDARD_WEEK,DEFAULT_BH_START,DEFAULT_BH_END, G_NOT_BLACKOUT_AGENT_INCL, null,STANDARD_EXPECTED_SL); END IF; END; PROCEDURE delete_sl( p_target_name in MGMT_TARGETS.target_name%TYPE, p_target_type in MGMT_TARGETS.target_type%TYPE, p_target_guid in MGMT_TARGETS.target_guid%TYPE ) IS l_count NUMBER; BEGIN IF (emdw_log.p_is_info_set)THEN emdw_log.info('delete_sl:Entry p_target_name='|| p_target_name||' , p_target_type='|| p_target_type||' , p_target_guid='||p_target_guid ,MODULE_NAME); END IF; SELECT count(*) INTO l_count FROM mgmt_targets tgt, mgmt_type_properties typ WHERE tgt.target_guid = p_target_guid AND tgt.target_type = typ.target_type AND typ.property_name = MGMT_GLOBAL.G_IS_SERVICE_PROP AND typ.property_value = 1; IF (l_count = 1) THEN EM_SL.delete_service_level(p_target_name,p_target_type); END IF; END; PROCEDURE update_sl_keyval_delete( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_key_value IN mgmt_metrics_raw.key_value%TYPE ) IS BEGIN IF (emdw_log.p_is_info_set)THEN emdw_log.info('update_sl_keyval_delete:Entry p_target_guid='|| p_target_guid|| ' p_metric_guid='|| p_metric_guid|| ' p_key_value='|| p_key_value, MODULE_NAME); END IF; DELETE FROM mgmt_sl_metrics WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value; DELETE FROM mgmt_sl_metrics_history WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value; END update_sl_keyval_delete; END EM_SL; / SHOW ERRORS;