Cleaning up database tables for Talend Activity Monitoring Console

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

Backup the stats table you want to clean up, statcatcher for example.

Information noteWarning: 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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here