
 
 
 
 CREATE OR REPLACE FUNCTION stats_rollup1_proc()
 RETURNS VOID
 LANGUAGE plpgsql
 VOLATILE
 AS
 $BODY$
 DECLARE
   l_time_cur cursor (current_time_in timestamp without time zone,cursor_limit int) for
           select sample_time, partition_index
             from vpx_sample_time1
            where rollup_counter is null
              and sample_time < current_time_in - '1 hour'::interval
            order by 1 asc
            limit cursor_limit;
 
   l_time                      vpx_sample_time1.sample_time%type;
   l_time_rowid                vpx_sample_time2.time_id%type;
   l_cnt                       int;
   l_rollup_counter            vpx_sample_time2.rollup_counter%type;
   l_start_time_id             vpx_sample_time1.time_id%type;
   l_rollup_start_time         vpx_sample_time1.sample_time%type;
   l_rollup_flag               numeric(1):=1;
   l_next_interval             numeric(10,0);
   l_current_interval          numeric(10,0);
   l_stat_rollup_level         numeric(10,0);
   l_null_device_id            numeric(4);
   l_last_sample_time          vpx_sample_time1.sample_time%type;
   l_source_interval_seq_num   numeric(1) := 1;
 
   l_hs1_partition_index        vpx_sample_time1.partition_index%type;
   l_hs1_frompartitionidxclause varchar(255);
   l_hs2_frompartitionidxclause varchar(255);
   l_previous_time              vpx_sample_time1.sample_time%type;
   l_next_time                  vpx_sample_time1.sample_time%type;
   l_last_part2                 vpx_sample_time2.partition_index%type;
   l_hour_mod                   int;
   l_cursor_limit               int;
   v_errmsg                     vpx_proc_log.description%type;
   v_err_log                    CHAR(3);
 
 BEGIN
 
 l_rollup_start_time := timezone('UTC', clock_timestamp());
 
 
 select COALESCE(value,'OFF') into v_err_log
    from vpx_parameter
    where name = 'DBProc.Log.Debug.Info';
 
 
 
 
 
 SELECT CAST( 30/(INTERVAL_VAL/60) * 3/2  AS INTEGER) INTO l_cursor_limit
        FROM VPX_STAT_INTERVAL_DEF
        WHERE INTERVAL_SEQ_NUM = 1;
 
 
 
 
 select rollup_enabled_flg into l_rollup_flag
   from vpx_stat_interval_def
  where interval_seq_num = 2;
 
 select max(sample_time) into l_last_sample_time
   from vpx_sample_time1
  where rollup_counter is null;
 
 if (l_rollup_flag = 1) and (l_last_sample_time is not null) then
   select interval_val into l_current_interval
     from vpx_stat_interval_def
    where interval_seq_num = l_source_interval_seq_num;
 
   select stats_level, interval_val into l_stat_rollup_level, l_next_interval
     from vpx_stat_interval_def
    where interval_seq_num = l_source_interval_seq_num + 1;
 
   SELECT DEVICE_ID
   INTO l_null_device_id
   FROM VPX_DEVICE
   WHERE DEVICE_NAME = '';
 
   
    select count(*) into l_cnt
      from vpx_sample_time1
     where rollup_counter is not null and
       partition_index_hs2 is not null;
 
    
    
    IF l_cnt = 0 then
       
       UPDATE VPX_SAMPLE_TIME1
          SET PARTITION_INDEX_HS2 = 1
        WHERE PARTITION_INDEX =
                (SELECT PARTITION_INDEX
                   FROM VPX_SAMPLE_TIME1
                  WHERE SAMPLE_TIME =
                         (SELECT MIN(SAMPLE_TIME)
                            FROM VPX_SAMPLE_TIME1
                           WHERE ROLLUP_COUNTER IS NULL
                             AND SAMPLE_TIME < l_rollup_start_time - '1 hour'::interval));
          l_last_part2 := 1;
    END IF;
 
    l_cnt:=1;
    open l_time_cur(l_rollup_start_time,l_cursor_limit);
 
    loop
     fetch l_time_cur into l_time, l_hs1_partition_index;
     if not found then
       exit;
     end if;
 
     
     
     
     
 
     
     select partition_index_hs2 into l_last_part2
       from vpx_sample_time1
      where sample_time = (select max(sample_time)
                             from vpx_sample_time1
                            where partition_index_hs2 is not null);
 
     
     select min(sample_time) into l_previous_time
       from vpx_sample_time1
      where partition_index_hs2 = l_last_part2;
 
     select min(sample_time) into l_next_time
       from vpx_sample_time1
      where rollup_counter is null
        and sample_time < l_rollup_start_time - '60 min'::interval;
 
     
     l_previous_time := date_trunc('hour', l_previous_time);
     l_hour_mod := extract(hour from l_previous_time)::int;
     l_hour_mod := l_hour_mod % 2;
     l_previous_time := l_previous_time - (l_hour_mod || ' hour')::interval;
 
     if l_next_time - '120 min'::interval >= l_previous_time then
        l_last_part2 := l_last_part2 + 1;
     end if;
 
     
     
     
     if l_last_part2 > 85 then
        l_last_part2 := 1;
     end if;
      
      SELECT rollup_counter, time_id
        into l_rollup_counter, l_start_time_id
        FROM vpx_sample_time1
       where sample_time = l_time;
 
      l_time:= date_trunc('hour', l_time) + (case when extract(minute from l_time) < 30 then 0 else 30 end || ' minute')::interval;
 
      
      
      if l_time - '120 min'::interval >= l_next_time then
         l_last_part2 := l_last_part2 + 1;
         l_next_time := l_time;
         if l_last_part2 > 85 then
            l_last_part2 := 1;
         end if;
      end if;
 
      l_hs1_fromPartitionIdxClause := 'VPX_HIST_STAT1_' || TO_CHAR(l_hs1_partition_index, 'FM9999');
      l_hs2_fromPartitionIdxClause := 'VPX_HIST_STAT2_' || TO_CHAR(l_last_part2, 'FM9999');
 
     
     
     
     
     if  l_rollup_counter is null
         and (l_last_sample_time + (l_current_interval || ' sec')::interval) >= (l_time + (l_next_interval || ' sec')::interval)  then
 
         
         update VPX_SAMPLE_TIME1
            set rollup_counter = (l_start_time_id::text || l_cnt)::numeric(38,0),
                partition_index_hs2 = l_last_part2
          where sample_time < l_time + (l_next_interval || ' sec')::interval
            and sample_time >= l_time;
 
         select time_id
           into l_time_rowid
           from vpx_sample_time2
          where sample_time = l_time
            and sample_interval=l_next_interval;
 
 
         if not found then
           select NEXTVAL('VPX_SAMPLE_TIME2_SEQ')
             into l_time_rowid;
           insert into vpx_sample_time2
                  (time_id, sample_time, sample_interval, partition_index)
           values
                  (l_time_rowid, l_time, l_next_interval, l_last_part2);
         end if;
 
 
         
         
         
         
         
         
         
         begin
           execute '
           insert into '||l_hs2_fromPartitionIdxClause||'(counter_id, time_id, stat_val)
           SELECT st.counter_id, $1, avg(st.stat_val)
             FROM vpx_sample_time1 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
            WHERE sm.rollup_counter = ($2::text || $3)::numeric(38,0)
              AND st.time_id = sm.time_id
              AND st.counter_id = SC.COUNTER_ID
              AND SC.STAT_ID = SD.ID
              AND ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                   or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
              AND SD.ROLLUP_TYPE = 0
            group BY st.counter_id'
           using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
         exception
           when OTHERS then
                v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
               if v_err_log = 'ON' then
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                       VALUES ('STATS_ROLLUP1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
               end if;
         end;
 
         begin
           execute '
           insert into '||l_hs2_fromPartitionIdxClause||'
           (counter_id, time_id, stat_val)
           SELECT  st.counter_id, $1, max(st.stat_val)
           FROM    vpx_sample_time1 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
           WHERE   sm.rollup_counter = ($2::text || $3)::numeric(38,0)
             AND   st.time_id = sm.time_id
             AND   st.counter_id = SC.COUNTER_ID
             AND   SC.STAT_ID = SD.ID
             AND   ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                     or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
             AND   SD.ROLLUP_TYPE = 1
           group BY st.counter_id'
           using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
         exception
           when OTHERS then
                v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
               if v_err_log = 'ON' then
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                       VALUES ('STATS_ROLLUP1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
               end if;
         end;
 
         begin
           execute '
           insert into '||l_hs2_fromPartitionIdxClause||'
           (counter_id, time_id, stat_val)
           SELECT  st.counter_id, $1, MIN(st.stat_val)
           FROM    vpx_sample_time1 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
           WHERE   sm.rollup_counter = ($2::text || $3)::numeric(38,0)
             AND   st.time_id = sm.time_id
             AND   st.counter_id = SC.COUNTER_ID
             AND   SC.STAT_ID = SD.ID
             AND   ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                     or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
             AND   SD.ROLLUP_TYPE = 2
           group BY st.counter_id'
           using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
         exception
           when OTHERS then
               v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
               if v_err_log = 'ON' then
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                       VALUES ('STATS_ROLLUP1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
               end if;
         end;
 
         begin
           execute '
           insert into '||l_hs2_fromPartitionIdxClause||'
           (counter_id, time_id, stat_val)
           SELECT  COUNTER_ID, TIME_ID, STAT_VAL
           FROM    (
                   SELECT
                         st.counter_id AS COUNTER_ID
                         ,$1 AS TIME_ID
                         ,ST.STAT_VAL AS STAT_VAL
                         ,DENSE_RANK()
                         OVER (PARTITION BY ST.COUNTER_ID ORDER BY SM.SAMPLE_TIME DESC) AS DENSE_RANK
                   FROM  vpx_sample_time1 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
                   WHERE sm.rollup_counter = ($2::text || $3)::numeric(38,0)
                     AND st.time_id = sm.time_id
                     AND st.counter_id = SC.COUNTER_ID
                     AND SC.STAT_ID = SD.ID
                     AND ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                           or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
                     AND SD.ROLLUP_TYPE = 3) as hist_stat2
           WHERE DENSE_RANK = 1'
           using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
         exception
           when OTHERS then
               v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
               if v_err_log = 'ON' then
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                       VALUES ('STATS_ROLLUP1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
               end if;
 
         end;
 
         begin
           execute '
           insert into '||l_hs2_fromPartitionIdxClause||'
           (counter_id, time_id, stat_val)
           SELECT  st.counter_id, $1, SUM(st.stat_val)
           FROM    vpx_sample_time1 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
           WHERE   sm.rollup_counter = ($2::text || $3)::numeric(38,0)
             AND   st.time_id = sm.time_id
             AND   st.counter_id = SC.COUNTER_ID
             AND   SC.STAT_ID = SD.ID
             AND   ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                     or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
             AND   SD.ROLLUP_TYPE = 4
           group BY st.counter_id'
           using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
         exception
           when OTHERS then
               v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
               if v_err_log = 'ON' then
                  INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                       VALUES ('STATS_ROLLUP1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
               end if;
 
         end;
 
         l_cnt:=l_cnt+1;
 
       end if;
 
       end loop;
     close l_time_cur;
 
 
 ELSE
      NULL;
 END IF;
 
 EXCEPTION
   WHEN OTHERS THEN
     PERFORM 1
     FROM    pg_cursors
     WHERE   name = 'l_time_cur';
 
     IF FOUND THEN
       CLOSE l_time_cur; 
     END IF;
     v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
     if v_err_log = 'ON' then
         INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('STATS_ROLLUP1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
     end if;
 end;
 $BODY$