ifx_setupTrickleFeed() function

The ifx_setupTrickleFeed() function enables the continuous refreshing of data in the fact and dimension tables of the data mart as the data changes in the database.

Syntax

accelerator_name: The name of the accelerator that contains the data mart.
data_mart_name: The name of the data mart.
buffertime: An integer that represents the time interval between refreshes and whether dimension tables are refreshed. For best results, set to at least 10 seconds.; -1 through -8639999 = The absolute value is used as the number of seconds between fact table refreshes. Dimension tables are not refreshed.; 1 through 8639999 = The number of seconds between refreshes of the fact and dimension tables.

Usage

After you load a data mart, run the ifx_setupTrickleFeed() function to enable trickle feed. For a high-availability cluster, run the ifx_setupTrickleFeed() function on the primary server.

If you plan to use partial data refresh for data marts, including trickle feed, the value of the MAX_PDQPRIORITY configuration parameter must be set to 50 or greater during the initial data load and partial data refresh.

As data is inserted into the database, the data in the specified data mart is refreshed at the frequency specified by the buffertime parameter. Between refreshes, the new data that is inserted into the fact tables is stored in the $INFORMIXDIR/tmp/trickleFeed directory on the database server. A small value of the buffertime parameter, such as 60, has the following advantages:

The data in the data mart is current.
Loading the data has a minimal impact on accelerated queries.
A small amount of disk space is needed to store the updates on the database server between refreshes.

Choose the value of the buffertime parameter carefully to suit your business needs.

Set the buffertime parameter to a negative number when you want to refresh insertions into the fact tables but ignore changes to the dimension tables.

The ifx_setupTrickleFeed() function adds the ifx_TrickleFeed_load_ID task to the Scheduler, where ID is a unique number. Every data mart for which you enable trickle feed has a trickle feed task in the Scheduler. You can determine if trickle feed is enabled for a data mart by querying the ph_task table in the sysadmin database. The tk_name column contains the name of the task and the tk_description column contains the description, which lists the data mart and accelerator names. If a task description includes the relevant data mart name, trickle feed is running for that data mart.

Important: You must periodically disable trickle feed and reload the data mart. The ifx_TrickleFeed_load_ID task in the Scheduler stops running after 32000 refreshes.

Trickle feed captures the data that is inserted into the fact tables and all types of data changes to the dimension tables. Use other methods to make any other changes to the data or the data mart. The ifx_setupTrickleFeed() function does not make the following changes:

Update or delete data in the fact tables. Typically, changes to existing data is not allowed in warehouses. If data is updated, disable trickle feed and run the ifx_refreshMart() function.
Attach or detach a partition in the data mart when a fragment is attached to or detached from a fact or dimension table. You must disable trickle feed and run the ifx_refreshMart() function or reload the data mart.
Update the data mart definition. If a table is added or dropped from the database server, you must disable trickle feed, drop the data mart, update the data mart definition, and then re-create the data mart.

Do not do any of the following tasks while trickle feed is enabled:

Refresh a partition by running the ifx_loadPartMart() function.
Drop a partition by running the ifx_dropPartMart() function.
Load the data mart by running the ifx_loadMart() function.
Drop the data mart by running the ifx_dropMart() function. The ifx_dropMart() function disables trickle feed.

To disable trickle feed, run the ifx_removeTrickleFeed() function.

Return value

The ifx_setupTrickleFeed() function returns the text string "The operation was completed successfully." or an error message.

Example 1: Start trickle feed

The following statement starts refreshing data every 60 seconds for a data mart named Datamart1 in an accelerator named MyAccelerator:

EXECUTE FUNCTION ifx_setupTrickleFeed('MyAccelerator', 'Datamart1', 60);

Example 2: Determine if trickle feed is running

The following SQL statement returns every trickle feed task name and description:

SELECT * FROM sysadmin::ph_task WHERE tk_name MATCHES 'ifx_TrickleFeed_load*';

The results of this query show that trickle feed is running for the data mart named Datamart1 in an accelerator named MyAccelerator:

...
tk_id               84
tk_name             ifx_TrickleFeed_load_16
tk_description      trickle feed loader for data mart Datamart1@MyAccelerator
...