Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/storage/storage_ui_group_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/22 17:47:20 yozhang Exp $ Rem Rem storage_ui_group_pkgbody.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem storage_ui_group_pkgbody.sql - Rem package to retrieve storage data for groups containing Rem at least one host. Rem Rem DESCRIPTION Rem package to retrieve storage data for groups containing Rem at least one host. Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem yozhang 03/19/09 - Fix perf bug 7643178: add join Rem sreddy 07/24/05 - remove hosts with no snapshots Rem from by_host views Rem ajdsouza 07/13/05 - add functions get_host_targets Rem rmenon 03/25/05 - Rem rmenon 02/24/05 - local file systems/db should have "size" Rem not "allocated" in UI. Rem rmenon 11/29/04 - Rem rmenon 10/26/04 - consolidated storage error logic Rem rmenon 10/13/04 - Rem rmenon 09/20/04 - Rem rmenon 09/08/04 - refresh storage, alloc summary, Rem usage summary logic Rem rmenon 08/24/04 - rmenon_storage_ui Rem rmenon 08/23/04 - Created Rem CREATE OR REPLACE PACKAGE BODY storage_ui_group_pkg AS /*-------------------------------------------------------------*/ /*---------- definition of public procedures/functions --------*/ /*-------------------------------------------------------------*/ ------------------------------------------------------ -- Name: get_alloc_summary_by_host -- -- Package: storage_ui_group_pkg -- -- Purpose: -- gets the allocation summary by host data to be rendered -- in storage group UI. -- -- IN Parameters: -- p_target_name - the target name of group -- p_target_type - the target type of group -- -- OUT Parameters: -- p_storage_unit - the storage unit in which UI will render -- p_asm_exists - 'Y', if at least one host in group has ASM -- data, 'N' otherwise -- p_volume_exists - 'Y', if at least one host in group has volume -- data, 'N' otherwise -- -- p_local_disk_exists - 'Y', if at least one host in group has -- local disks data, 'N' otherwise -- p_writeable_nfs_exists - 'Y', if at least one host in group has -- writeable NFS data, 'N' otherwise -- p_alloc_summary_cursor - ref cursor to allocation summary by host -- data ----------------------------------------------------------- procedure get_alloc_summary_by_host ( p_target_name in varchar2, p_target_type in varchar2, p_storage_unit in out varchar2, p_asm_exists in out varchar2, p_volume_exists in out varchar2, p_local_disk_exists in out varchar2, p_writeable_nfs_exists in out varchar2, p_alloc_summary_cursor in out sys_refcursor ) is l_select_portion varchar(1000); l_alloc_summary_cursor sys_refcursor; l_query varchar(4000); begin storage_ui_common_pkg.insert_targets_for_ui_queries( p_target_name, p_target_type ); STORAGE_UI_COMMON_PKG.DELETE_MISSING_SNAPSHOT_HOSTS; -- for the time being hard code the storage unit. p_storage_unit := storage_ui_util_pkg.GB; -- first set the flags that indicate if -- any of volumes, local disks, nfs or -- asm exist in at least one of the hosts -- belonging to the group. This is used -- to dynamically generate the correct list of -- columns for the query whose ref cursor -- gets returned to UI. select local_disks_exists, volume_exists, asm_exists, writeable_nfs_exists into p_local_disk_exists, p_volume_exists, p_asm_exists, p_writeable_nfs_exists from mgmt_v_storagelayerdata_exist; -- Depending on the storage layer existence flags, -- construct the select portion -- of the query whose cursor needs to be returned. The UI -- will then select appropriate columns based on the above -- flags which are also being returned by this procedure. -- NOTE: I did not put the logic to convert the storage data -- into p_storage_unit below since that makes the query -- string huge and cumbersome. Instead, I do this conversion -- in the middle tier based on the storage unit that this -- procedure returns. -- IMPORTANT: Do not change the order of the "if" statements. -- If you do, you need to change the order of all if statements -- used in the middle tier code to construct the UI table as well. l_select_portion := 'select target_name, total_allocated, ' || 'total_unallocated, ' || 'total_overhead '; if ( p_asm_exists = storage_ui_util_pkg.YES ) then -- add asm columns l_select_portion := l_select_portion || ', asm_allocated, asm_unallocated, asm_overhead '; end if; if ( p_volume_exists = storage_ui_util_pkg.YES ) then -- add volume columns l_select_portion := l_select_portion || ', vol_allocated, vol_unallocated, vol_overhead '; end if; if( p_local_disk_exists = storage_ui_util_pkg.YES ) then -- add local disk columns l_select_portion := l_select_portion || ',local_disks_allocated, local_disks_unallocated '; end if; if ( p_writeable_nfs_exists = storage_ui_util_pkg.YES ) then -- add writeable nfs columns l_select_portion := l_select_portion || ', (writeable_nfs_used+writeable_nfs_free) as writeable_nfs_allocated '; end if; -- set the from clause of the select for returning -- results. l_query := l_select_portion || ' from mgmt_v_grp_hostwise_storage '; open p_alloc_summary_cursor for l_query; end; ------------------------------------------------------ -- Name: get_job_execution_status -- -- Package: storage_ui_group_pkg -- -- Purpose: -- returns status of the job -- -- IN Parameters: -- p_job_name - the job name -- p_execution_id - the job execution id -- -- OUT Parameters: -- p_job_execution_status - status of job execution ----------------------------------------------------------- PROCEDURE get_job_execution_status ( p_job_name IN mgmt_job.job_name%TYPE, p_execution_id IN mgmt_job_exec_summary.execution_id%TYPE, p_job_execution_status IN OUT mgmt_job_exec_summary.status%TYPE ) IS BEGIN SELECT je.status INTO p_job_execution_status FROM mgmt_job_exec_summary je, mgmt_job j WHERE j.job_id = je.job_id AND j.job_name = upper(p_job_name) AND j.job_type = G_GROUP_REFRESH_JOB_TYPE AND je.execution_id = p_execution_id; END get_job_execution_status; ------------------------------------------------------ -- Name: get_host_targets -- -- Package: storage_ui_group_pkg -- -- Purpose: -- returns list of hosts with storage collections -- -- IN Parameters: -- p_group_name - the group name -- -- OUT Parameters: -- p_host_list - the list of hosts ---------------------------------------------------------- PROCEDURE get_host_targets ( p_aggregate_name IN mgmt_targets.target_name%TYPE, p_aggregate_type IN mgmt_targets.target_type%TYPE, p_host_list OUT MGMT_MEDIUM_STRING_TABLE ) IS l_host_list MGMT_MEDIUM_STRING_TABLE; BEGIN -- flat members tables -- associations -- pick source to target associations -- pick target to source assocations -- clean up the list to include only those targets which have -- a storage collection SELECT a.member_target_name BULK COLLECT INTO l_host_list FROM mgmt$target_flat_members a WHERE a.aggregate_target_name = p_aggregate_name AND a.aggregate_target_type = p_aggregate_type AND a.member_target_type = mgmt_global.G_HOST_TARGET_TYPE UNION SELECT b.assoc_target_name FROM mgmt$target_flat_members a, mgmt$target_associations b WHERE a.aggregate_target_name = p_aggregate_name AND a.aggregate_target_type = p_aggregate_type AND a.member_target_name = b.source_target_name AND a.member_target_type = b.source_target_type AND b.assoc_target_type = mgmt_global.G_HOST_TARGET_TYPE UNION SELECT b.source_target_name FROM mgmt$target_flat_members a, mgmt$target_associations b WHERE a.aggregate_target_name = p_aggregate_name AND a.aggregate_target_type = p_aggregate_type AND a.member_target_name = b.assoc_target_name AND a.member_target_type = b.assoc_target_type AND b.source_target_type = mgmt_global.G_HOST_TARGET_TYPE; -- without distinct you get 4 rows for each host -- for the data, keys, alias, issues metric SELECT DISTINCT VALUE(c) BULK COLLECT INTO p_host_list FROM mgmt_metric_collections a, mgmt$target b, mgmt_metrics m, TABLE ( CAST ( l_host_list AS MGMT_MEDIUM_STRING_TABLE ) ) c WHERE b.target_name = VALUE(c) AND b.target_type = mgmt_global.G_HOST_TARGET_TYPE AND b.target_guid = a.target_guid AND a.coll_name = storage_ui_common_pkg.G_HOST_STORAGE_COLL_NAME AND m.metric_guid = a.metric_guid AND m.target_type = b.target_type AND b.type_version = m.type_meta_ver AND ( m.category_prop_1 = ' ' or b.type_qualifier1 = m.category_prop_1 ) AND ( m.category_prop_2 = ' ' or b.type_qualifier2 = m.category_prop_2 ) AND ( m.category_prop_3 = ' ' or b.type_qualifier3 = m.category_prop_3 ) AND ( m.category_prop_4 = ' ' or b.type_qualifier4 = m.category_prop_4 ) AND ( m.category_prop_5 = ' ' or b.type_qualifier5 = m.category_prop_5 ); END get_host_targets; ------------------------------------------------------ -- Name: get_usage_summary_by_host -- -- should be made private later -- Package: storage_ui_group_pkg -- -- Purpose: -- gets the usage summary by host data to be rendered -- in storage group UI. -- -- IN Parameters: -- p_target_name - the target name of group -- p_target_type - the target type of group -- -- OUT Parameters: -- p_storage_unit - the storage unit in which UI will render -- p_db_exists - 'Y', if at least one host in group has db -- data, 'N' otherwise -- p_local_fs_exists - 'Y', if at least one host in group has -- local fs data, 'N' otherwise -- p_writeable_nfs_exists - 'Y', if at least one host in group has -- writeable NFS data, 'N' otherwise -- p_usage_summary_cursor - ref cursor to usage summary by host -- data ----------------------------------------------------------- procedure get_usage_summary_by_host ( p_target_name in varchar2, p_target_type in varchar2, p_storage_unit in out varchar2, p_db_exists in out varchar2, p_local_fs_exists in out varchar2, p_writeable_nfs_exists in out varchar2, p_usage_summary_cursor in out sys_refcursor ) is l_select_portion varchar(1000); l_usage_summary_cursor sys_refcursor; l_query varchar(4000); begin -- populate storage target information in the -- temporary tables from which all the queries -- pick up info such as target name etc.) for -- targets for which UI is being rendered. -- For a host, this would insert only host data. -- for a group, this would insert info of all -- hosts in the group. storage_ui_common_pkg.insert_targets_for_ui_queries( p_target_name, p_target_type ); STORAGE_UI_COMMON_PKG.DELETE_MISSING_SNAPSHOT_HOSTS; -- for the time being hard code the storage unit. p_storage_unit := storage_ui_util_pkg.GB; -- first set the flags that indicate if -- any of volumes, local disks, nfs or -- asm exist in at least one of the hosts -- belonging to the group. This is used -- to dynamically generate the correct list of -- columns for the query whose ref cursor -- gets returned to UI. select local_fs_exists, db_exists, writeable_nfs_exists into p_local_fs_exists, p_db_exists, p_writeable_nfs_exists from mgmt_v_storagelayerdata_exist; -- Depending on the storage layer existence flags, -- construct the select portion -- of the query whose cursor needs to be returned. The UI -- will then select appropriate columns based on the above -- flags which are also being returned by this procedure. -- NOTE: I did not put the logic to convert the storage data -- into p_storage_unit below since that makes the query -- string huge and cumbersome. Instead, I do this conversion -- in the middle tier based on the storage unit that this -- procedure returns. -- IMPORTANT: Do not change the order of the "if" statements. -- If you do, you need to change the order of all if statements -- used in the middle tier code to construct the UI table as well. l_select_portion := 'select target_name, total_allocated, ' || 'total_used '; if( p_db_exists = storage_ui_util_pkg.YES ) then -- add db columns l_select_portion := l_select_portion || ',(db_used+db_free) as db_allocated, db_used '; end if; if( p_local_fs_exists = storage_ui_util_pkg.YES ) then -- add local fs columns l_select_portion := l_select_portion || ',(local_fs_used+local_fs_free) as local_fs_allocated, local_fs_used '; end if; if ( p_writeable_nfs_exists = storage_ui_util_pkg.YES ) then -- add writeable nfs columns l_select_portion := l_select_portion || ', (writeable_nfs_used+writeable_nfs_free)' || ' as writeable_nfs_allocated, writeable_nfs_used '; end if; -- set the from clause of the select for returning -- results. l_query := l_select_portion || ' from mgmt_v_grp_hostwise_storage '; --dbms_output.put_line( 'select portion of query= ' || -- l_select_portion ); --dbms_output.put_line( 'query= ' ||l_query ); open p_usage_summary_cursor for l_query; end; ------------------------------------------------------ -- Name: set_group_history_flag -- -- Package: storage_ui_group_pkg -- -- Purpose: -- this procedure enables or disables group storage history. -- If invoked with a value of 'Y' for p_enable_disable_flag, -- it enables group storage history. -- If invoked with a value other than 'Y' for p_enable_disable_flag, -- it disables group storage history. Note that this procedure -- just sets a flag that determines the enabling of group -- storage history - it does not take any actions related to -- group history. For example, disabling group storage history -- will not result in any purging of existing history data. -- only the UI would stop showing history and the repository -- collection would disable itself in the next run. -- -- IN Parameters: -- p_target_name - the target name of group -- p_target_type - the target type of group -- p_enable_disable_flag - the flag that enables or disables -- group storage. If 'Y', enables, -- otherwise disables group storage -- -- OUT Parameters: ----------------------------------------------------------- procedure set_group_history_flag( p_target_name in varchar2, p_target_type in varchar2, p_enable_disable_flag in varchar2 ) is l_target_guid mgmt_targets.target_guid%type; begin l_target_guid := mgmt_target.get_target_guid( p_target_name, p_target_type ); begin insert into mgmt_target_properties( target_guid, property_name, property_value ) values ( l_target_guid, g_grp_storage_hist_enabled, p_enable_disable_flag ); exception when dup_val_on_index then dbms_output.put_line( 'property exists - updating value'); update mgmt_target_properties set property_value = p_enable_disable_flag where target_guid = l_target_guid and property_name = g_grp_storage_hist_enabled; end; if( p_enable_disable_flag = storage_ui_util_pkg.YES ) then -- start a collection for this group mgmt_collection.start_collection ( p_target_name => p_target_name, p_target_type => p_target_type, p_metric_name => storage_rep_metrics_pkg.G_GROUP_STORAGE_REP_METRIC, p_coll_schedule => mgmt_coll_schedule_obj.interval_schedule( storage_rep_metrics_pkg.G_STORAGE_COL_INTERVAL_IN_MINS, null, null) ); else mgmt_collection.stop_collection ( p_target_name => p_target_name, p_target_type => p_target_type, p_metric_name => storage_rep_metrics_pkg.G_GROUP_STORAGE_REP_METRIC ); end if; end; ------------------------------------------------------ -- Name: get_storage_hist_enabled -- -- Package: storage_ui_group_pkg -- -- Purpose: -- this procedure returns 'Y' if storage history for a group -- is enabled, 'N' otherwise. -- -- IN Parameters: -- p_group_name - the target name of group -- p_target_type - the target type of group -- -- Returns: -- 'Y' if storage history for a group is enabled, 'N' otherwise. ----------------------------------------------------------- function get_storage_hist_enabled ( p_group_name in varchar2, p_target_type in varchar2 default mgmt_global.G_COMPOSITE_TARGET_TYPE ) return varchar2 is l_storage_hist_enabled varchar2(1); begin select decode( count(*), 0, storage_ui_util_pkg.NO, storage_ui_util_pkg.YES ) into l_storage_hist_enabled from mgmt_target_properties tp, mgmt_targets t where t.target_guid = tp.target_guid and tp.property_name = G_GRP_STORAGE_HIST_ENABLED and property_type = mgmt_global.G_INSTANCE_PROP_TYPE and target_name = p_group_name and target_type = p_target_type and property_value = storage_ui_util_pkg.YES; return l_storage_hist_enabled; end; ------------------------------------------------------ -- Name: enable_hist_and_run_coll -- -- Package: storage_ui_group_pkg -- -- Purpose: -- this procedure enables group storage history. It then -- runs the repository collection that would calculate -- grup storage history for this group once. -- -- IN Parameters: -- p_target_name - the target name of group -- p_target_type - the target type of group -- -- OUT Parameters: ----------------------------------------------------------- procedure enable_hist_and_run_coll( p_target_name in varchar2, p_target_type in varchar2 ) is begin set_group_history_flag( p_target_name => p_target_name, p_target_type => p_target_type, p_enable_disable_flag => storage_ui_util_pkg.YES ); storage_ui_common_pkg.run_hist_collection ( p_target_name => p_target_name, p_target_type => p_target_type ); end; /*-------------------------------------------------------------*/ /*--------- definition of private procedures/functions --------*/ /*-------------------------------------------------------------*/ END; / SHOW ERRORS;