Rem
Rem $Header: ping_upgrade_4397891.sql 03-aug-2005.10:49:08 pmodi Exp $
Rem
Rem ping_upgrade_4397891.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ping_upgrade_4397891.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pmodi       08/03/05 - pmodi_bug-4527450
Rem    pmodi       08/03/05 - Created - Moved from v102010 for beta to production
Rem                           upgrade. Bug:4527456
Rem    rpinnama    06/15/05 - rpinnama_bug-4397891
Rem    rpinnama    06/06/05 - Created
Rem

rem
rem PURPOSE
rem 
rem   The MGMT_EMD_PING_CHECK table holds the list of agents and when the repository
rem   checked their ping status.
rem   
rem COLUMNS
rem 
rem   target_guid        - target guid of the agent.
rem   
rem   last_checked_utc   - the date/time (in GMT) when the repository last checked the
rem                        ping status of the Agent.
rem
CREATE TABLE MGMT_EMD_PING_CHECK
(
    target_guid            RAW(16)        NOT NULL,
    last_checked_utc       DATE           DEFAULT SYSDATE,
    CONSTRAINT mgmt_emd_ping_check_pk
      PRIMARY KEY (target_guid)
)
ORGANIZATION INDEX
MONITORING;

-- Create a intermediate table
CREATE TABLE MGMT_EMD_PING_INT
(
    target_guid            RAW(16)        NOT NULL,
    status                 NUMBER         DEFAULT 1,
    last_heartbeat_ts      DATE           DEFAULT SYSDATE,
    last_heartbeat_utc     DATE           DEFAULT SYSDATE,
    clean_heartbeat_utc    DATE           NOT NULL,
    status_sync_utc        DATE           NOT NULL,
    emd_uptime_utc         DATE           NOT NULL,
    unrch_start_ts         DATE           DEFAULT NULL,
    max_inactive_time      NUMBER         DEFAULT 120,
    last_checked_utc       DATE           DEFAULT SYSDATE,
    down_reason_code       NUMBER         DEFAULT 0,
    down_reason_msg        VARCHAR2(1024) DEFAULT ' ',
    heartbeat_recorder_url VARCHAR2(256)  DEFAULT ' ',
    ping_job_name          VARCHAR2(64)   DEFAULT NULL,
    job_submit_time        DATE           DEFAULT NULL
)
MONITORING;

PROMPT Bug 4397891 : Migrating ping data to avoid chained rows
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;


DECLARE
  l_string_32   VARCHAR2(32);
  l_string_64   VARCHAR2(64);
  l_string_128  VARCHAR2(128);
  l_string_256  VARCHAR2(256);
  l_string_512  VARCHAR2(512);
  l_string_1024 VARCHAR2(1024);
BEGIN

  -- 32 blanks      12345678901234567890123456789012
  l_string_32   := '                                ';
  l_string_64   := l_string_32  || l_string_32; -- 64 chars
  l_string_128  := l_string_64  || l_string_64; -- 128 chars
  l_string_256  := l_string_128 || l_string_128; -- 256 chars
  l_string_512  := l_string_256 || l_string_256; -- 512 chars
  l_string_1024 := l_string_512 || l_string_512; -- 1024 chars

  INSERT INTO mgmt_emd_ping_int
    (target_guid, status, last_heartbeat_ts, last_heartbeat_utc,
     clean_heartbeat_utc, status_sync_utc, emd_uptime_utc, unrch_start_ts,
     max_inactive_time, last_checked_utc, down_reason_code, down_reason_msg,
     heartbeat_recorder_url, ping_job_name, job_submit_time)
   SELECT target_guid, status, last_heartbeat_ts, last_heartbeat_utc,
     clean_heartbeat_utc, status_sync_utc, emd_uptime_utc, unrch_start_ts,
     max_inactive_time, last_checked_utc, down_reason_code, down_reason_msg,
     heartbeat_recorder_url, ping_job_name, job_submit_time
     FROM mgmt_emd_ping;

  DELETE FROM mgmt_emd_ping;

  INSERT INTO mgmt_emd_ping
    (target_guid, status, last_heartbeat_ts, last_heartbeat_utc,
     clean_heartbeat_utc, status_sync_utc, emd_uptime_utc, unrch_start_ts,
     max_inactive_time, last_checked_utc, down_reason_code, down_reason_msg,
     heartbeat_recorder_url, ping_job_name, job_submit_time)
   SELECT target_guid, status, last_heartbeat_ts, last_heartbeat_utc,
     clean_heartbeat_utc, status_sync_utc, emd_uptime_utc, unrch_start_ts,
     max_inactive_time, last_checked_utc, down_reason_code, l_string_1024,
     l_string_256, l_string_64, job_submit_time
     FROM mgmt_emd_ping_int;

  FOR prec IN (SELECT target_guid, down_reason_msg, heartbeat_recorder_url, 
                      ping_job_name
                 FROM mgmt_emd_ping_int
                ORDER BY target_guid)
  LOOP
    UPDATE mgmt_emd_ping
       SET down_reason_msg = prec.down_reason_msg,
           heartbeat_recorder_url = prec.heartbeat_recorder_url,
           ping_job_name = prec.ping_job_name
     WHERE target_guid = prec.target_guid;
  END LOOP;

  INSERT INTO mgmt_emd_ping_check
    (target_guid, last_checked_utc)
   SELECT target_guid, last_checked_utc
     FROM mgmt_emd_ping_int;

END;
/

PROMPT Done  Migrating ping data to avoid chained rows
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Drop intermediate table
DROP TABLE MGMT_EMD_PING_INT;

ALTER TABLE MGMT_EMD_PING DROP COLUMN last_checked_utc;

-- Create the new index on mgmt_emd_ping
CREATE INDEX MGMT_EMD_PING_IDX_01
  ON MGMT_EMD_PING(status, max_inactive_time)
  COMPRESS 2;