To keep your Yellowfin configuration database finely tuned there are a few tasks you can do as mentioned in this forum post. One of them is to keep the number of records in the Event and EventArchive tables under control, this is good practice because these tables can get quite big as they record many different types of Yellowfin events.
There are 2 jobs that are important to this upkeep and maintenance of the Event and EventArchive tables, one of them moves records from the Event table to the EventArchive table when they become older than a certain number of days, and the other one deletes records from the EventArchive table when they become older than a certain number of days.
The way to create these 2 jobs and configure the number of days is by running the following 2 queries, keeping in mind that the last value of each INSERT query represents the number of days.
1. Archive tasks from the EVENT table, the job runs every day, searches for events older than x number of days ( eg. 30 days).
INSERT INTO Configuration
VALUES (1, ‘SYSTEM’, ‘EVENTMAXDAYS’, 30)
2. Delete records from the EVENTARCHIVE table older than x number of days ( eg. 60 days).
INSERT INTO Configuration
VALUES (1, ‘SYSTEM’, ‘EVENTARCHIVEMAXDAYS’, 60)
* * * *
And if you are wondering how and when Yellowfin moves the records across, and whether you can have any control over it, the process is very simple:
When a row has reached its archive date then Yellowfin just inserts it into the EventArchive table and deletes the original in the Event table.
And as to how often it is done and what time of day, if you have a look at the TaskSchedule table in the Yellowfin config DB you will see a column called FrequencyTypeCode – that’s where you can control the frequency of the background system task, it has possible values of MINUTES, DAILY, WEEKLY, FORTNIGHTLY, MONTHLY, ANNUAL, MANUAL.
Then there is a column called ScheduledLocalRunTime, this is where you can configure what time of day the background task should run, it is measured in how many seconds from midnight, for example, 1 AM would be 3600. And as the column name implies, this is in your local time, NOT GMT time.
If you ever want to get a background system task to run immediately without having to wait a week or a month or whatever, you can do so by setting the column called LastRunGMTDateTime back to the previous run time, for example, if you have a task that runs every week, set the value of this column to the previous week. This is because Yellowfin polls this column every few minutes to check the last time it was run and whether it is due to run again. Don’t forget, as the column name implies, this is in GMT time, NOT your local time (unless you live in Greenwich!).