Rem drv: <create type="tables" pos="log/log_tables.sql+"/>
Rem
Rem $Header: rep_metric_tables.sql 16-aug-2005.15:43:48 gan Exp $
Rem
Rem rep_metric_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      metric_tables.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gan        08/15/05 - add opt_code 
Rem    gan        08/11/05 - add col repo_metric_only 
Rem    gsbhatia   06/26/05 - New repmgr header impl 
Rem    gan        03/01/05 - add column 
Rem    gsbhatia   02/13/05 - updating repmgr header 
Rem    gsbhatia   02/09/05 - updating repmgr header 
Rem    gsbhatia   02/07/05 - updating repmgr header 
Rem    ktlaw      01/11/05 - add repmgr header 
Rem    streddy    10/20/04 - Evaluation order support 
Rem    gan        08/17/04 - add monitoring 
Rem    streddy    08/09/04 - Add eval_func column 
Rem    streddy    10/07/03 - Add members_ready column 
Rem    streddy    09/25/03 - Added type_meta_ver support 
Rem    streddy    05/22/03 - Remove mgmt_targets_repo_side_avail
Rem    streddy    04/17/03 - Add error column to mgmt_metric_dependency
Rem    streddy    04/09/03 - streddy_composite_target_availability
Rem    streddy    04/08/03 - Created
Rem

REM
REM PURPOSE
REM     Table to track metric severity dependency definitions at
REM     target type level.
REM
REM  COLUMNS
REM     TARGET_TYPE      - The target type
REM     TYPE_META_VER    - Metadata version of the target type
REM     METRIC_GUID      - Unique id of the metric
REM     DEP_TARGET_TYPE  - Type of the dependency target
REM     DEP_METRIC_GUID  - Unique id of the dependency metric
REM     OPT_CODE         - optimization code for availability eval.
REM                        constant define in mgmt_global
REM                        G_REPO_SEV_OPT_NONE 0
REM                        G_REPO_SEV_OPT_AND 1
REM                        G_REPO_SEV_OPT_OR 2
REM
REM  NOTES   
REM     There is some level of denormalization here but it is 
REM     justified to do so as (i) it speeds up the queries;
REM     and (ii) we do nott expect too many rows for a given 
REM     target type.
REM
CREATE TABLE MGMT_METRIC_DEPENDENCY_DEF
(
   target_type       VARCHAR2(256) NOT NULL,
   type_meta_ver     VARCHAR2(8)   NOT NULL,
   metric_guid       RAW(16)       NOT NULL,
   dep_target_type   VARCHAR2(256) NOT NULL,
   dep_metric_guid   RAW(16)       NOT NULL,
   opt_code          NUMBER DEFAULT 0,
   CONSTRAINT PK_MGMT_METRIC_DEPENDENCY_DEF
    PRIMARY KEY(metric_guid, type_meta_ver, dep_target_type, dep_metric_guid)
)
INITRANS 2
  MONITORING;


REM
REM PURPOSE
REM     Table to track metric severity dependency data for each
REM     metric at instance level. The actual details are maintained
REM     in MGMT_METRIC_DEPENDENCY_DETAILS table.
REM
REM  COLUMNS
REM     TARGET_GUID      - Unique id of the target
REM     METRIC_GUID      - Unique id of the metric
REM     KEY_VALUE        - Optional Key Value
REM     RS_METRIC        - Is this response/status metric?
REM                      - 1 -> yes; 0->No
REM     EVAL_ORDER       - Evaluation order where 0 is leaf 
REM     EVAL_FUNC        - Metric evaluation function
REM                        The signatures for event based evaluation
REM                        is different from data based evaluation
REM     EVENT_METRIC     - 1 means event based; 0 means data based.
REM     DISABLED         - 1 means disabled; 0 means enabled.
REM     MEMBERS_READY    - 1 means ready; 0 means not ready        
REM     CAN_CALCULATE    - 1 means yes; 0 means no. We need at least one
REM                        instance of each of the dependency metrics in
REM                        order to calculate the severity of the metric.
REM     FORCE_CALCULATE  - 1 means evaluate the severity irrespective of
REM                        changes in member severties; this is done
REM                        whenever new members are added.
REM     ERROR_MSG        - Detailed error message if an error occured during
REM                        severity evaluations
REM     MIN_DEP_INTERVAL - The min. of dependent metric collection interval.
REM                        It is used when calculating the data metric.
REM     OPT_CODE         - optimization code for availability eval.
REM                        constant define in mgmt_global
REM                        G_REPO_SEV_OPT_NONE 0
REM                        G_REPO_SEV_OPT_AND 1
REM                        G_REPO_SEV_OPT_OR 2
REM     REPO_METRIC_ONLY - The target only has repository side metric. It 
REM                        is set for rs_metric =1 row. 1 for yes, 0 for no
CREATE TABLE MGMT_METRIC_DEPENDENCY
(
  target_guid         RAW(16)  NOT NULL,
  metric_guid         RAW(16)  NOT NULL,
  key_value           VARCHAR2(256) DEFAULT ' ',
  rs_metric           NUMBER(1) DEFAULT 0,
  eval_order          NUMBER(2) DEFAULT 0,
  eval_func           VARCHAR2(256) DEFAULT NULL,
  event_metric        NUMBER   DEFAULT 1,
  disabled            NUMBER   DEFAULT 0,
  members_ready       NUMBER   DEFAULT 0,
  can_calculate       NUMBER   DEFAULT 0,
  start_timestamp     DATE     DEFAULT NULL,
  force_calculate     NUMBER   DEFAULT 0,
  error_msg           VARCHAR2(4000) DEFAULT ' ',
  min_dep_interval    NUMBER   DEFAULT 5,
  opt_code            NUMBER   DEFAULT 0,
  repo_metric_only    NUMBER(1) DEFAULT 0,
  CONSTRAINT PK_MGMT_METRIC_DEPENDENCY
       PRIMARY KEY(target_guid, metric_guid, key_value)
) MONITORING;



REM
REM PURPOSE
REM     Table to track metric severity dependency details
REM
REM  COLUMNS
REM     TARGET_GUID      - Unique id of the target
REM     METRIC_GUID      - Unique id of the metric
REM     KEY_VALUE        - Key value of the metric 
REM     DEP_TARGET_GUID  - Unique id of the dependency target
REM     DEP_METRIC_GUID  - Unique id of the dependency metric
REM     DEP_TARGET_GUID  - Unique id of the dependency target
REM     DEP_METRIC_GUID  - Unique id of the dependency metric
REM     EDEP_TARGET_GUID  - Unique id of the effective dependency target
REM     EDEP_METRIC_GUID  - Unique id of the effective dependency metric
REM     DEP_KEY_VALUE    - Key value of the dependency metric 
REM     START_TIMESTAMP  - Timestamp starting which this dependency is
REM                        active
REM     DEP_COLL_NAME    - The name of the collection which collect data for the 
REM                        dependent metric/key
REM

CREATE TABLE MGMT_METRIC_DEPENDENCY_DETAILS
(
  target_guid         RAW(16)  NOT NULL,
  metric_guid         RAW(16)  NOT NULL,
  key_value           VARCHAR2(256) DEFAULT ' ',
  dep_target_guid     RAW(16)  NOT NULL,
  dep_metric_guid     RAW(16)  NOT NULL,
  edep_target_guid    RAW(16)  NOT NULL,
  edep_metric_guid    RAW(16)  NOT NULL,
  dep_key_value       VARCHAR2(256) DEFAULT ' ',
  start_timestamp     DATE,
  dep_coll_name       VARCHAR2(64),
   CONSTRAINT PK_MGMT_METRIC_DEP_DETAILS
      PRIMARY KEY(target_guid, metric_guid, key_value, dep_target_guid, dep_metric_guid, dep_key_value)
) MONITORING;