Rem drv:
Rem
Rem $Header: default_homepage_pkgbody.sql 11-jul-2007.19:25:44 aptrived Exp $
Rem
Rem default_homepage_pkgbody.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem default_homepage_pkgbody.sql -
Rem
Rem DESCRIPTION
Rem plsql for default homepage
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem aptrived 04/26/07 - Bug#5951879, PERF, tuning query in
Rem get_alert_data()
Rem dankim 10/02/06 - Backport dankim_bug-5104074 from main
Rem rpatti 08/14/06 - XbranchMerge rpatti_bug-4769518 from main
Rem dankim 09/12/06 - bug 5104074,5409677
Rem rpatti 08/09/06 - add support for tgt types that are not host bound
Rem gsbhatia 07/18/05 - Add repmgr header
Rem vagarwal 07/13/05 - vagarwal_bug-4321324
Rem vagarwal 07/12/05 - fix bug in alerts, errors query: join with
Rem type_meta_ver and categ props in mgmt_metrics
Rem vagarwal 07/12/05 - continue
Rem vagarwal 07/07/05 - Created
Rem
CREATE OR REPLACE PACKAGE BODY em_default_home AS
PROCEDURE get_alert_data(
p_target_name_in IN VARCHAR2,
p_target_type_in IN VARCHAR2,
p_is_single_target_in IN NUMBER,
p_include_self_in IN NUMBER,
p_txn_name_in IN VARCHAR2,
p_metric_error_count_out OUT NUMBER,
p_alert_cur_out OUT CURSORTYPE)
IS
l_target_guid MGMT_TARGETS.target_guid%TYPE;
BEGIN
l_target_guid := mgmt_target.get_target_guid(p_target_name_in, p_target_type_in);
IF p_is_single_target_in=1 THEN
IF p_txn_name_in IS NOT NULL THEN
-- single target with a txn
OPEN p_alert_cur_out FOR
SELECT mm.column_label, cv.key_value, cv.violation_level,
cv.collection_timestamp AS severity_timestamp,
ROUND(cm.value, 2) AS value,
cm.string_value, cm.collection_timestamp,
mm.metric_name, mm.metric_column,
mm.num_keys, mm.keys_from_mult_colls,
ck.key_part1_value, ck.key_part2_value,
ck.key_part3_value, ck.key_part4_value,
ck.key_part5_value, mm.column_label_nlsid
FROM mgmt_current_violation cv,
mgmt_metrics mm,
mgmt_current_metrics cm,
mgmt_targets tg,
mgmt_metrics_composite_keys ck
WHERE ck.key_part1_value like p_txn_name_in
AND cv.target_guid = l_target_guid
AND tg.target_guid = cv.target_guid
AND cv.policy_guid = mm.metric_guid
AND cv.target_guid = cm.target_guid(+)
AND cv.policy_guid = cm.metric_guid(+)
AND cv.key_value = cm.key_value(+)
AND cv.target_guid = ck.target_guid(+)
AND cv.key_value = ck.composite_key(+)
AND cv.violation_type IN (0,2)
AND mm.target_type = tg.target_type
AND mm.type_meta_ver = tg.type_meta_ver
AND (mm.category_prop_1 = tg.category_prop_1
OR mm.category_prop_1 = ' ')
AND (mm.category_prop_2 = tg.category_prop_2
OR mm.category_prop_2 = ' ')
AND (mm.category_prop_3 = tg.category_prop_3
OR mm.category_prop_3 = ' ')
AND (mm.category_prop_4 = tg.category_prop_4
OR mm.category_prop_4 = ' ')
AND (mm.category_prop_5 = tg.category_prop_5
OR mm.category_prop_5 = ' ')
ORDER BY cv.violation_level desc, cv.collection_timestamp desc;
ELSE
-- single target without txn
OPEN p_alert_cur_out FOR
SELECT mm.column_label, cv.key_value, cv.violation_level,
cv.collection_timestamp AS severity_timestamp,
ROUND(cm.value, 2) AS value,
cm.string_value, cm.collection_timestamp,
mm.metric_name, mm.metric_column,
mm.num_keys, mm.keys_from_mult_colls,
ck.key_part1_value, ck.key_part2_value,
ck.key_part3_value, ck.key_part4_value,
ck.key_part5_value, mm.column_label_nlsid
FROM mgmt_current_violation cv,
mgmt_metrics mm, mgmt_current_metrics cm,
mgmt_targets tg, mgmt_metrics_composite_keys ck
WHERE cv.target_guid = l_target_guid
AND tg.target_guid = cv.target_guid
AND cv.policy_guid = mm.metric_guid
AND cv.target_guid = cm.target_guid(+)
AND cv.policy_guid = cm.metric_guid(+)
AND cv.key_value = cm.key_value(+)
AND cv.target_guid = ck.target_guid(+)
AND cv.key_value = ck.composite_key(+)
AND cv.violation_type IN (0,2)
AND mm.type_meta_ver = tg.type_meta_ver
AND mm.target_type = tg.target_type
AND (mm.category_prop_1 = tg.category_prop_1
OR mm.category_prop_1 = ' ')
AND (mm.category_prop_2 = tg.category_prop_2
OR mm.category_prop_2 = ' ')
AND (mm.category_prop_3 = tg.category_prop_3
OR mm.category_prop_3 = ' ')
AND (mm.category_prop_4 = tg.category_prop_4
OR mm.category_prop_4 = ' ')
AND (mm.category_prop_5 = tg.category_prop_5
OR mm.category_prop_5 = ' ')
ORDER BY cv.violation_level desc, cv.collection_timestamp desc;
END IF ;
SELECT count(err.metric_guid)
INTO p_metric_error_count_out
FROM mgmt_current_metric_errors err, mgmt_metrics met,
mgmt_targets tgt
WHERE tgt.target_guid = err.target_guid
AND tgt.target_guid = l_target_guid
AND err.metric_guid = met.metric_guid
AND err.metric_error_message IS NOT NULL
AND met.type_meta_ver = tgt.type_meta_ver
AND (met.category_prop_1 = tgt.category_prop_1
OR met.category_prop_1 = ' ')
AND (met.category_prop_2 = tgt.category_prop_2
OR met.category_prop_2 = ' ')
AND (met.category_prop_3 = tgt.category_prop_3
OR met.category_prop_3 = ' ')
AND (met.category_prop_4 = tgt.category_prop_4
OR met.category_prop_4 = ' ')
AND (met.category_prop_5 = tgt.category_prop_5
OR met.category_prop_5 = ' ');
ELSE
-- aggregate target
IF p_include_self_in=1 THEN
-- aggregate target with include self
OPEN p_alert_cur_out FOR
SELECT mm.column_label, cv.key_value, cv.violation_level,
cv.collection_timestamp AS severity_timestamp,
ROUND(cm.value, 2) AS value, cm.string_value, cm.collection_timestamp,
mm.metric_name, mm.metric_column,
mm.num_keys, mm.keys_from_mult_colls,
tg.target_name, tg.target_type, tg.type_display_name,
ck.key_part1_value, ck.key_part2_value,
ck.key_part3_value, ck.key_part4_value,
ck.key_part5_value, mm.column_label_nlsid
FROM mgmt_targets tg, mgmt_metrics mm, mgmt_current_violation cv,
mgmt_target_memberships tm, mgmt_current_metrics cm,
mgmt_metrics_composite_keys ck
WHERE tm.composite_target_name = p_target_name_in
AND tm.composite_target_type = p_target_type_in
AND tm.member_target_guid = tg.target_guid
AND tg.target_guid = cv.target_guid
AND cv.policy_guid = mm.metric_guid
AND cv.target_guid = cm.target_guid(+)
AND cv.policy_guid = cm.metric_guid(+)
AND cv.key_value = cm.key_value(+)
AND cv.target_guid = ck.target_guid(+)
AND cv.key_value = ck.composite_key(+)
AND cv.violation_type IN (0,2)
AND mm.target_type = tg.target_type
AND mm.type_meta_ver = tg.type_meta_ver
AND mm.category_prop_1 = tg.category_prop_1
AND mm.category_prop_2 = tg.category_prop_2
AND mm.category_prop_3 = tg.category_prop_3
AND mm.category_prop_4 = tg.category_prop_4
AND mm.category_prop_5 = tg.category_prop_5
ORDER BY cv.violation_level desc, cv.collection_timestamp desc;
ELSE
-- aggr target without include self
OPEN p_alert_cur_out FOR
SELECT mm.column_label, cv.key_value, cv.violation_level,
cv.collection_timestamp AS severity_timestamp,
ROUND(cm.value, 2) AS value, cm.string_value, cm.collection_timestamp,
mm.metric_name, mm.metric_column,
mm.num_keys, mm.keys_from_mult_colls,
tg.target_name, tg.target_type, tg.type_display_name, mm.column_label_nlsid,
ck.composite_key,ck.target_guid,ck.key_part1_value,
ck.key_part2_value, ck.key_part3_value,ck.key_part4_value,
ck.key_part5_value
FROM mgmt_targets tg, mgmt_metrics mm, mgmt_current_violation cv,
mgmt_flat_target_memberships tm, mgmt_current_metrics cm,
mgmt_metrics_composite_keys ck
WHERE tm.composite_target_name = p_target_name_in
AND tm.composite_target_type = p_target_type_in
AND (tm.member_target_guid = tg.target_guid
OR tm.composite_target_guid = tg.target_guid)
AND tg.target_guid = cv.target_guid
AND cv.policy_guid = mm.metric_guid
AND cv.target_guid = cm.target_guid(+)
AND cv.policy_guid = cm.metric_guid(+)
AND cv.key_value = cm.key_value(+)
AND cv.target_guid = ck.target_guid(+)
AND cv.key_value = ck.composite_key(+)
AND cv.violation_type IN (0,2)
AND mm.target_type = tg.target_type
AND mm.type_meta_ver = tg.type_meta_ver
AND mm.category_prop_1 = tg.category_prop_1
AND mm.category_prop_2 = tg.category_prop_2
AND mm.category_prop_3 = tg.category_prop_3
AND mm.category_prop_4 = tg.category_prop_4
AND mm.category_prop_5 = tg.category_prop_5
ORDER BY cv.violation_level desc, cv.collection_timestamp desc;
END IF ;
SELECT count(err.metric_guid)
INTO p_metric_error_count_out
FROM mgmt_current_metric_errors err
WHERE target_guid IN
(SELECT DISTINCT target_guid
FROM (
(SELECT member_target_guid AS target_guid
FROM mgmt_target_memberships
WHERE composite_target_guid=l_target_guid
UNION
SELECT target_guid
FROM mgmt_targets
WHERE target_name=p_target_name_in AND
target_type=p_target_type_in
)
)
)
AND metric_error_message IS NOT NULL;
END IF;
END get_alert_data;
PROCEDURE get_general_data(
p_target_name_in IN VARCHAR2,
p_target_type_in IN VARCHAR2,
p_pct_available_out OUT NUMBER,
p_avail_cur_out OUT CURSORTYPE )
IS
l_remotely_mon NUMBER(1) := 0;
BEGIN
IF p_target_type_in = MGMT_GLOBAL.G_HOST_TARGET_TYPE
THEN
raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
p_target_type_in || ' target type is not supported by this proc' );
END IF;
p_pct_available_out := mgmt_target.get_avail_pct_value(
p_target_name_in,
p_target_type_in,
1);
-- check if targets of this type are remotely monitored
BEGIN
SELECT DECODE(NVL(property_value, 0), 1, 1, 0)
INTO l_remotely_mon
FROM mgmt_type_properties
WHERE target_type = p_target_type_in
AND property_name = MGMT_GLOBAL.G_REMOTELY_MONITORED_PROP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_remotely_mon := 0;
END;
-- currently takes host_name=null to mean host_managed_by_em;
-- would like to query that independently from a type property
OPEN p_avail_cur_out FOR
SELECT v.current_status, t.target_name as display_name,
t.host_name as host_name, t.type_display_name,
l_remotely_mon as remotely_monitored,
DECODE(t.host_name, NULL, 0, 1) as host_managed_by_em
FROM mgmt_targets t,
mgmt_current_availability v,
mgmt_targets h
WHERE t.target_name = p_target_name_in
AND t.target_type = p_target_type_in
AND t.target_guid = v.target_guid (+)
AND t.host_name = h.target_name (+)
AND MGMT_GLOBAL.G_HOST_TARGET_TYPE = h.target_type (+);
END get_general_data;
end em_default_home;
/
show errors;