Automatic Data Archiving: Difference between revisions

Latest revision as of 12:09, 1 April 2026

Commander4j's database can comfortably hold many millions of records, but after several years of operation you may wish to remove old data to keep the database size manageable and query performance fast. The Archive module allows you to define automatic data deletion jobs that run on a schedule as part of the background interface service.

Template:Note

How Archiving Works

Each archive job defines a DELETE operation against a specific database table. The job specifies:

Which table to delete from
A WHERE clause that includes a date threshold (records older than this are deleted)
A retention period in days (the threshold date is calculated as today minus retention days)
A maximum number of records to delete per run (to limit the impact on database performance)

Jobs run automatically as part of the Commander4j interface background service, in the sequence order defined in the configuration.

Archive Job Fields

Field	Description
Archive ID	A unique name for this archive job
Description	A human-readable description of what the job deletes
Enabled	Whether the job runs automatically during background service execution
Background Task	When Yes, the job is included in the scheduled background run
SQL Table	The database table records are deleted from
Retention Days	Records older than this many days are eligible for deletion
Max Delete	Maximum number of records deleted in a single run. Prevents large deletions from locking the table.
Sequence	Execution order. Lower numbers run first.
Result	Status message from the most recent run
Run Start / Run End	Timestamps of the most recent execution

Managing Archive Jobs

From the Archive Admin screen, administrators can:

Add — create a new archive job
Edit — modify an existing job's definition, retention period, or limits
Delete — remove an archive job
Run — execute a selected job immediately, without waiting for the scheduled background run

SQL Criteria

The WHERE clause for each job uses a ? placeholder which Commander4j replaces at runtime with the calculated archive date (today minus retention days). For example:

created_date < ?

Or for jobs that should only remove closed records:

status = 'CLOSED' AND close_date < ?

The exact DELETE syntax varies slightly between database platforms (SQL Server, MySQL, Oracle) but Commander4j handles this automatically based on your configured database type.

Database Table

Archive job definitions are stored in the SYS_ARCHIVE table.

@@ Line 1: / Line 1: @@
-The database can easily accomodate many millions of records but you may want to remove old data after a number of years. To make this process easy and automatic there is an Archive function which you can use to remove data which is older than x days. The archiving process runs as part of the background interfacing service.
+Commander4j's database can comfortably hold many millions of records, but after several years of operation you may wish to remove old data to keep the database size manageable and query performance fast. The Archive module allows you to define automatic data deletion jobs that run on a schedule as part of the background interface service.
-[[file:Archive_Admin.png|700px]]
+{{Note|Care must be taken when removing data from the database. Commander4j's tables have complex relationships between them. Deleting records from one table may affect the behaviour of related features. Always back up your database before enabling new archive jobs, and test any new job with a small retention period on a non-production system first.}}
-Care should be exercised when removing data from databases because there are complex relationships between tables and removing data from one table may effect how the system operates.
+== How Archiving Works ==
+Each archive job defines a DELETE operation against a specific database table. The job specifies:
+* Which table to delete from
+* A WHERE clause that includes a date threshold (records older than this are deleted)
+* A retention period in days (the threshold date is calculated as today minus retention days)
+* A maximum number of records to delete per run (to limit the impact on database performance)
+Jobs run automatically as part of the Commander4j interface background service, in the sequence order defined in the configuration.
+== Archive Job Fields ==
+{| class="wikitable"
+|-
+! Field !! Description
+|-
+| '''Archive ID''' || A unique name for this archive job
+|-
+| '''Description''' || A human-readable description of what the job deletes
+|-
+| '''Enabled''' || Whether the job runs automatically during background service execution
+|-
+| '''Background Task''' || When Yes, the job is included in the scheduled background run
+|-
+| '''SQL Table''' || The database table records are deleted from
+|-
+| '''Retention Days''' || Records older than this many days are eligible for deletion
+|-
+| '''Max Delete''' || Maximum number of records deleted in a single run. Prevents large deletions from locking the table.
+|-
+| '''Sequence''' || Execution order. Lower numbers run first.
+|-
+| '''Result''' || Status message from the most recent run
+|-
+| '''Run Start / Run End''' || Timestamps of the most recent execution
+|}
+== Managing Archive Jobs ==
+From the Archive Admin screen, administrators can:
+* '''Add''' — create a new archive job
+* '''Edit''' — modify an existing job's definition, retention period, or limits
+* '''Delete''' — remove an archive job
+* '''Run''' — execute a selected job immediately, without waiting for the scheduled background run
+== SQL Criteria ==
+The WHERE clause for each job uses a '''?''' placeholder which Commander4j replaces at runtime with the calculated archive date (today minus retention days). For example:
+ created_date < ?
+Or for jobs that should only remove closed records:
+ status = 'CLOSED' AND close_date < ?
+The exact DELETE syntax varies slightly between database platforms (SQL Server, MySQL, Oracle) but Commander4j handles this automatically based on your configured database type.
+== Database Table ==
+Archive job definitions are stored in the <code>SYS_ARCHIVE</code> table.
+See also: [[System Keys]], [[Install Interface Services]]
+[[Category:Commander4j]]