Rem
Rem $Header: storage_ui_group_views.sql 26-jul-2005.02:20:14 sreddy Exp $
Rem
Rem storage_ui_group_views.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      storage_ui_group_views.sql - 
Rem        contains views used in group storage reports UI code.
Rem
Rem    DESCRIPTION
Rem      views used to get data for group storage UI.
Rem
Rem    NOTES
Rem      NOTE 1: The views with names beginning with "mgmt_v" 
Rem              must not to be exposed as public views. 
Rem      NOTE 1 : For many views to work, you need to populate the temporary
Rem            table mgmt_storage_report_ui_targets using the method:
Rem            storage_ui_common_pkg.insert_targets_for_ui_queries.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sreddy      07/23/05 - Filter for hosts with valid snapshots in
Rem                           views, cleanup predicates
Rem    gsbhatia    06/26/05 - New repmgr header impl 
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/05/05 - 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    rmenon      12/09/04 - 
Rem    rmenon      11/29/04 - 
Rem    rmenon      11/10/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

---------------------------------------------------------------
---------------  group storage views begin --------------------
---------------------------------------------------------------
---------------------------------------------------------
-- NAME: mgmt_v_grp_hostwise_storage
--
-- PURPOSE: 
--   This view gets host wise storage data for a group.
--   It gets the data from the host storage history 
--   data which should exist in the repository metric
--   tables corresponding to host storage history.
--
-- 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_grp_hostwise_storage
as
select tgt.target_name,
       max( decode( met.metric_column, 'db_free', 
                    curr.value, 0) ) as db_free,
       max( decode( met.metric_column, 'db_used', 
                    curr.value, 0) ) as db_used,
       max( decode( met.metric_column, 'total_allocated', 
                    curr.value, 0) ) as total_allocated,
       max( decode( met.metric_column, 'total_unallocated', 
                    curr.value, 0) ) as total_unallocated,
       max( decode( met.metric_column, 'total_used', 
                    curr.value, 0) ) as total_used,
       max( decode( met.metric_column, 'total_free', 
                    curr.value, 0) ) as total_free,
       max( decode( met.metric_column, 'total_overhead', 
                    curr.value, 0) ) as total_overhead,
       max( decode( met.metric_column, 'disks_allocated', 
                    curr.value, 0) ) as local_disks_allocated,
       max( decode( met.metric_column, 'disks_unallocated', 
                    curr.value, 0) ) as local_disks_unallocated,
       max( decode( met.metric_column, 'local_fs_free', 
                    curr.value, 0) ) as local_fs_free,
       max( decode( met.metric_column, 'local_fs_used', 
                    curr.value, 0) ) as local_fs_used,
       max( decode( met.metric_column, 'writeable_nfs_free', 
                    curr.value, 0) ) as writeable_nfs_free,
       max( decode( met.metric_column, 'writeable_nfs_used', 
                    curr.value, 0) ) as writeable_nfs_used,
       max( decode( met.metric_column, 'vol_allocated', 
                    curr.value, 0) ) as vol_allocated,
       max( decode( met.metric_column, 'vol_unallocated', 
                    curr.value, 0) ) as vol_unallocated,
       max( decode( met.metric_column, 'vol_overhead', 
                    curr.value, 0) ) as vol_overhead,
       max( decode( met.metric_column, 'asm_allocated', 
                    curr.value, 0) ) as asm_allocated,
       max( decode( met.metric_column, 'asm_unallocated', 
                    curr.value, 0) ) as asm_unallocated,
       max( decode( met.metric_column, 'asm_overhead', 
                    curr.value, 0) ) as asm_overhead
  from mgmt_metrics met,
       mgmt_current_metrics curr,
       mgmt_targets tgt,
       mgmt_storage_report_ui_targets uit
  where uit.ecm_snapshot_id IS NOT NULL
    and uit.target_guid = tgt.target_guid
    and tgt.target_guid = curr.target_guid
    and tgt.target_type = met.target_type
    and met.type_meta_ver = tgt.type_meta_ver
    and met.metric_name = 'host_storage_history'
    and met.metric_guid = curr.metric_guid
    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)
  group by tgt.target_name;

---------------------------------------------------------
-- NAME: mgmt_v_storagelayerdata_exist
--
-- PURPOSE: 
--   This view gets existence flags for storage layers
--   ('Y if exists, 'N' if does not.) for a host in a group.
--   It depends on the storage history data for this info.
--
-- 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_storagelayerdata_exist
as
  select max(
           case
             when metric_column in( 'disks_allocated',
                                    'disks_unallocated' )
                  and nvl( value, 0 ) != 0
             then 'Y'
           else 'N'
           end) local_disks_exists,
         max(
           case
             when metric_column in( 'vol_unallocated',
                                    'vol_allocated',
                                    'vol_overhead' ) 
                  and nvl( value, 0 ) != 0
             then 'Y'
           else 'N'
           end ) volume_exists,
         max(
           case
             when metric_column in ( 'asm_unallocated',
                                     'asm_allocated',
                                     'asm_overhead' )
                  and nvl( value, 0 ) != 0
             then 'Y'
           else 'N'
           end) asm_exists,
         max(
           case
             when metric_column in( 'writeable_nfs_used',
                                    'writeable_nfs_free' )
                  and nvl( value, 0 ) != 0
             then 'Y'
           else 'N'
           end) writeable_nfs_exists,
         max(
           case
             when metric_column in ( 'db_used',
                                     'db_free' )
                  and nvl( value, 0 ) != 0
             then 'Y'
           else 'N'
           end) db_exists,
         max(
           case
             when metric_column in ( 'local_fs_used',
                                     'local_fs_free' )
                  and nvl( value, 0 ) != 0
             then 'Y'
           else 'N'
           end) local_fs_exists
  from mgmt_metrics met,
       mgmt_current_metrics curr,
       mgmt_targets tgt,
       mgmt_storage_report_ui_targets uit
  where uit.ecm_snapshot_id IS NOT NULL
    and uit.target_guid = tgt.target_guid
    and uit.target_type = met.target_type
    and tgt.target_guid = curr.target_guid
    and met.metric_name = 'host_storage_history'
    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;