Rem drv:
Rem
Rem $Header: assoc_data_upgrade.sql 25-nov-2006.01:55:52 pmodi Exp $
Rem
Rem assoc_data_upgrade.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem assoc_data_upgrade.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem pmodi 11/22/06 - Backport pmodi_bug-5666121 Bug#5666552 -
Rem Cursor change for perf
Rem chyu 07/26/05 - modifying the migrate header position
Rem chyu 07/18/05 - updating the new rep manager header
Rem pmodi 07/13/05 - Move update assoc count code to v102020 for beta to
Rem production upgrade
Rem pmodi 05/14/05 - Bug-4364791: assoc_count has been moved to
Rem mgmt_target_assoc_status
Rem pmodi 04/21/05 - Avoid self association for hosted by and
Rem monitored by
Rem pmodi 04/13/05 - recreate flat assoc entry for all targets based
Rem on view propagation privilege defined for association
Rem neearora 03/10/05 - Added code to add new parameters for
Rem delete_target job
Rem pmodi 02/16/05 - code moved in assoc_post_creation
Rem ramlhot 02/09/05 - created
Rem
Prompt Creating System Defined Associations
--Insert hosted-by Associations
INSERT
INTO mgmt_target_assocs
(assoc_guid, source_target_guid, assoc_target_guid,is_editable,created_by)
SELECT '474766142F9111CA6F361F38A1CA1A11',
target.target_guid,
host.target_guid , 0, 0
FROM mgmt_targets target,
mgmt_targets host
WHERE host.target_name = target.host_name
AND host.target_type = 'host'
AND target.target_type != 'host';
--Insert Monitored-By Associations
INSERT
INTO mgmt_target_assocs
(assoc_guid, source_target_guid, assoc_target_guid,is_editable,created_by)
SELECT '9A7862F4B441AEFCB05EBE55A5398F90',
target.target_guid,
agent.target_guid ,
0, 0
FROM mgmt_targets target,
mgmt_targets agent
WHERE agent.emd_url = target.emd_url
AND agent.target_type = 'oracle_emd'
AND target.target_type != 'oracle_emd';
Prompt Resubmitting the Jobs with new parameters..
--delete target job has changed so we should resubmit the job with new parameters.
-- Select all the scheduled jobs for delete target
DECLARE
l_old_params MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_new_params MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_target_name VARCHAR2(256);
l_target_type VARCHAR2(256);
l_delete_members VARCHAR2(256);
l_additional_new_params MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_additional_new_params_cnt NUMBER := 1;
l_parent_target_name VARCHAR2(256);
l_parent_target_type VARCHAR2(256);
l_member_name_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_member_type_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_member_emd_url_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_association_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
existing_param BOOLEAN := false;
l_delete_hosts VARCHAR2(10) := 'false';
l_force_delete VARCHAR2(10) := 'false';
l_target_guid RAW(16);
CURSOR del_target_job_execs IS
SELECT DISTINCT job_exec.job_id , job_exec.execution_id
FROM mgmt_job job, mgmt_job_execution job_exec
WHERE job.job_name like 'DELTGT_JOB%'
AND job_exec.end_time IS NULL
AND job.job_id = job_exec.job_id;
CURSOR get_members(p_target_guid RAW) IS
SELECT member.target_name target_name, member.target_type target_type,
member.emd_url emd_url, MGMT_ASSOC.ASSOC_DEF_CONTAINS association
FROM MGMT_FLAT_TARGET_ASSOC assoc, mgmt_targets member
WHERE assoc.source_target_guid = p_target_guid
AND member.target_guid = assoc.assoc_target_guid
AND assoc.is_membership = 1
UNION
SELECT member.target_name target_name, member.target_type target_type,
member.emd_url emd_url, MGMT_ASSOC.ASSOC_DEF_CONTAINS association
FROM MGMT_FLAT_TARGET_ASSOC assoc, mgmt_targets_delete member
WHERE assoc.source_target_guid = p_target_guid
AND member.target_guid = assoc.assoc_target_guid
AND assoc.is_membership = 1 ;
BEGIN
FOR cur_rec IN del_target_job_execs LOOP
-- Get the target_name and target_type of the target to be deleted by the current job
SELECT scalar_value INTO l_target_name
FROM MGMT_JOB_PARAMETER
WHERE job_id=cur_rec.job_id
AND execution_id=MGMT_JOB_ENGINE.NO_EXECUTION
AND parameter_name = 'delete_target_name';
SELECT scalar_value INTO l_target_type
FROM MGMT_JOB_PARAMETER
WHERE job_id=cur_rec.job_id
AND execution_id=cur_rec.execution_id
AND parameter_name = 'delete_target_type';
SELECT scalar_value INTO l_delete_members
FROM MGMT_JOB_PARAMETER
WHERE job_id=cur_rec.job_id
AND execution_id=cur_rec.execution_id
AND parameter_name = 'delete_members';
-- Collect the old job parameters
SELECT MGMT_JOB_PARAM_RECORD(parameter_name, parameter_type,scalar_value, vector_value)
BULK COLLECT INTO l_old_params
FROM MGMT_JOB_PARAMETER
WHERE job_id=cur_rec.job_id
AND execution_id=cur_rec.execution_id;
-- Generate target_guid based on l_target_name and l_target_type
l_target_guid := MGMT_TARGET.generate_target_guid(l_target_name, l_target_type);
-- Get the new parameters
BEGIN
-- retrive the value of l_delete_host and l_force_delete
FOR i IN 1..l_old_params.COUNT
LOOP
IF(l_old_params(i).param_name = 'delete_hosts')
THEN
l_delete_hosts := l_old_params(i).scalar_value;
ELSIF(l_old_params(i).param_name = 'force_delete')
THEN
l_force_delete := l_old_params(i).scalar_value;
END IF;
END LOOP;
l_additional_new_params := MGMT_JOB_PARAM_LIST();
l_additional_new_params_cnt := 1;
l_new_params := MGMT_JOB_PARAM_LIST();
l_parent_target_name := NULL;
l_parent_target_type := NULL;
l_member_name_array := MGMT_JOB_VECTOR_PARAMS();
l_member_type_array := MGMT_JOB_VECTOR_PARAMS();
l_member_emd_url_array := MGMT_JOB_VECTOR_PARAMS();
l_association_array := MGMT_JOB_VECTOR_PARAMS();
l_new_params.extend(11);
l_new_params(1) := MGMT_JOB_PARAM_RECORD('targetName', 1, l_target_name, null);
l_new_params(2) := MGMT_JOB_PARAM_RECORD('targetType', 1, l_target_type, null);
BEGIN
SELECT DISTINCT ct.target_name, ct.target_type
INTO l_parent_target_name, l_parent_target_type
FROM MGMT_TARGETS t, MGMT_TARGETS ct, MGMT_TARGET_ASSOCS m, MGMT_TYPE_PROPERTIES p,
MGMT_TARGET_ASSOCS om, MGMT_TARGETS ot
WHERE t.target_name = l_target_name
AND t.target_type = l_target_type
AND t.target_guid = m.assoc_target_guid
AND m.source_target_guid = ct.target_guid
AND m.assoc_guid = mgmt_assoc.g_contains_guid
AND ct.target_type = p.target_type
AND p.property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP
AND t.emd_url = ct.emd_url
AND NOT EXISTS(
SELECT ot.target_name
FROM MGMT_TARGET_ASSOCS om, MGMT_TARGETS ot
WHERE ct.target_guid = om.SOURCE_TARGET_GUID
AND om.ASSOC_TARGET_GUID = ot.TARGET_GUID
AND om.assoc_guid = mgmt_assoc.g_contains_guid
AND ct.emd_url = ot.emd_url
AND ot.target_guid != t.target_guid);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
l_new_params(3) := MGMT_JOB_PARAM_RECORD('parentTargetName', 1, l_parent_target_name, null);
l_new_params(4) := MGMT_JOB_PARAM_RECORD('parentTargetType', 1, l_parent_target_Type, null);
IF(l_delete_members = 'true')
THEN
FOR crec IN get_members (l_target_guid)
LOOP
l_member_name_array.extend(1);
l_member_type_array.extend(1);
l_member_emd_url_array.extend(1);
l_association_array.extend(1);
l_member_name_array(l_member_name_array.count) := crec.target_name;
l_member_type_array(l_member_type_array.count) := crec.target_type;
l_member_emd_url_array(l_member_emd_url_array.count) := crec.emd_url;
l_association_array(l_association_array.count) := crec.association;
END LOOP;
END IF;
l_new_params(5) := MGMT_JOB_PARAM_RECORD('memberTargetNames', 0, null,l_member_name_array);
l_new_params(6) := MGMT_JOB_PARAM_RECORD('memberTargetTypes', 0, null,l_member_type_array);
l_new_params(7) := MGMT_JOB_PARAM_RECORD('memberEmdUrls', 0, null,l_member_emd_url_array);
l_new_params(8) := MGMT_JOB_PARAM_RECORD('associations', 0, null,l_association_array);
l_new_params(9) := MGMT_JOB_PARAM_RECORD('deleteMembers', 1, l_delete_members, null);
l_new_params(10) := MGMT_JOB_PARAM_RECORD('deleteHost', 1, l_delete_hosts, null);
l_new_params(11) := MGMT_JOB_PARAM_RECORD('forceDelete', 1, l_force_delete, null);
-- Filter out those parameter which are in the old param list also and
-- return only the additional parameters.
FOR i IN 1..l_new_params.COUNT
LOOP
existing_param := false;
FOR j IN 1..l_old_params.COUNT
LOOP
IF(l_old_params(j).param_name = l_new_params(i).param_name AND
l_old_params(j).param_type = l_new_params(i).param_type)
THEN
existing_param := true;
EXIT;
END IF;
END LOOP;
IF(NOT existing_param)
THEN
l_additional_new_params.extend(1);
l_additional_new_params(l_additional_new_params_cnt) := l_new_params(i);
l_additional_new_params_cnt := l_additional_new_params_cnt + 1;
END IF;
END LOOP;
l_new_params := l_additional_new_params;
END;
-- Insert the job parameters for NO_EXECUTION using MGMT_JOB.add_job_parameters()
MGMT_JOB_ENGINE.update_job_parameters(cur_rec.job_id, MGMT_JOB_ENGINE.NO_EXECUTION, l_new_params,
false, false, true, 1);
-- Insert the job parameters for execution using MGMT_JOB.add_job_parameters()
MGMT_JOB_ENGINE.update_job_parameters(cur_rec.job_id, cur_rec.execution_id, l_new_params,
false, false, true, 1);
--- Delete the entries from mgmt_target_assocs and mgmt_flat_target assoc
DELETE FROM mgmt_target_assocs
WHERE (source_target_guid = l_target_guid OR
assoc_target_guid = l_target_guid );
DELETE FROM mgmt_flat_target_assoc
WHERE (source_target_guid = l_target_guid OR
assoc_target_guid = l_target_guid );
END LOOP;
END;
/
commit;