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.
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.
About this task
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.
Backup the stats table you want to clean up, statcatcher
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.
- Create a temporary table, tempstat for example.
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')
Truncate the stats table by running the following SQL statement, where
<stats-table>is the name of the stats table.
truncate table <stats-table>
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>
- Delete the temporary table if you do not need it anymore.