Rem drv: Rem Rem $Header: aggservice_ui_pkgdef.sql 12-dec-2006.11:43:17 andyao Exp $ Rem Rem aggservice_pkgdef.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem aggservice_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem andyao 12/11/06 - add get_promoted_metrics_list Rem eporter 08/01/06 - Backport eporter_bug-5404472 from main Rem eporter 07/25/06 - Bug 5404472 Rem eporter 03/29/06 - Add Business functions Rem jpyang 09/23/05 - timezone support in chart Rem gsbhatia 07/01/05 - New repmgr header impl Rem jpyang 06/22/05 - support timezone Rem jpyang 03/30/05 - add set_threshold Rem jpyang 11/16/04 - handle duplicate target Rem jpyang 11/08/04 - perf/usage tab show/hide support Rem jpyang 10/26/04 - avail history hgrid Rem jpyang 10/15/04 - charts sql Rem jpyang 10/07/04 - homepage chart sql Rem vagarwal 10/03/04 - change proc name to get_services_for_system Rem vagarwal 09/30/04 - add proc to get svc for a system Rem jpyang 09/29/04 - home pages Rem jpyang 09/08/04 - Rem jpyang 09/02/04 - jpyang_services_ui Rem jpyang 08/31/04 - Created Rem CREATE OR REPLACE PACKAGE EM_AGGSVC_UI AS TYPE CURSOR_TYPE IS REF CURSOR; MODULE_NAME CONSTANT VARCHAR2(12) := 'AGG_SERVICE'; EDIT_MODE CONSTANT VARCHAR(4) := 'EDIT'; -- CONSTANT VARCHAR2(100) := MGMT_METRIC_HELPER.EVAL_MIN_VALUE_FUNC -- -- PROCEDURE: save_aggservice -- PURPOSE: save user-defined aggregate service to repository. -- can be a new aggregate service or modify an existing one -- IN PARAMETERS: p_aggsvc_name VARCHAR2 -- p_target_type VARCHAR2 -- p_subservices SMP_EMD_NVPAIR_ARRAY -- p_availability VARCHAR2 -- p_perfmetrics AGGSERVICE_METRIC_ARRAY -- p_usagemetrics AGGSERVICE_METRIC_ARRAY -- p_businessmetrics AGGSERVICE_METRIC_ARRAY PROCEDURE SAVE_AGGSERVICE(p_aggsvc_name VARCHAR2, p_target_type VARCHAR2, p_subservices SMP_EMD_NVPAIR_ARRAY, p_avail_eval_func VARCHAR2, p_timezone VARCHAR2, p_perfmetrics AGGSERVICE_METRIC_ARRAY, p_usagemetrics AGGSERVICE_METRIC_ARRAY, p_businessmetrics AGGSERVICE_METRIC_ARRAY, p_mode IN VARCHAR2); -- -- PROCEDURE: add_aggservice -- PURPOSE: save newly created user-defined aggregate service to repository. -- IN PARAMETERS: p_aggsvc_name VARCHAR2 -- p_target_type VARCHAR2 -- p_subservices SMP_EMD_NVPAIR_ARRAY -- p_availability VARCHAR2 -- p_timezone VARCHAR2 -- p_perfmetrics AGGSERVICE_METRIC_ARRAY -- p_usagemetrics AGGSERVICE_METRIC_ARRAY PROCEDURE ADD_AGGSERVICE(p_aggsvc_name VARCHAR2, p_target_type VARCHAR2, p_subservices SMP_EMD_NVPAIR_ARRAY, p_avail_eval_func VARCHAR2, p_timezone VARCHAR2, p_perfmetrics AGGSERVICE_METRIC_ARRAY, p_usagemetrics AGGSERVICE_METRIC_ARRAY); -- -- PROCEDURE: modify_aggservice -- PURPOSE: modify an existing user-defined aggregate service to repository. -- IN PARAMETERS: p_aggsvc_name VARCHAR2 -- p_target_type VARCHAR2 -- p_subservices SMP_EMD_NVPAIR_ARRAY -- p_availability VARCHAR2 -- p_timezone VARCHAR2 -- p_perfmetrics AGGSERVICE_METRIC_ARRAY -- p_usagemetrics AGGSERVICE_METRIC_ARRAY -- p_businessmetrics AGGSERVICE_METRIC_ARRAY PROCEDURE MODIFY_AGGSERVICE(p_aggsvc_name VARCHAR2, p_target_type VARCHAR2, p_subservices SMP_EMD_NVPAIR_ARRAY, p_avail_eval_func VARCHAR2, p_timezone VARCHAR2, p_perfmetrics AGGSERVICE_METRIC_ARRAY, p_usagemetrics AGGSERVICE_METRIC_ARRAY, p_businessmetrics AGGSERVICE_METRIC_ARRAY); -- -- PROCEDURE: get_aggservice -- PURPOSE: gets user-defined aggregate service with given service name and type. -- IN PARAMETERS: p_aggsvc_name VARCHAR2 -- p_target_type VARCHAR2 -- p_subservices SMP_EMD_NVPAIR_ARRAY -- p_availability VARCHAR2 -- p_timezone VARCHAR2 -- p_perfmetrics AGGSERVICE_METRIC_ARRAY -- p_usagemetrics AGGSERVICE_METRIC_ARRAY -- p_businessmetrics AGGSERVICE_METRIC_ARRAY -- p_timezone_list SMP_EMD_STRING_ARRAY PROCEDURE GET_AGGSERVICE(p_aggsvc_name IN VARCHAR2, p_target_type IN VARCHAR2, p_subservices OUT SMP_EMD_NVPAIR_ARRAY, p_avail_eval_func OUT VARCHAR2, p_timezone OUT VARCHAR2, p_perfmetrics OUT AGGSERVICE_METRIC_ARRAY, p_usagemetrics OUT AGGSERVICE_METRIC_ARRAY, p_businessmetrics OUT AGGSERVICE_METRIC_ARRAY, p_timezone_list OUT SMP_EMD_STRING_ARRAY); -- PROCEDURE: get_services_for_system -- PURPOSE: gets services associated with the given system name. -- IN PARAMETERS: p_target_name VARCHAR2 target name of the system target -- p_target_type VARCHAR2 target_type of the system target -- OUT PARAMETERS: p_services CURSOR_TYPE cursor containing info for the -- services associated with this system PROCEDURE GET_SERVICES_FOR_SYSTEM( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_services OUT CURSOR_TYPE); -- -- PROCEDURE: get_service_metrics -- PURPOSE: gets performance or usage metrics with given service name and type. -- IN PARAMETERS: p_aggsvc_name VARCHAR2 -- p_target_type VARCHAR2 -- p_metric_type VARCHAR2 either perfType, usageType, or businessType -- p_metrics_list AGGSERVICE_METRIC_ARRAY PROCEDURE GET_SERVICE_METRICS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_type IN VARCHAR2, p_metrics_list OUT AGGSERVICE_METRIC_ARRAY); -- -- PROCEDURE: get_service_metrics -- PURPOSE: gets performance or usage metrics with given service name and type. -- IN PARAMETERS: p_aggsvc_name VARCHAR2 -- p_target_type VARCHAR2 -- p_metric_type VARCHAR2 either perfType, usageType, or businessType -- p_mode VARCHAR2 use EDIT_MODE if editing metrics -- p_metrics_list AGGSERVICE_METRIC_ARRAY FUNCTION GET_SERVICE_METRICS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_type IN VARCHAR2, p_mode IN VARCHAR2 DEFAULT NULL) RETURN AGGSERVICE_METRIC_ARRAY; -- -- PROCEDURE: get_service_general_info -- PURPOSE: given service name and type returns data -- that shows in the general section of service home page -- PROCEDURE GET_SERVICE_GENERAL_INFO(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, gen_info_out OUT CURSOR_TYPE); -- -- PROCEDURE: get_service_general_info -- PURPOSE: given service name and type returns alerts data -- that shows in the alerts section of service home page -- p_alerts_type: can be either mgmt_global.G_SERVICE_CATEGORY_USAGE -- or mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE. If not specified -- it will return all USAGE and RESPONSE alerts. PROCEDURE GET_SERVICE_ALERTS_INFO(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_alerts_category IN VARCHAR2 DEFAULT NULL, alerts_info_out OUT CURSOR_TYPE); -- -- PROCEDURE: get_subservices_list -- PURPOSE: given service name and type return list -- of member services. -- The member services can be either aggregate or leaf service -- This query gets the immediate members only. PROCEDURE GET_SUBSERVICES_LIST (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, subservices_list_out OUT CURSOR_TYPE); -- -- PROCEDURE: get_subservices_list -- PURPOSE: given a list of service target, return -- target's availablitiy, performance alerts, usage alerts, -- policy voilations, and system information (if applied). FUNCTION GET_SERVICES_INFO(p_target_list IN SMP_EMD_NVPAIR_ARRAY) RETURN CURSOR_TYPE; PROCEDURE GET_DEFAULT_HOMEPAGE_METRICS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, perf_metric_info OUT CURSOR_TYPE, usage_metric_info OUT CURSOR_TYPE); -- PROCEDURE: get_metrics_list -- PURPOSE: given service target and type, return -- either list of performance metrics or usage metrics -- metric_category : can be either mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE -- or mgmt_global.G_SERVICE_CATEGORY_USAGE PROCEDURE GET_METRICS_LIST (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_category IN VARCHAR2, metrics_list_out OUT CURSOR_TYPE, target_sysdate OUT DATE); PROCEDURE GET_AVAIL_ALL_ASSOC_TARGETS (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, assoc_type_in IN VARCHAR2, start_time_out OUT DATE, end_time_out OUT DATE, tz_out OUT VARCHAR2, avail_states_self_out OUT CURSOR_TYPE, avail_states_assoc_out OUT CURSOR_TYPE ); -- given target type and type_meta_ver return list of -- performance or usage metrics for the type PROCEDURE HAS_PERF_USAGE_BY_TYPE(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2 DEFAULT NULL, perf_metrics_out OUT SMP_EMD_NVPAIR_ARRAY, usage_metrics_out OUT SMP_EMD_NVPAIR_ARRAY); -- sets performance/usage threshold -- this calls mgmt_moinitoring.save_target_metric_config() PROCEDURE SET_THRESHOLDS( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric AGGSERVICE_METRIC ); -- gets a list of the non-promoted metrics for a service -- these are shown on the metric promotion pages -- Note: -- This method is deprecated and should not be used. -- I am not taking this out because 10.2.0.3 is already in show stopper mode. PROCEDURE GET_NON_PROMOTED_METRICS_LIST (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_category IN VARCHAR2, metrics_list_out OUT CURSOR_TYPE); -- gets a list of the promoted metrics for a service/category. -- get_metric_list returns the full set of promoted and non promoted metrics. -- these are subtracted from those in get_metric_list to show -- non promoted metrics. PROCEDURE GET_PROMOTED_METRICS_LIST (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_category IN VARCHAR2, metrics_list_out OUT CURSOR_TYPE); -- -- FUNCTION: ALLOW_BUSINESS_PROMOTION -- -- Decides whether or not to show the 'Business Metrics' page on the gensvc -- Monitoring Configuration page. -- Only works for generic service/website. For aggregate service, use the -- same function in gensvc (gensvc/gensvc_tgt_pkgbody.sql) -- -- IN: p_target_name, p_target_type, -- OUT: allow_promotion -- PROCEDURE ALLOW_BUSINESS_PROMOTION(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, allow_promotion OUT INTEGER); END EM_AGGSVC_UI; / show errors;