Rem
Rem $Header: storage_ui_common_views.sql 18-aug-2006.13:52:08 mnihalan Exp $
Rem
Rem storage_ui_common_views.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      storage_ui_common_views.sql - 
Rem        contains views used in the host and group  
Rem        storage UI backend code.
Rem
Rem    DESCRIPTION
Rem      contains views used in both host AND group storage
Rem      UI code.
Rem
Rem    NOTES
Rem      . The views with names beginning with "mgmt_v" 
Rem        must not to be exposed as public views. 
Rem      . If a view refers to mgmt_storage_report_ui_targets, then
Rem        you need to populate the temporary table 
Rem        mgmt_storage_report_ui_targets using the method 
Rem        storage_ui_common_pkg.insert_targets_for_ui_queries
Rem        for the view to work.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    mnihalan    08/18/06 - fix backport 5475298
Rem    sreddy      07/23/05 - fix for bug#4253179 
Rem                           mgmt_v_storage_coll_errors is restricted to
Rem                           show errors at DB, ASM level only if an 
Rem                           associated host snapshot exists. Fixes the bug
Rem                           of listing collection errors from db and asm 
Rem                           targets that do not have associated host 
Rem                           snapshot. Also scope it down to target list
Rem                           from mgmt_storage_ui_targets table.
Rem    sreddy      07/23/05 - Cleanup: removed mgmt_storage_ui_targets predicate
Rem                           from mgmt_v_timeseries_daily_hist view, it is
Rem                           redundant.
Rem    sreddy      07/23/05 - Cleanup: removed predicates from 
Rem                           mgmt_v_db_alloc_used that were redundant with 
Rem                           respect to mgmt_v_host_dbs view
Rem    sreddy      07/23/05 - Fixed mgmt_v_host_dbs view such that
Rem                           it lists rac dbs + non_rac dbs having
Rem                           no cluster db association. Fixes the bug of
Rem                           including dbs which have cluster db association.
Rem    sreddy      07/23/05 - Cleanup: Added target_type column to 
Rem                           mgmt_v_storage_stats view. Join on this
Rem                           view should be against target_name, target_type
Rem    sreddy      07/23/05 - Modified views to work with ecm_snapshot_id
Rem                           column of mgmt_storage_report_ui_targets
Rem    sreddy      07/23/05 - Filter data of get_storage_vendor_distrib view
Rem                           to snapshots of mgmt_storage_report_ui_targets
Rem    sreddy      07/23/05 - Cleanup: define and use constants
Rem    ajere       07/11/05 - Fix bug# 4222308 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    rmenon      03/18/05 - 
Rem    chyu        03/18/05 - removing the echo off 
Rem    rmenon      03/10/05 - changed db size calculation. it now depends
Rem                           only on tablespace size (not on control files
Rem                           size and redo log sizes.
Rem    rmenon      02/21/05 - formating changes
Rem    rmenon      02/07/05 - fix for 399806 - fixed by looking at tkprof
Rem                           there were two problems:
Rem                           a. There was a full scan on mgmt_targets
Rem                              that I now avoid by adding the target_type
Rem                              column in my views AND using them
Rem                              in the queries to give mor info to the CBO
Rem                              for it to be able to use index.
Rem                           b. There were sorts using physical IO which
Rem                              can be a bottleneck. If I alter the
Rem                              sort_area_size AND hash_area_size then
Rem                              these disappear. If this problem occurs
Rem                              again, we can at the minimum change these
Rem                              settings at a session level perhaps. 
Rem                              Formatted as per EM coding standards.
Rem    rmenon      01/24/05 - fixed vendor dist resource bundle error
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    rmenon      01/24/05 - fixed vendor dist resource bundle error
Rem                           - had removed resource bundle NFS
Rem                           in my previous transaction but forgot to
Rem                           change the vendor distribution query.
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    rmenon      12/22/04 - 
Rem    rmenon      11/22/04 - rounded the average value in the history stats;
Rem                           corrected the metric errors query to pick
Rem                           mgmt_current_metric_errors instead of
Rem                           mgmt_metric_errors which has historical data.
Rem    rmenon      11/12/04 - 
Rem    rmenon      10/28/04 - corrected queries from mgmt_metrics
Rem                           to include type_meta_ver and
Rem                           category properties conditions.
Rem    rmenon      10/13/04 - 
Rem    rmenon      10/09/04 - rmenon_storage_group_ui_and_sql_code_refactor
Rem    rmenon      09/20/04 - Created
Rem

-- SQLPLUS DEFINES begin
DEFINE ASM_DB_DG_USAGE_METRIC      = Database_DiskGroup_Usage
DEFINE ASM_DB_DG_USAGE_COLL        = Database_DiskGroup_Usage
DEFINE ASM_DG_USAGE_METRIC         = DiskGroup_Usage
DEFINE ASM_DG_USAGE_COLL           = DiskGroup_Usage_10_2
DEFINE ASM_FREE_MB_COLUMN          = free_mb
DEFINE ASM_TARGET_TYPE             = osm_instance
DEFINE ASM_TOTAL_MB_COLUMN         = total_mb
DEFINE ASM_TYPE_METRIC_COLUMN      = type
DEFINE ASM_USABLE_FILE_MB_COLUMN   = usable_file_mb

DEFINE DB_TARGET_TYPE              = oracle_database
DEFINE DB_CONFIG_COLL              = oracle_dbconfig
DEFINE DB_CONFIG_SNAPSHOT_TYPE     = oracle_dbconfig

DEFINE HOST_TARGET_TYPE            = host
DEFINE HOST_STORAGE_COLL           = host_storage
DEFINE HOST_STORAGE_REP_METRIC     = host_storage_history
DEFINE GROUP_TARGET_TYPE           = composite
DEFINE GROUP_STORAGE_REP_METRIC    = group_storage_history

DEFINE RAC_DB_TARGET_TYPE          = rac_database
DEFINE RAC_DB_CONFIG_COLL          = oracle_racconfig
DEFINE RAC_DB_CONFIG_SNAPSHOT_TYPE = oracle_racconfig

DEFINE CLUSTER_DATABASE_PROPERTY   = cluster_database
DEFINE DB_UNIQUE_NAME_PROPERTY     = db_unique_name
DEFINE INSTANCE_PROPERTY           = INSTANCE

DEFINE LOCAL_FILESYSTEM            = LOCAL_FILESYSTEM
DEFINE NFS                         = NFS
DEFINE NFSWRITE                    = NFSWRITE
DEFINE OS_DISK                     = OS_DISK
DEFINE UNKNOWN                     = Unknown
DEFINE VOLUME_MANAGER              = VOLUME_MANAGER
DEFINE WRITEABLE_NFS               = WRITEABLE_NFS

---------------------------------------------------------
-- NAME: mgmt_v_storage_coll_errors
--
-- PURPOSE: 
--   This view gets metric errrors related to storage
--   data for all config snapshots related to host storage.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_storage_coll_errors
as
  SELECT tgt.host_name, 
         tgt.target_name, 
         met.target_type, 
         met.metric_label,
         err.collection_timestamp, 
         metric_error_message as
         metric_collection_error_msg
  FROM mgmt_current_metric_errors err,
       mgmt_targets tgt,
       mgmt_metrics met
  WHERE tgt.target_guid = err.target_guid
    AND met.metric_guid = err.metric_guid
    AND met.type_meta_ver = tgt.type_meta_ver
    AND (met.category_prop_1 = ' ' OR
         met.category_prop_1 =  tgt.category_prop_1) 
    AND (met.category_prop_2 = ' ' OR
         met.category_prop_2 =  tgt.category_prop_2)
    AND (met.category_prop_3 = ' ' OR
         met.category_prop_3 =  tgt.category_prop_3)
    AND (met.category_prop_4 = ' ' OR
         met.category_prop_4 =  tgt.category_prop_4)
    AND (met.category_prop_5 = ' ' OR
         met.category_prop_5 =  tgt.category_prop_5)
    AND ((tgt.target_type = '&HOST_TARGET_TYPE' AND
          err.coll_name = '&HOST_STORAGE_COLL' AND
          EXISTS
          (select uit.target_guid
             from mgmt_storage_report_ui_targets uit
            where uit.target_guid = tgt.target_guid)) OR
          (((tgt.target_type = '&DB_TARGET_TYPE' AND
             err.coll_name = '&DB_CONFIG_COLL') OR
             (tgt.target_type = '&RAC_DB_TARGET_TYPE' AND
              err.coll_name = '&RAC_DB_CONFIG_COLL') OR
             (tgt.target_type = '&ASM_TARGET_TYPE' AND
              err.coll_name IN ('&ASM_DB_DG_USAGE_COLL',
                                '&ASM_DG_USAGE_COLL'))) AND
             EXISTS
             (
               select uit.ecm_snapshot_id
                 from mgmt_storage_report_ui_targets uit,
                      mgmt_targets t
                where uit.ecm_snapshot_id is not null
                  and uit.target_name = t.host_name
                  and t.target_guid = err.target_guid
             )
          )
        );
         
---------------------------------------------------------
-- NAME: mgmt_v_storage_lyr_existence
--
-- PURPOSE: 
--   This view gets counts for each of the following
--   storage layer entities:
--   1. databases (rac AND non-rac )
--   2. local fs
--   3. writeable nfs
--   4. volumes
--   5. ASM
--   6. disks
--
--   We use this to check if the history data needs to
--   be populated for a layer (otherwise, say a host
--   does not have volumes - you get unnecessary data
--   containing all zeros in the rollup tables populated
--   by storage repository metrics.)
--
-- IMPLMENTATION NOTES:
--   NOTE: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_storage_lyr_existence
as
  SELECT db_exists_flag, asm_exists_flag, 
         rac_exists_flag, local_fs_exists_flag, 
         writeable_nfs_exists_flag, vol_exists_flag, 
         disk_exists_flag
  FROM
  (
    SELECT max( DECODE( tgt.target_type, 
                        '&DB_TARGET_TYPE', 'Y', 'N')) db_exists_flag,
           max( DECODE( tgt.target_type, 
                        '&ASM_TARGET_TYPE', 'Y', 'N')) asm_exists_flag,
           max( DECODE( tgt.target_type, 
                        '&RAC_DB_TARGET_TYPE', 'Y', 'N')) rac_exists_flag
    FROM mgmt_targets tgt,
         mgmt_storage_report_ui_targets uit
    WHERE uit.ecm_snapshot_id IS NOT NULL
      AND uit.target_name = tgt.host_name
      AND tgt.target_type in ( '&ASM_TARGET_TYPE', 
                               '&DB_TARGET_TYPE',
                               '&RAC_DB_TARGET_TYPE' )
  ),
  (
    SELECT max( DECODE( storage_layer, 
                        '&LOCAL_FILESYSTEM', 'Y', 'N')) local_fs_exists_flag,
           max( DECODE( storage_layer||nfs_mount_privilege, 
                        '&NFSWRITE', 'Y', 'N')) writeable_nfs_exists_flag,
           max( DECODE( storage_layer, 
                        '&VOLUME_MANAGER', 'Y', 'N')) vol_exists_flag,
           max( DECODE( storage_layer, 
                        '&OS_DISK', 'Y', 'N')) disk_exists_flag
    FROM mgmt_v_storage_report_data data,
         mgmt_storage_report_ui_targets uit
    WHERE uit.ecm_snapshot_id = data.ecm_snapshot_id
  );

---------------------------------------------------------
-- NAME: mgmt_v_asm_db_dg_usage
--
-- PURPOSE: 
--   This view gets disk groups information about ASMs
--   installed in one or more hosts.
-- IMPLMENTATION NOTES:
--   NOTE: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_asm_db_dg_usage as
  SELECT t.host_name, 
         t.target_name,
         t.target_guid,
         key_part1_value disk_group_name,
         key_part2_value database_name,
         max(DECODE( m.metric_column, '&ASM_TOTAL_MB_COLUMN', 
           c.value, null )) total_mb,
         max(DECODE( p.property_name, '&DB_UNIQUE_NAME_PROPERTY',
           p.property_value, null)) db_unique_name,
         max(DECODE( p.property_name, '&CLUSTER_DATABASE_PROPERTY',
           p.property_value, null)) cluster_db_flag
  FROM mgmt_current_metrics c,
       mgmt_metrics_composite_keys k,
       mgmt_metrics m, 
       mgmt_targets t,
       mgmt_target_properties p,
       mgmt_storage_report_ui_targets uit
  WHERE uit.ecm_snapshot_id IS NOT NULL
    AND t.host_name = uit.target_name
    AND c.target_guid = t.target_guid
    AND t.target_type = '&ASM_TARGET_TYPE'
    AND t.target_guid = p.target_guid
    AND p.property_type = '&INSTANCE_PROPERTY'
    AND p.property_name in ( '&DB_UNIQUE_NAME_PROPERTY', 
                             '&CLUSTER_DATABASE_PROPERTY')
    AND c.key_value = k.composite_key
    AND k.target_guid = c.target_guid
    AND c.metric_guid = m.metric_guid
    AND m.target_type = '&ASM_TARGET_TYPE'
    AND m.metric_name = '&ASM_DB_DG_USAGE_METRIC'
    AND m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = ' ' OR
         m.category_prop_1 =  t.category_prop_1) 
    AND (m.category_prop_2 = ' ' OR
         m.category_prop_2 =  t.category_prop_2)
    AND (m.category_prop_3 = ' ' OR
         m.category_prop_3 =  t.category_prop_3)
    AND (m.category_prop_4 = ' ' OR
         m.category_prop_4 =  t.category_prop_4)
    AND (m.category_prop_5 = ' ' OR
         m.category_prop_5 =  t.category_prop_5)
    AND m.metric_column in( '&ASM_TOTAL_MB_COLUMN')
  GROUP BY t.host_name, t.target_name, t.target_guid, 
           key_part1_value, key_part2_value;

-- for asm_size, asm_unallocated
---------------------------------------------------------
-- NAME: mgmt_v_asm_dg_usage
--
-- PURPOSE: 
--   This view gets total size, usable file size, AND some
--   other information related to ASM on one or multiple
--   hosts.
--
-- IMPLMENTATION NOTES:
--   NOTE: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_asm_dg_usage as
    SELECT t.host_name,
           t.target_name,
           c.key_value disk_group_name,
           max( DECODE( m.metric_column, '&ASM_TOTAL_MB_COLUMN', c.value, 0)) 
             as total_mb,
           max( DECODE( m.metric_column, '&ASM_USABLE_FILE_MB_COLUMN', 
             c.value, 0)) as usable_file_mb,
           max(DECODE( p.property_name, '&DB_UNIQUE_NAME_PROPERTY',
             p.property_value, null)) as db_unique_name,
           max(DECODE( p.property_name, '&CLUSTER_DATABASE_PROPERTY',
             p.property_value, null)) as cluster_db_flag
    FROM mgmt_current_metrics c,  
         mgmt_metrics m,
         mgmt_targets t, 
         mgmt_target_properties p,
         mgmt_storage_report_ui_targets uit
    WHERE uit.ecm_snapshot_id IS NOT NULL
      AND t.host_name = uit.target_name
      AND t.target_guid = c.target_guid
      AND t.target_type = '&ASM_TARGET_TYPE'
      AND m.target_type = t.target_type
      AND c.metric_guid = m.metric_guid
      AND m.metric_name = '&ASM_DG_USAGE_METRIC'
      AND m.metric_column in ('&ASM_TOTAL_MB_COLUMN', 
                              '&ASM_FREE_MB_COLUMN', 
                              '&ASM_USABLE_FILE_MB_COLUMN',
                              '&ASM_TYPE_METRIC_COLUMN')
      AND m.type_meta_ver = t.type_meta_ver
      AND (m.category_prop_1 = ' ' OR
           m.category_prop_1 =  t.category_prop_1) 
      AND (m.category_prop_2 = ' ' OR
           m.category_prop_2 =  t.category_prop_2)
      AND (m.category_prop_3 = ' ' OR
           m.category_prop_3 =  t.category_prop_3)
      AND (m.category_prop_4 = ' ' OR
           m.category_prop_4 =  t.category_prop_4)
      AND (m.category_prop_5 = ' ' OR
           m.category_prop_5 =  t.category_prop_5)
      AND t.target_guid = p.target_guid
      AND p.property_type = '&INSTANCE_PROPERTY'
      AND p.property_name in ( '&DB_UNIQUE_NAME_PROPERTY', '&CLUSTER_DATABASE_PROPERTY')
    GROUP BY t.host_name, t.target_name, c.key_value;

---------------------------------------------------------
-- NAME: mgmt_v_host_dbs
--
-- PURPOSE: 
--   This view returns a list of databases on a host. It
--   makes sure not to return a RAC target more than
--   once (e.g. as a RAC target AND as a non-RAC target
--   instance.) 
--
-- IMPLMENTATION NOTES:
--   NOTE: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_host_dbs AS
-- have to join with mgmt_targets to get required emd_url column
  SELECT tgt.host_name, snap.target_name, snap.target_guid,
         snap.ecm_snapshot_id, snap.target_type, tgt.emd_url
    FROM MGMT$ECM_CURRENT_SNAPSHOTS snap,
         MGMT_TARGETS tgt,
         MGMT_STORAGE_REPORT_UI_TARGETS uit
   WHERE uit.ecm_snapshot_id IS NOT NULL
     AND uit.target_name = tgt.host_name
     AND tgt.target_type = '&RAC_DB_TARGET_TYPE'
     AND tgt.target_guid = snap.target_guid
     AND snap.snapshot_type = '&RAC_DB_CONFIG_SNAPSHOT_TYPE'
   UNION ALL
  SELECT tgt.host_name, snap.target_name, snap.target_guid,
         snap.ecm_snapshot_id, snap.target_type, tgt.emd_url
    FROM MGMT$ECM_CURRENT_SNAPSHOTS snap,
         MGMT_TARGETS tgt,
         MGMT_STORAGE_REPORT_UI_TARGETS uit
   WHERE uit.ecm_snapshot_id IS NOT NULL
     AND uit.target_name = tgt.host_name
     AND tgt.target_type = '&DB_TARGET_TYPE'
     AND tgt.target_guid = snap.target_guid
     AND snap.snapshot_type = '&DB_CONFIG_SNAPSHOT_TYPE'
     AND NOT EXISTS
         (
           SELECT member_target_name 
             FROM MGMT_TARGET_MEMBERSHIPS
            WHERE member_target_guid = tgt.target_guid 
              AND composite_target_type = '&RAC_DB_TARGET_TYPE'
         );

---------------------------------------------------------
-- NAME: mgmt_v_db_alloc_used
--
-- PURPOSE: 
--   This view gets storage allocated to databases (RAC AND/or
--   non RAC) for a given set of hosts.
--
-- IMPLMENTATION NOTES:
--   For this view to work, you need to populate the temporary
--   table mgmt_storage_report_ui_targets
--   using the method storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_db_alloc_used
as
  -- tablespace size, used
  SELECT tgt.target_name,
         tgt.target_type,
         tbspAlloc.ALLOCATED_SPACE  allocated,
         tbspUsed.USED_SPACE  used
    FROM 
       (SELECT tg.target_guid,  
          ROUND(SUM(m.value * 1024 * 1024), 2)   AS ALLOCATED_SPACE  
        FROM  
         mgmt$metric_current m,  
         mgmt_v_host_dbs tg 
       WHERE 
         m.metric_name='tbspAllocation' AND
         m.metric_column='spaceAllocated'  AND 
         m.target_guid=tg.target_guid  group by tg.target_guid) tbspAlloc,
      (SELECT tg.target_guid,  
          ROUND(SUM(m.value * 1024 * 1024), 2)   AS USED_SPACE  
        FROM  
         mgmt$metric_current m,  
         mgmt_v_host_dbs tg 
       WHERE 
         m.metric_name='tbspAllocation' AND
         m.metric_column='spaceUsed'  AND 
         m.target_guid=tg.target_guid  group by tg.target_guid) tbspUsed,
         mgmt_v_host_dbs tgt
  WHERE tgt.target_guid = tbspAlloc.target_guid and
	tgt.target_guid = tbspUsed.target_guid;
---------------------------------------------------------
-----     storage history related views begin      ------
---------------------------------------------------------

---------------------------------------------------------
-- NAME: mgmt_v_storage_stats
--
-- PURPOSE: 
--   This view gets storage history stats for
--   a host FROM mgmt_metrics_1day AND mgmt_current_metrics. 
--   We need to also consider mgmt_current_metrics because
--   the last_known_value is shown as part of the stats
--   AND needs to be considered in other statistics such
--   as average, maximum etc. for the UI to make sense.
--
-- IMPLMENTATION NOTES:
--   NOTE 1: You need to store the following values in the 
--           storage_context for this view to give correct 
--           answers by using a command like:
--           
--           SQL> exec storage_ui_util_pkg.set_storage_context( 
--                  'history_period_in_days', 90)
--   NOTE 2: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_storage_stats
as
-- here we take the average as well apart FROM
-- the other values selected in the inner queries.
SELECT target_name,
       target_type,
       metric_name,
       metric_column,
       round(sum_value/sum_sample_count, 2) as average,
       maximum,
       minimum,
       last_known_value
FROM
(
  -- we get the sum of all values, sample count,
  -- max, minimum AND last known value here.
  SELECT target_name,
         target_type,
         metric_name,
         metric_column,
         sum( value) sum_value,
         sum( sample_count) sum_sample_count,
         max( maximum) maximum,
         min( minimum ) minimum,
         max( last_known_value ) as last_known_value
  FROM
  (
    -- since we want to include the current value
    -- alongwith the daily rollup values, we take
    -- a union of SELECT FROM current value AND
    -- daily rollup values. The SELECT immediately
    -- below selects values FROM the mgmt_current_metrics,
    -- treating the current value as maximum, minimum etc.
    -- note that we need to get the sample count as well
    -- AND in this case, it would be always 1 since there
    -- is only one record for a metric in the
    -- mgmt_current_metrics.
    SELECT tgt.target_name,
           tgt.target_type,
           met.metric_name,
           met.metric_column,
           curr.value as maximum,
           curr.value as minimum,
           curr.value as last_known_value,
           1 as sample_count,
           curr.value as value
    FROM mgmt_metrics met, 
         mgmt_targets tgt,
         mgmt_current_metrics curr
    WHERE tgt.target_guid = curr.target_guid
      AND ((tgt.target_type = '&HOST_TARGET_TYPE' AND
            met.metric_name = '&HOST_STORAGE_REP_METRIC') OR
           (tgt.target_type = '&GROUP_TARGET_TYPE' AND
            met.metric_name = '&GROUP_STORAGE_REP_METRIC'))
      AND met.type_meta_ver = tgt.type_meta_ver
      AND (met.category_prop_1 = ' ' OR
           met.category_prop_1 =  tgt.category_prop_1) 
      AND (met.category_prop_2 = ' ' OR
           met.category_prop_2 =  tgt.category_prop_2)
      AND (met.category_prop_3 = ' ' OR
           met.category_prop_3 =  tgt.category_prop_3)
      AND (met.category_prop_4 = ' ' OR
           met.category_prop_4 =  tgt.category_prop_4)
      AND (met.category_prop_5 = ' ' OR
           met.category_prop_5 =  tgt.category_prop_5)
      AND met.metric_guid = curr.metric_guid
      AND collection_timestamp >= sysdate - 
            to_number(sys_context('storage_context', 
                      'history_period_in_days' ))
    UNION ALL
    -- the SELECT below gets maximum, minimum, 
    -- sum of all values, last known value
    -- AND sum of sample counts
    -- FROM the daily rollup table. Note that
    -- last known value always comes FROM the
    -- SELECT above (the one FROM mgmt_current_metrics)
    -- so we just use a max(0) in the query below - the
    -- max existing for the GROUP BY to work.
    SELECT tgt.target_name,
           tgt.target_type,
           met.metric_name,
           met.metric_column,
           max(daily.value_maximum) as maximum,
           min(daily.value_minimum) as minimum,
           max( 0 ) as last_known_value,
           sum(daily.sample_count ) as sample_count,
           sum(daily.value_average*sample_count) as value
    FROM mgmt_metrics met, 
         mgmt_targets tgt,
         mgmt_metrics_1day daily
    WHERE tgt.target_guid = daily.target_guid
      AND ((tgt.target_type = '&HOST_TARGET_TYPE' AND
            met.metric_name = '&HOST_STORAGE_REP_METRIC') OR
           (tgt.target_type = '&GROUP_TARGET_TYPE' AND
            met.metric_name = '&GROUP_STORAGE_REP_METRIC'))
      AND met.type_meta_ver = tgt.type_meta_ver
      AND (met.category_prop_1 = ' ' OR
           met.category_prop_1 =  tgt.category_prop_1) 
      AND (met.category_prop_2 = ' ' OR
           met.category_prop_2 =  tgt.category_prop_2)
      AND (met.category_prop_3 = ' ' OR
           met.category_prop_3 =  tgt.category_prop_3)
      AND (met.category_prop_4 = ' ' OR
           met.category_prop_4 =  tgt.category_prop_4)
      AND (met.category_prop_5 = ' ' OR
           met.category_prop_5 =  tgt.category_prop_5)
      AND met.metric_guid = daily.metric_guid
      AND rollup_timestamp >= sysdate - 
            to_number(sys_context('storage_context', 
                      'history_period_in_days' ))
    GROUP BY tgt.target_name, tgt.target_type, met.metric_name, met.metric_column
  )
  GROUP BY target_name, target_type, metric_name, metric_column
);

---------------------------------------------------------------
-----  storage history time series data related views begin ---
---------------------------------------------------------------

---------------------------------------------------------
-- NAME: mgmt_v_daily_history
--
-- PURPOSE: 
--   This view gets storage history data for a host
--   FROM mgmt_metrics_1day. It is used by another view
--   mgmt_v_timeseries_daily_hist below to get the
--   data for the time series charts in the UI.
--
-- IMPLMENTATION NOTES:
--   NOTE 1: You need to store the following values in the 
--           storage_context for this view to give correct 
--           answers by using a command like:
--           
--           SQL> exec storage_ui_util_pkg.set_storage_context( 
--                  'history_period_in_days', 90)
--   NOTE 2: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_daily_history 
as
  SELECT tgt.target_type,
         tgt.target_name,
         met.metric_name,
         met.metric_guid,
         met.metric_column,
         rollup_timestamp,
         value_average as value
  FROM mgmt_metrics met, 
       mgmt_metrics_1day day,
       mgmt_targets tgt
  WHERE tgt.target_guid = day.target_guid
    AND tgt.target_name in
        ( SELECT target_name
          FROM mgmt_storage_report_ui_targets
        )
    AND met.metric_guid = day.metric_guid
    AND met.metric_name in( '&HOST_STORAGE_REP_METRIC', 
                            '&GROUP_STORAGE_REP_METRIC' )
    AND met.type_meta_ver = tgt.type_meta_ver
    AND (met.category_prop_1 = ' ' OR
         met.category_prop_1 =  tgt.category_prop_1) 
    AND (met.category_prop_2 = ' ' OR
         met.category_prop_2 =  tgt.category_prop_2)
    AND (met.category_prop_3 = ' ' OR
         met.category_prop_3 =  tgt.category_prop_3)
    AND (met.category_prop_4 = ' ' OR
         met.category_prop_4 =  tgt.category_prop_4)
    AND (met.category_prop_5 = ' ' OR
         met.category_prop_5 =  tgt.category_prop_5)
    AND rollup_timestamp >= sysdate - 
          to_number(sys_context('storage_context', 
                    'history_period_in_days' ));

---------------------------------------------------------
-- NAME: mgmt_v_timeseries_daily_hist
--
-- PURPOSE: 
--   This view gets data for the time series charts in
--   storage ui. It uses the results of the view
--   mgmt_v_daily_history above to combine the 
--   data FROM the mgmt_metrics_1day AND mgmt_current_metrics.
--
-- IMPLMENTATION NOTES:
--   NOTE 1: You need to store the following values in the 
--           storage_context for this view to give correct 
--           answers by using a command like:
--           
--           SQL> exec storage_ui_util_pkg.set_storage_context( 
--                  'history_period_in_days', 90)
--   NOTE 2: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
---------------------------------------------------------
CREATE OR REPLACE VIEW mgmt_v_timeseries_daily_hist
as
  SELECT target_type,
         target_name,
         metric_name, 
         metric_column, 
         rollup_timestamp, 
         value
  FROM mgmt_v_daily_history
  UNION ALL
  SELECT tgt.target_type, 
         tgt.target_name, 
         metric_name, 
         met.metric_column,
         collection_timestamp,
         curr.value as value
  FROM mgmt_metrics met,
       mgmt_current_metrics curr,
       mgmt_targets tgt
  WHERE tgt.target_guid = curr.target_guid
    AND met.type_meta_ver = tgt.type_meta_ver
    AND met.metric_name in( '&HOST_STORAGE_REP_METRIC', 
                            '&GROUP_STORAGE_REP_METRIC' )
    AND (met.category_prop_1 = ' ' OR
         met.category_prop_1 =  tgt.category_prop_1) 
    AND (met.category_prop_2 = ' ' OR
         met.category_prop_2 =  tgt.category_prop_2)
    AND (met.category_prop_3 = ' ' OR
         met.category_prop_3 =  tgt.category_prop_3)
    AND (met.category_prop_4 = ' ' OR
         met.category_prop_4 =  tgt.category_prop_4)
    AND (met.category_prop_5 = ' ' OR
         met.category_prop_5 =  tgt.category_prop_5)
    AND met.metric_guid = curr.metric_guid
    AND collection_timestamp >
        (SELECT nvl(max(rollup_timestamp), 
         to_date('01-01-0001', 'MM-DD-YYYY' ) )
         FROM mgmt_v_daily_history)
  ORDER BY 5 desc;

---------------------------------------------------------
-- NAME: mgmt_v_strg_flat_memberships
--
-- PURPOSE: 
--    This view gives a list of all members of a composite target
--    I did not use the view mgmt_flat_target_memberships since 
--    it selects one more column (that seems to be doing some 
--    extra work) based on the flag "is_group_memb" which I 
--    don't think I need.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_strg_flat_memberships
AS
  SELECT ct.target_name composite_target_name,
         ct.target_type composite_target_type,
         ct.target_guid composite_target_guid,
         mt.target_name member_target_name,
         mt.target_type member_target_type,
         mt.target_guid member_target_guid
  FROM   mgmt_flat_target_assoc a,
         mgmt_targets ct,
         mgmt_targets mt
  WHERE a.is_membership = 1
    AND ct.target_guid = a.source_target_guid
    AND mt.target_guid = a.assoc_target_guid;

---------------------------------------------------------------
-----  storage vendor distribution data views begin         ---
---------------------------------------------------------------

---------------------------------------------------------
-- NAME: mgmt_v_vendor_distribution
--
-- PURPOSE: 
--   This view gets storage vendor distribution for nfs AND
--   disk layers.
--
-- IMPLMENTATION NOTES:
---------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_vendor_distribution
as
  SELECT ecm_snapshot_id, 
         DECODE( vendor, null, '&UNKNOWN', vendor) vendor, 
         DECODE( storage_layer, '&NFS', 
                 '&WRITEABLE_NFS', storage_layer ) storage_layer,
         sizeb,
         global_unique_id
  FROM
  (
    SELECT data.ecm_snapshot_id, 
           DECODE( storage_layer||nfs_mount_privilege,
                   '&NFSWRITE', nfs_vendor,
                   '&OS_DISK', disk_vendor
                 ) as vendor,
           storage_layer,
           sizeb,
           global_unique_id
    FROM mgmt_v_storage_report_data data,
         mgmt_storage_report_ui_targets uit
    WHERE uit.ecm_snapshot_id = data.ecm_snapshot_id
      AND (storage_layer = '&OS_DISK' OR
           storage_layer||nfs_mount_privilege = '&NFSWRITE')
      AND em_query_flag LIKE '%_BOTTOM%'
  );