Rem
Rem $Header: severity_fix_data_3901347.sql 24-jan-2005.18:08:12 rpinnama Exp $
Rem
Rem severity_fix_data_3901347.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_fix_data_3901347.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    rpinnama    01/24/05 - rpinnama_bug-3901347
Rem    rpinnama    01/21/05 - Created
Rem

PROMPT Fixing violation duration (bug 3901347)
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;


DECLARE
  type rawtab IS TABLE OF RAW(16) index by binary_integer ;
  type varchar256tab IS TABLE OF VARCHAR2(256) index by binary_integer ;
  type datetab IS TABLE OF DATE index by binary_integer ;
  type numtab is TABLE of NUMBER index by binary_integer;

  l_host_resp_stat_guid mgmt_metrics.metric_guid%TYPE;
  l_commit_frequency NUMBER := 50000 ;

  l_target_guids rawtab ;
  l_metric_guids rawtab ;
  l_collection_tss datetab ;
  l_severity_guids rawtab ;
  l_severity_codes numtab ;
  l_severity_durs numtab ;

  l_upd_cnt number := 0;
  l_upd_durs numtab;
  l_upd_vguids rawtab;

  l_calc_duration mgmt_severity.severity_duration%TYPE;

  l_new_tgt_start NUMBER := 0;
  l_cnt           NUMBER := 0;

  CURSOR avail_violations_cur IS
    SELECT target_guid, metric_guid, collection_timestamp,
           severity_code, severity_duration, severity_guid
      FROM mgmt_severity
     WHERE metric_guid IN (SELECT DISTINCT metric_guid
                             FROM mgmt_metrics
                            WHERE metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                              AND metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN)
       AND key_value = ' '
     ORDER BY target_guid, collection_timestamp, load_timestamp,
              DECODE(severity_code,
                      MGMT_GLOBAL.G_SEVERITY_CLEAR,          1,
                      MGMT_GLOBAL.G_SEVERITY_WARNING,        3,
                      MGMT_GLOBAL.G_SEVERITY_CRITICAL,       4,
                      MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 5,
                      MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END,   6,
                                                             9) DESC;
  l_prev_rec avail_violations_cur%rowtype ;

BEGIN

  BEGIN
    SELECT parameter_value
      INTO l_commit_frequency
      FROM mgmt_parameters
     WHERE parameter_name = 'viol_duration_upgrade_batchsize' ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_commit_frequency := 10000 ;
  END ;

  OPEN avail_violations_cur;

  FETCH avail_violations_cur BULK COLLECT INTO
    l_target_guids, l_metric_guids, l_collection_tss, l_severity_codes,
    l_severity_durs, l_severity_guids
    LIMIT l_commit_frequency;

  IF ( (l_target_guids IS NOT NULL) or (l_target_guids.COUNT > 0) ) THEN
    -- Dummy up a guid, so that it wont match the first target
    l_prev_rec.target_guid := HEXTORAW('0000000000000000000000000000');

  END IF;

  WHILE ( (l_target_guids IS NOT NULL) AND (l_target_guids.COUNT > 0) )
  LOOP

    FOR i IN l_target_guids.FIRST..l_target_guids.LAST
    LOOP

      l_cnt := l_cnt + 1;
      l_new_tgt_start := 0;

      IF (l_target_guids(i) != l_prev_rec.target_guid) THEN
        l_new_tgt_start := 1;
      END IF;

      IF (l_new_tgt_start = 0) THEN
  
        l_calc_duration := (l_collection_tss(i) - l_prev_rec.collection_timestamp) * 24;

        IF ( (l_calc_duration != l_prev_rec.severity_duration) ) THEN

          l_upd_cnt := l_upd_cnt + 1; 
          l_upd_durs(l_upd_cnt) := l_calc_duration;
          l_upd_vguids(l_upd_cnt) := l_prev_rec.severity_guid;

        END IF;

      ELSE

        l_calc_duration := 0;
    
      END IF;
    
      l_prev_rec.target_guid := l_target_guids(i);
      l_prev_rec.metric_guid := l_metric_guids(i);
      l_prev_rec.collection_timestamp := l_collection_tss(i);
      l_prev_rec.severity_code := l_severity_codes(i);
      l_prev_rec.severity_duration := NVL(l_severity_durs(i), -1);
      l_prev_rec.severity_guid := l_severity_guids(i);

    END LOOP; -- FOR loop

    IF (l_upd_cnt > 0) THEN

      /**
      DBMS_OUTPUT.PUT_LINE('Fixing ' || l_upd_cnt || ' violations ');

      FOR k IN 1..l_upd_cnt
      LOOP
        DBMS_OUTPUT.PUT_LINE(' ' || k ||
                         ' Viol GUID - ' || l_upd_vguids(k) ||
                         ' New Dur - ' || ROUND(l_upd_durs(k), 4) );
      END LOOP;
      **/

      FORALL j IN 1..l_upd_cnt
        UPDATE mgmt_violations
           SET violation_duration = l_upd_durs(j)
         WHERE violation_guid = l_upd_vguids(j);

      COMMIT;


    END IF;



    FETCH avail_violations_cur BULK COLLECT INTO
      l_target_guids, l_metric_guids, l_collection_tss, l_severity_codes,
      l_severity_durs, l_severity_guids
      LIMIT l_commit_frequency;

    l_cnt := 0;
    l_upd_cnt := 0;

  END LOOP; -- WHILE loop

  CLOSE avail_violations_cur;

END;
/

PROMPT Done fixing violation duration 
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;