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.
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.