Rem Rem $Header: service_level_tables.sql 27-jul-2005.05:39:44 tjana Exp $ Rem Rem service_level_tables.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem service_level_tables.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem tjana 07/27/05 - removing default ' ' for upgrade Rem gsbhatia 06/26/05 - New repmgr header impl Rem mkm 06/03/05 - mkm_t0519 Rem mkm 06/02/05 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 rem rem MGMT_SL_RULES rem This table stores the service level rules that is set rem for the service rem COLUMNS rem Target_guid - unique id of the target rem rem Date_sequence - Dates that are set as business days rem A sequence of days are stored in the order of rem 'mon/tue/wed/thu/fri/sa/sun' rem a sequence of 1101101 indicate monday and thursday rem are non-business days and all other days are business days rem rem start_time - start times within business days which are rem considered valid business hrs. rem rem end_time - end times within business days which are rem considered valid business hrs. rem rem avail_includes - This column contains value between 1..4 rem indicating if blackout and agent downtime needs to be rem included as avail times or not rem Each of the value represents one of the State rem 1 --> 00 - Blackout and agent downtime is not included in service avail rem 2 --> 10 - Blackout is included while agent downtime is not rem 3 --> 01 - Blackout is not included while agent downtime is rem 4 --> 11 - Blackout and agent downtime is included rem rem expected_sl - expected SL for this service. rem NOTES CREATE TABLE MGMT_SL_RULES (TARGET_GUID RAW(16) NOT NULL, DATE_SEQUENCE CHAR(7) NOT NULL, START_TIME CHAR(4) NOT NULL, END_TIME CHAR(4) NOT NULL, AVAIL_INCLUDES NUMBER NOT NULL, EXPECTED_SL NUMBER NOT NULL ) STORAGE (FREELISTS 4) INITRANS 2 MONITORING; -- Comment for the Table COMMENT ON TABLE MGMT_SL_RULES IS 'SL rules defn' ; COMMENT ON COLUMN MGMT_SL_RULES.TARGET_GUID IS 'Target guid for the Service' ; COMMENT ON COLUMN MGMT_SL_RULES.DATE_SEQUENCE IS 'Days considered business and non-business days' ; COMMENT ON COLUMN MGMT_SL_RULES.START_TIME IS 'Business hour start on a business day' ; COMMENT ON COLUMN MGMT_SL_RULES.END_TIME IS 'Business hour end on a business day' ; COMMENT ON COLUMN MGMT_SL_RULES.AVAIL_INCLUDES IS 'Rule to have blackout and agent down time included in SLA' ; COMMENT ON COLUMN MGMT_SL_RULES.EXPECTED_SL IS 'Expected SL for this service' ; rem rem MGMT_SL_RULES_HISTORY rem Purpose - This table serves as the audit table for storing rem prev sla definition rem rem Columns - In addtion to all columns in service level rules rem table this also saves the date when this entry was rem created CREATE TABLE MGMT_SL_RULES_HISTORY (TARGET_GUID RAW(16) NOT NULL, DATE_SEQUENCE CHAR(7) NOT NULL, START_TIME CHAR(4) NOT NULL, END_TIME CHAR(4) NOT NULL, AVAIL_INCLUDES NUMBER NOT NULL, EXPECTED_SL NUMBER NOT NULL, LOAD_TIMESTAMP DATE NOT NULL ) STORAGE (FREELISTS 4) INITRANS 2 MONITORING; -- Comment on the columns COMMENT ON TABLE MGMT_SL_RULES_HISTORY IS 'SL rules defn' ; COMMENT ON COLUMN MGMT_SL_RULES_HISTORY.TARGET_GUID IS 'Target guid for the Service' ; COMMENT ON COLUMN MGMT_SL_RULES_HISTORY.DATE_SEQUENCE IS 'Days considered business and non-business days' ; COMMENT ON COLUMN MGMT_SL_RULES_HISTORY.START_TIME IS 'Business hour start on a business day' ; COMMENT ON COLUMN MGMT_SL_RULES_HISTORY.END_TIME IS 'Business hour end on a business day' ; COMMENT ON COLUMN MGMT_SL_RULES_HISTORY.AVAIL_INCLUDES IS 'Rule to have blackout and agent down time included in SLA' ; COMMENT ON COLUMN MGMT_SL_RULES_HISTORY.EXPECTED_SL IS 'Expected SL for this service' ; COMMENT ON COLUMN MGMT_SL_RULES_HISTORY.LOAD_TIMESTAMP IS 'Date time when the SL rule was redefined' ; rem rem MGMT_SL_METRICS rem rem PURPOSE - This table stores the list of metrics which are rem included SLA defn rem rem COLUMNS rem target_guid - Unique ID the target rem metric_guid - unique id of the target rem rem NOTES rem CREATE TABLE MGMT_SL_METRICS (TARGET_GUID RAW(16) NOT NULL, METRIC_GUID RAW(16) NOT NULL, KEY_VALUE VARCHAR2(256) ) STORAGE (FREELISTS 4) INITRANS 2 MONITORING; -- Comments for the table COMMENT ON TABLE MGMT_SL_METRICS IS 'Metric included in SLA defn' ; COMMENT ON COLUMN MGMT_SL_METRICS.TARGET_GUID IS 'Target guid of the Service' ; COMMENT ON COLUMN MGMT_SL_METRICS.METRIC_GUID IS 'Metric guid included in SLA for the Service' ; COMMENT ON COLUMN MGMT_SL_METRICS.KEY_VALUE IS 'Key Value of the metric included in SLA for the service' ; rem rem MGMT_SL_METRICS_HISTORY rem PURPOSE - This table serves as the audit table for the metric rem rem COLUMNS rem This table has the same set of columns except it has a rem load time stamp indicating when the audit entry was made. rem CREATE TABLE MGMT_SL_METRICS_HISTORY (TARGET_GUID RAW(16) NOT NULL, METRIC_GUID RAW(16) NOT NULL, KEY_VALUE VARCHAR2(256) , LOAD_TIMESTAMP DATE NOT NULL ) STORAGE (FREELISTS 4) INITRANS 2 MONITORING; COMMENT ON TABLE MGMT_SL_METRICS_HISTORY IS 'Historical SL rules' ; COMMENT ON COLUMN MGMT_SL_METRICS_HISTORY.TARGET_GUID IS 'Target guid of the Service' ; COMMENT ON COLUMN MGMT_SL_METRICS_HISTORY.METRIC_GUID IS 'Metric guid included in SLA for the Service' ; COMMENT ON COLUMN MGMT_SL_METRICS_HISTORY.KEY_VALUE IS 'Key Value of the metric included in SLA for the service' ; COMMENT ON COLUMN MGMT_SL_METRICS_HISTORY.LOAD_TIMESTAMP IS 'Time stamp when the service rule was redefined' ;