Manual database clean-up

When you are using an external high volume database, the out-of-the-box periodic clean-up threads can be disabled in order to have fine control over when clean-up is performed.

For example, running clean-up only between the hours of 1am – 4am when traffic on the services is low.

Warning: Incorrect clean-up can cause data-loss and functional issues. It is not recommended unless a database admin (DBA) is involved.
There are five features that supports clean-up threads:

The periodic clean-up threads run on an interval that is set in advanced configuration settings. Use these settings to disable periodic cleanup. See Advanced configuration properties.

When you are performing the clean-up, it is essential that database locks are efficient to stop contention. The recommended pattern is to open a read-only cursor on the statement. It identifies rows to be deleted and reuses the cursor to perform the deletion one row at a time. This minimizes the size and duration of the write lock.

In order to achieve this pattern two queries are provided for each table:
  1. A select pattern. This pattern returns a list of IDs that needs to be deleted.
  2. A delete query.
How and when these two patterns are run is environment dependent. The discretion and direction of a database admin is recommended. It is also important to manage the commit frequency as some DBMSs have limits. For example, DB2. See How many concurrently running statements allowed for a DB2 Java application and how to increase it?. The database admin might be required to supplement some values from the built-in functions.