Log Cleaning

Internal Data Warehouse Cleanup

During the long running time and the huge amount of queries that datavirtuality Server handles, there will be a considerable amount of log entries and table data, which will not be useful anymore from a certain point in time. For example, if an optimization to a table created several stages of mat tables, not all of them are needed anymore and the old ones can be dropped. Also, it is not always necessary to be able to read all operations from the server that are older than a month. It is imaginable that a huge amount of log entries increases the time it will take to display them. Datavirtuality Server offers the ability to perform some cleanup operations in order to automatically remove obsolete data. Datavirtuality Server is shipped with a specific job implemented which can get rid of all outdated mat tables. Throughout the time, the server will remove several mat tables for the same optimization and this enables a fallback, if another replication of an optimization fails and it is better to have older data than none. This scenario applies to tables and views which take a long time if run every time with live data and wich are not frequently updated (for example only once per day). This automatic fallback possibility requires the server to keep old tables that belong to optimizations, even if there are already more current 'rollback' tables. The cleaner job performs a refresh on all information regarding optimizations and then gathers the knowledge about which mat tables can be safely dropped. There are several reasons to run the cleaner job:

• reduce the amount of data in the internal DWH
• keep only the most current data
• there are no internal entries in the configuration databse which are not used anymore
• improves lookup time of DWH tables
• improves index management since only useful indexes are being kept

You can simply run the celanup task via datavirtuality Studio from the Jobs tab or you can create a schedule attached to it (The job always has JobId 1):

Cleanup of Query Logs and Job Logs until LDWH 2.0

There are several Stored Procedures to clear the Query Log and the Job History Log. Only the data from the tables of the configuration database will be deleted but the entries still exist in the server.log file.

SYSADMIN.clearFinishedJobLogs

Clears job history for finished jobs only.

• before => Clear all log entries with "start"-timestamp older then this timestamp. If NULL is provided, all entries for finished jobs will be deleted from job history.

SYSADMIN.clearJobLogs

Clears job history.

• before => Clear all log entries with "finished"-timestamp older then this timestamp. If NULL is provided, all entries will be deleted from job history.

SYSADMIN.clearQueryLogs

Clears query log.

• before => Clear all log entries with "finished"-timestamp older then this timestamp. If NULL is provided, all entries will be deleted from query log.

Cleanup Procedures LDWH 2.0

There are several Stored Procedures to clear the Query Log and the Job History Log. Only the data from the tables of the configuration database will be deleted but the entries still exist in the server.log file.

SYSLOG.clearFinishedJobLogs

Clears job history for finished jobs only.

• before => Clear all log entries with "start"-timestamp older then this timestamp. If NULL is provided, all entries for finished jobs will be deleted from job history.

SYSLOG.clearJobLogs

Clears job history.

• before => Clear all log entries with "finished"-timestamp older then this timestamp. If NULL is provided, all entries will be deleted from job history.

SYSLOG.clearQueryLogs

Clears query log.

• before => Clear all log entries with "finished"-timestamp older then this timestamp. If NULL is provided, all entries will be deleted from query log.

SYSLOG.clearHistory

Clears history tables.

• "historyTableName" is the name of the history table in SYSLOG. If this parameter is null, all the history tables will be cleared
• "before" is a timestamp param. As for SYSLOG.clearQueryLogs(), all the entries older than the provided timestamp will be deleted. If this parameter is null, all entries will be deleted.