Limit the size of data with time windows

You can limit the amount of data in a data mart for time series data by using time windows.

Create time windows

After you assign a calendar, you can create time windows by using the ifx_TSDW_createWindow() routine. A time window consists of one or more adjacent virtual partitions. You can create multiple time windows. Time windows cannot overlap each other.

Note: If you create time windows, the time series data remains partitioned in the time windows until the data mart is dropped. To load all time series data into the data mart without the time windows, you must drop the data mart and re-create without the time windows.

A time window is identified by a begin virtual partition and an end virtual partition. The begin virtual partition is the first virtual partition that is included in the time window, while the end virtual partition is the first virtual partition that is excluded from the time window. For example, if you want to compare the data of the first 3 months of the year for 2010, 2011, and 2012, you can create three time windows: January through March for each year, each of which contains three virtual partitions, by running the following statements:

ifx_TSDW_createWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v', 0, 3); 
ifx_TSDW_createWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v', 12, 15); 
ifx_TSDW_createWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v', 24, 27);

Or by using time stamps to identify the virtual partitions:

ifx_TSDW_createWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v',
	'2010-01'::datetime year to month, '2010-04'::datetime year to month);
ifx_TSDW_createWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v',
	'2011-01'::datetime year to month, '2011-04'::datetime year to month);
ifx_TSDW_createWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v',
	'2012-01'::datetime year to month, '2012-04'::datetime year to month);

This figure shows three time windows that each contain three virtual partitions:

Shows the three time windows, each of which contain three virtual partitions.

The initial window for a time series virtual table must be created before the data mart is initially loaded (the data mart must be in LoadPending state). Subsequent time windows can be created either before the data mart is initially loaded, or when the data mart is in Active state. When you create a time window in an Active data mart, the time series data for the new time window is loaded to the data mart immediately.

Remove time windows

To remove a time window and the time series data it contains from the accelerator, use the ifx_TSDW_dropWindow() routine. The time window to be dropped is identified by the virtual partition it starts with. For example, if you want to remove the time window for January to March 2010, run the following statement:

ifx_TSDW_dropWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v', 0);

Or by using a time stamp to identify the time window:

ifx_TSDW_dropWindow('demo_dwa', 'demo_mart', 'informix', 'ts_data_v',
	'2010-01'::datetime year to month);

This figure shows two time windows that each contain three virtual partitions.

Shows two time windows, each of which contain three virtual partitions.

You can remove time windows when the data mart is in LoadPending or Active state. When you remove a time window in an Active data mart, the time series data for the time window is removed from the data mart immediately.

Move time windows

After you create time windows and load the data into the data mart, you can move the time windows forward or backward by a specified number of virtual partitions. When you move a time window, any virtual partition that is no longer contained in the time window is dropped from the data mart. Any virtual partition that is included in the moved time window is loaded into the data mart. You move time windows by running the ifx_TSDW_moveWindows() routine. The virtual partition default value is 1.

For example, you can move the time windows so that you can query on the second 3 months of the years 2011 and 2012 by running the following statement:

ifx_TSDW_moveWindows('demo_dwa', 'demo_mart', 'informix', 'ts_data_v');

That is equivalent to:

ifx_TSDW_moveWindows('demo_dwa', 'demo_mart', 'informix', 'ts_data_v', 1);

This figure shows that two time windows are moved forward in time by one time interval:

Shows the three time windows that moved forward in time by three intervals.