Rem DO NOT PUT HEADER IN THIS FILE, SINCE THE chronos_data_upgrade_cond.sql
Rem IS REFERENFERCING TO THIS DEPENDING ON CONDITION.
Rem
Rem $Header: chronos_data_upgrade.sql 18-jul-2005.11:00:21 chyu Exp $
Rem
Rem chronos_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_data_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Upgrades the chronos region tables from 10.1.0.3 to 10.1.0.4
Rem
Rem    NOTES
Rem      This script fills in region data in the new form from the old system.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    chyu        07/18/05 - adding the upgrade header 
Rem    eporter     03/07/05 - Drop mgmt_rt_region_members table 
Rem    eporter     02/16/05 - eporter_bug-4167002
Rem    adosani     12/09/04 - add chronos purge policy
Rem    eporter     11/23/04 - eporter_backport_10.1.0.3.0_3849061
Rem    eporter     11/18/04 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


UPDATE MGMT_RT_METRICS_RAW SET visitor_ip_num = emd_mntr_user.ipStr2Num(visitor_ip);
COMMIT;

UPDATE MGMT_RT_DOMAIN_1HOUR SET visitor_subnet_num = emd_mntr_user.ipSubnet2Num(visitor_subnet);
COMMIT;
UPDATE MGMT_RT_DOMAIN_1DAY SET visitor_subnet_num = emd_mntr_user.ipSubnet2Num(visitor_subnet);
COMMIT;
UPDATE MGMT_RT_DOMAIN_DIST_1HOUR SET visitor_subnet_num = emd_mntr_user.ipSubnet2Num(visitor_subnet);
COMMIT;
UPDATE MGMT_RT_DOMAIN_DIST_1DAY SET visitor_subnet_num = emd_mntr_user.ipSubnet2Num(visitor_subnet);
COMMIT;



DECLARE
  raw_row mgmt_rt_region_members%ROWTYPE;

  CURSOR update_cursor IS
    SELECT *  FROM mgmt_rt_region_members;

  ret INTEGER;
  dot1  number;
  dot2  number;
  min_ip number;
  max_ip number;

BEGIN

  OPEN update_cursor();
  LOOP
    FETCH update_cursor INTO raw_row;
    EXIT WHEN update_cursor%NOTFOUND;
      --dbms_output.put_line(raw_row.region_member);
    IF(raw_row.member_type = 'D') THEN
      ret := EMD_MNTR_USER.addRegionEntry(raw_row.region_guid, raw_row.region_member, -1, 0);
    ELSE -- it is a subnet
      dot1 := instr(raw_row.region_member, '.');
      dot2 := instr(raw_row.region_member, '.',  dot1+1);

      IF(dot1 = 0) THEN -- it's a class A subnet 
        min_ip := to_number(raw_row.region_member) * 16777216;
        max_ip := min_ip + 16777215;
      ELSIF(dot2 = 0) THEN -- it's a class B subnet
        min_ip := to_number(substr(raw_row.region_member, 1, dot1-1)) * 16777216 + 
                  to_number(substr(raw_row.region_member, dot1+1)) * 65536;
        max_ip := min_ip + 65535;
      ELSE 
        min_ip := to_number(substr(raw_row.region_member, 1, dot1-1)) * 16777216 + 
                  to_number(substr(raw_row.region_member, dot1+1, dot2-dot1-1)) * 65536 + 
                  to_number(substr(raw_row.region_member, dot2+1)) * 256;
        max_ip := min_ip + 255;
        END IF;
        ret := EMD_MNTR_USER.addRegionEntry(raw_row.region_guid, raw_row.region_member, min_ip, max_ip);
        --IF(ret != 0) THEN
        --  dbms_output.put_line('Error adding region ' || raw_row.region_member || ' error code ' || ret);
        --END IF;
    END IF;

  END LOOP; 
  CLOSE update_cursor;

EXCEPTION 
  WHEN OTHERS THEN
    IF update_cursor%ISOPEN THEN
      CLOSE update_cursor;
    END IF;
    --dbms_output.put_line(sqlerrm);
END;
/

-- I have to drop the table here because it is needed in the step above.
DROP TABLE &EM_REPOS_USER..MGMT_RT_REGION_MEMBERS;

-- Chronos Purge policies
BEGIN
-- Create Run Metric Dates in String metric history Purge Policy group
EM_PURGE.add_purge_policy_group(EMD_CHRONOS_ADMIN.G_RETENTION_GROUP_RUNMETRIC,
				  'Chronos Run Metric Dates', 31*24);
COMMIT;
END;
/

BEGIN
-- Register Run Metric Dates purge policy
EM_PURGE.add_purge_policy('CHRONOS_RUN_METRIC',
			    EM_PURGE.G_POLICY_TYPE_SYSTEM,
			    'EMD_CHRONOS_ADMIN.PURGE_RUN_METRIC_DATES',
			    0,
			    NULL,
			    'Purge policy for Chronos Run Metric Dates in String metric history.',
			    EMD_CHRONOS_ADMIN.G_RETENTION_GROUP_RUNMETRIC
			    );
COMMIT;
END;
/