Rem Rem $Header: sdk_schema_pkgdef.sql 10-aug-2004.13:45:28 streddy Exp $ Rem Rem sdk_schema_pkgdef.sql Rem Rem Copyright (c) 2002, 2004, Oracle. All rights reserved. Rem Rem NAME Rem sdk_schema_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem streddy 08/10/04 - Add is_transposed to add_metric Rem ancheng 09/24/03 - bug 3139898 Rem ancheng 07/31/03 - add add_severity_annotation Rem streddy 07/11/03 - Add message_nlsid to add_severity Rem streddy 04/09/03 - Severity evaluation support Rem streddy 03/03/03 - Added severity_eval_proc to add_table_metric_column API Rem rpinnama 05/13/03 - Pass category props to add_metric procedures Rem dcawley 09/23/02 - Add load_time parameter Rem rpinnama 05/16/02 - rpinnama_reorg_rep_scripts_2 Rem rpinnama 05/16/02 - Created Rem ----------------------------- -- The package description -- ----------------------------- CREATE OR REPLACE PACKAGE EMD_SCHEMA IS -- Constants for metric types NUMBER_METRIC_TYPE constant number := 0; STRING_METRIC_TYPE constant number := 1; TABLE_METRIC_TYPE constant number := 2; RAW_METRIC_TYPE constant number := 3; EXTERNAL_METRIC_TYPE constant number := 4; REPOSITORY_TABLE_METRIC_TYPE constant number := 5; REPOSITORY_NUMBER_METRIC_TYPE constant number := 6; REPOSITORY_STRING_METRIC_TYPE constant number := 7; ---------------------------------------------- -- Procedures for adding metric definitions -- ---------------------------------------------- -- PURPOSE -- Procedure to add a metric. -- -- PARAMETERS -- -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_TYPE - the type of the metric -- 0 - NUMBER -- 1 - STRING -- 2 - TABLE (table with one column primary key, no alternate keys) -- 3 - RAW (long character object) -- 4 - EXTERNAL -- 5 - REPOSITORY_TABLE -- 6 - REPOSITORY_NUMBER -- 7 - REPOSITORY_STRING -- V_LABEL - the label for the metric -- V_DESCRIPTION - a description of the metric -- V_EVAL_PROC - the name of the evaluation procedure if the metric is of -- type 5, 6 or 7 -- V_UNIT - the unit the metric is measured in -- V_SHORT_NAME - a short name for the metric (14 characters max) to be -- used in dense UIs -- V_IS_FOR_SUMMARY - a flag to indicate whether this metric should -- be displayed as part of a summary UI -- -- V_LABEL_NLSID - NLS ID of the metric label -- -- V_IS_TRANSPOSED - This distinguishes transposed metrics from regular metrics. -- Transposed metrics are metrics whose metric columns are not -- known at definition time and only known at run time.The values -- of the key columns are used as the names for the data columns. -- If IS_TRANSPOSED = 0, then it means that the metric is not -- transposed. If IS_TRANSPOSED = 1, then it means that the -- metric is transposed -- -- NOTES -- -- If a TABLE metric is being defined then the columns for the table must be -- added using ADD_TABLE_METRIC_COLUMN. -- If a REPOSITORY collection metric is being defined (metric_type is 5,6 or 7) -- then thresholds must be defined using EMD_COLLECTION.ADD_THRESHOLDS and a -- collection started by calling EMD_COLLECTION.START_COLLECTION -- PROCEDURE ADD_METRIC(v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_type IN NUMBER, v_label IN VARCHAR2 DEFAULT NULL, v_description IN VARCHAR2 DEFAULT ' ', v_eval_proc IN VARCHAR2 DEFAULT NULL, v_unit IN VARCHAR2 DEFAULT ' ', v_short_name IN VARCHAR2 DEFAULT NULL, v_is_for_summary IN NUMBER DEFAULT 0, v_label_nlsid IN VARCHAR2 DEFAULT NULL, v_is_transposed IN NUMBER DEFAULT 0); -- procedure that supports metric versioning. PROCEDURE ADD_METRIC_FOR_VERSION (v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_type IN NUMBER, v_type_meta_ver IN VARCHAR2 DEFAULT '1.0', v_category_prop_1 IN VARCHAR2 DEFAULT ' ', v_category_prop_2 IN VARCHAR2 DEFAULT ' ', v_category_prop_3 IN VARCHAR2 DEFAULT ' ', v_category_prop_4 IN VARCHAR2 DEFAULT ' ', v_category_prop_5 IN VARCHAR2 DEFAULT ' ', v_label IN VARCHAR2 DEFAULT NULL, v_description IN VARCHAR2 DEFAULT ' ', v_eval_proc IN VARCHAR2 DEFAULT NULL, v_unit IN VARCHAR2 DEFAULT ' ', v_short_name IN VARCHAR2 DEFAULT NULL, v_is_for_summary IN NUMBER DEFAULT 0, v_label_nlsid IN VARCHAR2 DEFAULT NULL, v_is_transposed IN NUMBER DEFAULT 0); -- PURPOSE -- Procedure to add a column to a table metric -- -- PARAMETERS -- -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_TYPE - the type of the metric -- V_METRIC_COLUMN - the name of the metric column -- V_IS_KEY - flag to indicate whether this is a key column -- 0 - Not a key column -- 1 - Is a key column -- V_LABEL - the label for the metric column -- V_DESCRIPTION - a description of the metric -- V_UNIT - the unit the metric is measured in -- V_SHORT_NAME - a short name for the metric (14 characters max) to be -- used in dense UIs -- V_IS_FOR_SUMMARY - a flag to indicate whether this metric should -- be displayed as part of a summary UI -- -- V_SEVERITY_EVAL_PROC - the name of the severity evaluation -- procedure if the metric is of type G_METRIC_TYPE_REPOS_EVENT. -- The signature of the proc must be the following: -- PROCEDURE (v_target_guid IN RAW, -- v_metric_guid IN RAW, -- v_dependency_list IN MGMT_METRIC_DETAILS_ARRAY, -- v_sev_code OUT NUMBER, -- v_message OUT VARCHAR2, -- v_message_nlsid OUT VARCHAR2, -- v_message_params OUT VARCHAR2); -- V_LABEL_NLSID - NLS ID of the column label -- NOTES -- -- A table metric can have more than one column. This procedure should be -- called for each column in the table but must be can only be called after -- the table metric has been added using ADD_METRIC. If the table contains -- multiple rows and thus requires a key column, this can be added using this -- procedure and setting the v_is_key flag to 1. -- -- If the metric_type is REPOSITORY_EVENT, then severity_eval_proc must -- be defined. -- -- If severity_eval_proc is defined, then add_metric_severity_deps() must -- be called to add dependencies. Not doing so will result in this method -- never getting called. -- PROCEDURE ADD_TABLE_METRIC_COLUMN(v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_type IN NUMBER, v_metric_column IN VARCHAR2, v_is_key IN NUMBER, v_label IN VARCHAR2 DEFAULT NULL, v_description IN VARCHAR2 DEFAULT ' ', v_unit IN VARCHAR2 DEFAULT ' ', v_short_name IN VARCHAR2 DEFAULT NULL, v_is_for_summary IN NUMBER DEFAULT 0, v_severity_eval_proc IN VARCHAR2 DEFAULT NULL, v_label_nlsid IN VARCHAR2 DEFAULT NULL); -- API that supports metric versioning PROCEDURE ADD_METRIC_COLUMN_FOR_VERSION (v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_type IN NUMBER, v_metric_column IN VARCHAR2, v_is_key IN NUMBER, v_type_meta_ver IN VARCHAR2 DEFAULT '1.0', v_category_prop_1 IN VARCHAR2 DEFAULT ' ', v_category_prop_2 IN VARCHAR2 DEFAULT ' ', v_category_prop_3 IN VARCHAR2 DEFAULT ' ', v_category_prop_4 IN VARCHAR2 DEFAULT ' ', v_category_prop_5 IN VARCHAR2 DEFAULT ' ', v_label IN VARCHAR2 DEFAULT NULL, v_description IN VARCHAR2 DEFAULT ' ', v_unit IN VARCHAR2 DEFAULT ' ', v_short_name IN VARCHAR2 DEFAULT NULL, v_is_for_summary IN NUMBER DEFAULT 0, v_severity_eval_proc IN VARCHAR2 DEFAULT NULL, v_label_nlsid IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to delete a metric -- -- PARAMETERS -- -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- -- NOTES -- This will also delete all metric and key columns of a table metric PROCEDURE DELETE_METRIC(v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2); --------------------------------------- -- Procedures for adding metric data -- --------------------------------------- -- PURPOSE -- Procedure to add a number value for a simple metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_VALUE - the number value to insert for the simple metric -- V_TIMESTAMP - the time the data was calculated -- -- NOTES -- PROCEDURE ADD_METRIC_DATA (v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_value IN NUMBER, v_timestamp IN DATE DEFAULT SYSDATE); -- PURPOSE -- Procedure to add a string value for a simple metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_STRING_VALUE - the string value to insert for the simple metric -- V_TIMESTAMP - the time the data was calculated -- -- NOTES -- PROCEDURE ADD_METRIC_DATA (v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_string_value IN VARCHAR2, v_timestamp IN DATE DEFAULT SYSDATE); -- PURPOSE -- Procedure to add a number value for a column in a table metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_COLUMN - the name of the metric column -- V_KEY_VALUE - the value for the key column. If the table metric has -- been defined without a key column the default value of -- ' ' should be used -- V_VALUE - the number value to insert for the simple metric -- V_TIMESTAMP - the time the data was calculated -- -- NOTES -- There are no checks on the mgmt_metrics_raw table to stop someone -- entering invalid values for v_metric_column and v_key_value -- PROCEDURE ADD_METRIC_DATA (v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2, v_key_value IN VARCHAR2 DEFAULT ' ', v_value IN NUMBER, v_timestamp IN DATE DEFAULT SYSDATE); -- PURPOSE -- Procedure to add a string value for a column in a table metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_COLUMN - the name of the metric column -- V_KEY_VALUE - the value for the key column. If the table metric has -- been defined without a key column the default value of -- ' ' should be used -- V_STRING_VALUE - the string value to insert for the simple metric -- V_TIMESTAMP - the time the data was calculated -- -- NOTES -- There are no checks on the mgmt_metrics_raw table to stop someone -- entering invalid values for v_metric_column and v_key_value -- PROCEDURE ADD_METRIC_DATA (v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2, v_key_value IN VARCHAR2 DEFAULT ' ', v_string_value IN VARCHAR2, v_timestamp IN DATE DEFAULT SYSDATE); ------------------------------------------------ -- Procedures for adding severity occurrences -- ------------------------------------------------ -- PURPOSE -- Procedure to add a warning severity occurrence for a simple metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_SEVERITY_TYPE - the type of the severity -- 0 - UNKNOWN -- 1 - AVAILABILITY -- 2 - RESOURCE -- V_ANNOTATION - an annotation to add for the severity -- V_MESSAGE - the message associated with the severity -- V_MESSAGE_NLSID - NLS ID of the severity message -- V_MESSAGE_PARAMS - URL encoded parameters separated by "&" to be -- used to format the severity message -- NOTES PROCEDURE ADD_WARNING_SEVERITY(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_severity_type IN NUMBER, v_annotation IN VARCHAR2, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a warning severity occurrence for a table metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_COLUMN - the name of the metric column -- V_KEY_VALUE - the value for the key column. If the table metric has -- been defined without a key column the default value of -- ' ' should be used -- V_SEVERITY_TYPE - the type of the severity -- 0 - UNKNOWN -- 1 - AVAILABILITY -- 2 - RESOURCE -- V_ANNOTATION - an annotation to add for the severity -- V_MESSAGE - the message associated with the severity -- V_MESSAGE_NLSID - NLS ID of the severity message -- V_MESSAGE_PARAMS - URL encoded parameters separated by "&" to be -- used to format the severity message -- -- NOTES PROCEDURE ADD_WARNING_SEVERITY(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2, v_key_value IN VARCHAR2 DEFAULT ' ', v_severity_type IN NUMBER, v_annotation IN VARCHAR2, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a critical severity occurrence for a simple metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_SEVERITY_TYPE - the type of the severity -- 0 - UNKNOWN -- 1 - AVAILABILITY -- 2 - RESOURCE -- V_ANNOTATION - an annotation to add for the severity -- V_MESSAGE - the message associated with the severity -- V_MESSAGE_NLSID - NLS ID of the severity message -- V_MESSAGE_PARAMS - URL encoded parameters separated by "&" to be -- used to format the severity message -- -- NOTES PROCEDURE ADD_CRITICAL_SEVERITY(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_severity_type IN NUMBER, v_annotation IN VARCHAR2, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a critical severity occurrence for a table metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_COLUMN - the name of the metric column -- V_KEY_VALUE - the value for the key column. If the table metric has -- been defined without a key column the default value of -- ' ' should be used -- V_SEVERITY_TYPE - the type of the severity -- 0 - UNKNOWN -- 1 - AVAILABILITY -- 2 - RESOURCE -- V_ANNOTATION - an annotation to add for the severity -- V_MESSAGE - the message associated with the severity -- V_MESSAGE_NLSID - NLS ID of the severity message -- V_MESSAGE_PARAMS - URL encoded parameters separated by "&" to be -- used to format the severity message -- -- NOTES PROCEDURE ADD_CRITICAL_SEVERITY(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2, v_key_value IN VARCHAR2, v_severity_type IN NUMBER, v_annotation IN VARCHAR2, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a severity clear occurrence for a simple metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_SEVERITY_TYPE - the type of the severity -- 0 - UNKNOWN -- 1 - AVAILABILITY -- 2 - RESOURCE -- V_ANNOTATION - an annotation to add for the severity -- V_MESSAGE - the message associated with the severity -- V_MESSAGE_NLSID - NLS ID of the severity message -- V_MESSAGE_PARAMS - URL encoded parameters separated by "&" to be -- used to format the severity message -- -- NOTES PROCEDURE ADD_SEVERITY_CLEAR(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_severity_type IN NUMBER, v_annotation IN VARCHAR2, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a severity clear occurrence for a table metric -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_COLUMN - the name of the metric column -- V_KEY_VALUE - the value for the key column. If the table metric has -- been defined without a key column the default value of -- ' ' should be used -- V_SEVERITY_TYPE - the type of the severity -- 0 - UNKNOWN -- 1 - AVAILABILITY -- 2 - RESOURCE -- V_ANNOTATION - an annotation to add for the severity -- V_MESSAGE - the message associated with the severity -- V_MESSAGE_NLSID - NLS ID of the severity message -- V_MESSAGE_PARAMS - URL encoded parameters separated by "&" to be -- used to format the severity message -- -- NOTES PROCEDURE ADD_SEVERITY_CLEAR(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2, v_key_value IN VARCHAR2, v_severity_type IN NUMBER, v_annotation IN VARCHAR2, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a severity occurrence -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- V_METRIC_COLUMN - the name of the metric column -- V_KEY_VALUE - the value for the key column. If the table metric has -- been defined without a key column the default value of -- ' ' should be used -- V_SEVERITY_CODE - the code of the severity -- 0 - non up/down severity occurence -- 1 - up/down severity occurence -- 2 - agent reachable/unreachable -- 3 - severity registration/deregistration -- 4 - node unreachable -- 5 - blackout start -- 6 - blackout end -- 15 - clear -- 18 - error -- 20 - warning -- 25 - alert -- 30 - node down -- V_SEVERITY_TYPE - the type of the severity -- 0 - UNKNOWN -- 1 - AVAILABILITY -- 2 - RESOURCE -- V_ANNOTATION - an annotation to add for the severity -- V_MESSAGE - the message associated with the severity -- V_MESSAGE_NLSID - NLS ID of the severity message -- V_MESSAGE_PARAMS - URL encoded parameters separated by "&" to be -- used to format the severity message -- -- NOTES PROCEDURE ADD_SEVERITY(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2, v_key_value IN VARCHAR2, v_severity_code IN NUMBER, v_severity_type IN NUMBER, v_annotation IN VARCHAR2, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a severity annotation -- -- PARAMETERS -- -- V_SEVERITY_GUID - the severity guid this annotation is associated with -- V_MESSAGE - the annotation itself -- V_USER_NAME - EM user who authored the annotation -- V_ANNOTATION_TYPE - the annotation type -- -- NOTES -- -- The timestamp of the annotation will be the SYSDATE calculated in the -- target timezone region (ttzr). -- PROCEDURE add_severity_annotation_ttzr(v_severity_guid IN RAW, v_message IN VARCHAR2, v_user_name IN VARCHAR2 DEFAULT '', v_annotation_type IN VARCHAR2 DEFAULT NULL); -- PURPOSE -- Procedure to add a severity annotation -- -- PARAMETERS -- -- V_SEVERITY_GUID - the severity guid this annotation is associated with -- V_MESSAGE - the annotation itself -- V_USER_NAME - EM user who authored the annotation -- V_TIMESTAMP - time at which this annotation is created -- V_ANNOTATION_TYPE - the annotation type -- -- NOTES PROCEDURE add_severity_annotation(v_severity_guid IN RAW, v_message IN VARCHAR2, v_user_name IN VARCHAR2 DEFAULT '', v_timestamp IN DATE DEFAULT SYSDATE, v_annotation_type IN VARCHAR2 DEFAULT NULL); --------------------------------------- -- Procedures for deleting metric data -- --------------------------------------- -- PURPOSE -- Procedure to delete all data related to a given metric. -- -- PARAMETERS -- -- V_TARGET_NAME - the name of the target associated with the metric -- V_TARGET_TYPE - the type of the target -- V_METRIC_NAME - the name of the metric -- -- NOTES -- PROCEDURE DELETE_METRIC_DATA (v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2); END EMD_SCHEMA; / show errors