Cleaning up database tables for Talend Activity Monitoring Console - 8.0

Talend Activity Monitoring Console User Guide

Version
8.0
Language
English
Product
Talend Big Data Platform
Talend Data Fabric
Talend Data Management Platform
Talend Data Services Platform
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Activity Monitoring Console
Content
Administration and Monitoring
After your system has been running for a while and log tables become huge, Talend recommends you to clean up obsolete and unnecessary data for Talend Activity Monitoring Console to keep it neat and tidy.

About this task

The following procedure shows an example of how to clean up the stats table in Oracle database. You can clean up data in other tables or other types of databases in a similar way.
Note: Talend recommends to clean up data by running a series of statements as shown in the following procedure instead of a single DELETE WHERE statement, because the TRUNCATE statement is faster and more efficient than the DELETE statement in SQL databases.

Procedure

  1. Backup the stats table you want to clean up, statcatcher for example.
    Warning: To prevent any kind of data loss and to recover your original data in case of any failure, Talend strongly recommends you to backup the table before cleaning up.
  2. Create a temporary table, tempstat for example.
  3. Select data you want to keep from the stats table and insert it into the temporary table by running the following SQL statement, where <temp-table> is the name of the temporary table, <stats-table> is the name of the stats table, and <condition> is used to filter data you want to keep.
    insert into <temp-table> select * from <stats-table> where <condition>

    For example, the following SQL statement selects data generated after 2022-01-01 00:00:00 from the statcatcher table and inserts it into the tempstat table.

    insert into tempstat select * from statcatcher where moment > to_date('2022-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
  4. Truncate the stats table by running the following SQL statement, where <stats-table> is the name of the stats table.
    truncate table <stats-table>
  5. Insert data from the temporary table into the stats table by running the following SQL statement, where <temp-table> is the name of the temporary table.
    insert into <stats-table> select * from <temp-table>
  6. Delete the temporary table if you do not need it anymore.