Example: Continuous refresh of accelerated time series data

Insert a row into the scheduler in the sysadmin database to add the continuous refresh stored procedure. The accelerator name and data mart name are provided:

create dba function
informix.ifx_continuous_refresh
    (task_id integer, task_seq integer, accel varchar(128), mart varchar(128))
    returning integer

    define task_interval like sysadmin:ph_task.tk_frequency;
    define mart_id       like sysadmin:iwa_datamarts.m_mart_id;
    define mart_dbname   like sysadmin:iwa_datamarts.m_dbname; 
    define table_owner   like sysadmin:iwa_marttables.mt_owner;
    define table_name    like sysadmin:iwa_marttables.mt_tabname;
    define table_id      like sysadmin:iwa_marttables.mt_tab_id;
    define cal_name      like sysadmin:iwa_tsvt_partcal.calname;
    define has_windows   like sysadmin:iwa_tsvt_partcal.parted;
    define cal_startdate datetime year to fraction(5);
    define upd_begin     integer;
    define upd_end       integer;
    define win_begin     integer;
    define win_end       integer;
    define index         integer;
    define upd_result    lvarchar;
    define sql_err       integer;

    on exception set sql_err
        return sql_err;
    end exception

Define the frequency of the refresh.

    select tk_frequency into task_interval
        from sysadmin:ph_task where tk_id = task_id;

Add the data mart definition from the sysadmin database. The data mart must already exist.

    -- Get the data mart information from the sysadmin database.
    select m_mart_id, m_dbname
        into mart_id, mart_dbname from sysadmin:iwa_datamarts
        where m_accel_name=accel and m_name=mart;

    -- The data mart must already exist.
    if mart_id is NULL then return -1; end if
    -- The data mart must be defined in the current database.
    if mart_dbname != DBINFO('dbname') THEN return -2; end if

    -- For each fact table that is a time series virtual table.
    foreach select mt_tab_id, mt_owner, mt_tabname
        into table_id, informix, table_name
        from sysadmin:iwa_marttables mt,
             informix.systables t, informix.sysams a
        where mt_mart_id = mart_id
          and mt.mt_isfact = 1
          and mt.mt_tabid = t.tabid
          and a.am_id = t.am_id
          and a.am_name = 'ts_vtam'

Add the time series virtual table from the sysadmin database. If there is no partitioning calendar, the virtual table is not included.

        -- Get the time series virtual table partitioning calendar.
        select calname, parted into cal_name, has_windows
            from sysadmin:iwa_tsvt_partcal
            where tab_id = table_id;

        -- If no partitioning calendar, skip the time series virtual table.
        if cal_name is null then continue foreach; end if

Define the calendar by querying the time series table with the CalStartDate function to obtain the existing start date, and then refresh the time series data with the ifx_TSDW_updatePartition() function to get the latest information.

        -- Get the calendar start date.
        execute function CalStartDate(cal_name) into cal_startdate;
        if cal_startdate is NULL then continue foreach; end if

        -- Get the calendar index of current time and update the end index.
        execute function CalIndex(
            cal_name, cal_startdate, current) into upd_end;
        if upd_end is NULL or upd_end < 0 then continue foreach; end if
        let upd_end = upd_end + 1;

        -- Get the calendar index of current time and update the begin index.
        execute function CalIndex(
            cal_name, cal_startdate, current-task_interval) into upd_begin;
        -- An index value less than zero is not allowed.
        if upd_begin < 0 then let upd_begin = 0; end if

        -- If the time windows are defined:
        if has_windows = 1 then
            -- Get all time windows that overlap the calendar start and end times.
            foreach select begin, end into win_begin, win_end
                from sysadmin:iwa_tsvt_windows
                where tab_id = table_id
                and end > upd_begin and begin < upd_end

                if win_begin < upd_begin then let win_begin = upd_begin; end if
                if win_end   > upd_end   then let win_end   = upd_end;   end if

                let index = win_begin;
                while index < win_end
                    execute function ifx_TSDW_updatePartition(
                        demo_dwa, datamart_name, informix, ts_data_v,
                         calendar_index)
                        into upd_result;
                    let index = index + 1;
                end while
            end foreach
        else
         -- If time windows are not defined.
         let index = upd_begin;
         while index < upd_end
             execute function ifx_TSDW_updatePartition(
                 demo_dwa, datamart_name, informix, ts_data_v, calendar_index)
                 into upd_result;
             let index = index + 1;
            end while
        end if
    end foreach

    return 0;

end function;

Use the GRANT statement to give access to the continuous refresh stored procedure in the sysadmin database scheduler.

grant execute on function
    informix.ifx_continuous_refresh(integer,integer,varchar,varchar)
	to public as informix;

Add an entry to the scheduler ph_task table in the sysadmin database to refresh the data every 15 minutes.

insert into 'sysadmin':ph_task
    (tk_name,
tk_description,
tk_type,
tk_dbs,    -- The database where the data mart is located.
tk_execute, -- The continuous refresh procedure to run.
tk_delete,
tk_start_time,  -- Whether to start immediately or later.
tk_stop_time,   -- The stop time or never stop (NULL).
tk_frequency,   -- Indicates the frequency, which in this case is 15 minutes.
tk_enable) 
values (
    'ifx_continuous_refresh_myAccel_myMart',
    'trickle feed for data mart myMart@myAccel',
    'TASK',
    'myDatabase',        
    'execute function ifx_continuous_refresh($DATA_TASK_ID,$DATA_SEQ_ID,
     "demo_dwa","demo_mart")',
    NULL,
    datetime(00:00:00) hour to second,
    NULL,
    interval(15) minute to minute,   -- The frequency in which the task is run.
    't');