Purge Database

Purging is the process of freeing up space in the database or deleting obsolete data that is no longer required. The purge process can be based on the age of the data or the type of data.

Functionality

In case the replication is running on the main site, and the database is set up for the purge feature, then, on the export stage, Replicator generates purge requests for the satellites where the purge is enabled.

The main site is never purged, and, therefore, satellite sites do not export purge requests. The main site is the only site that can export purge requests and send them to other sites (satellites).

Replicator runs a separate replication phase called Purge database. This phase runs right after the import phase. When the purge database is in progress, you can see in Replicator Manager what tables are purged and how many rows are deleted from each table:

Technical Details

Purge requests are exported at the very end of the procedure that exports data to a site. Replicator walks through the export tasks for a site once again after tasks export. It handles every table task where the new DataPurgedAfterDays or PKPurgeQuery field of the repl_ExportTasks view is set to a value other than null.

Those two fields suggest two different ways to specify data to be purged from the database of a satellite site. DataPurgedAfterDays enables you to specify for a table the number of days during which irrelevant data are stored on a satellite site until the data are deleted. The field PKPurgeQuery enables you to specify your own purge rule in the form of a SQL query. A purge rule for a table is a SELECT query that returns a set of primary key values.

If DataPurgedAfterDays or PKPurgeQuery is not null, Replicator creates a SQL local temporary table where it stores primary key values retrieved based on DataPurgedAfterDays and PKPurgeQuery. Table rows with those primary key values are to be deleted on the satellite site.

Let’s consider the meaning of the DataPurgedAfterDays field in detail.

After a table row no longer belongs to the result of the restricting query (PKRestrictionQuery, the partial replication) for a site, the value of the field LastExportTime of the distrib-table for this row and site is not updated (until the row belongs to the restricting query again). From this moment, the table row becomes irrelevant to that site. After some time, the difference in days between the value of LastExportTime and the current date exceeds the number of days specified in DataPurgedAfterDays. And that row must be deleted from the satellite site, and the corresponding row must be deleted from the distrib-table.

Example. The replicator is exporting data for site 50, DataPurgedAfterDays = 180 for the PW001P01 table. The replicator will determine rows to be deleted with the following query:

SELECT PIN

FROM dbo.repl_Distrib_PW001P01

WHERE SiteNr = 50 AND LastExportTime < GETDATE() - 180

Replicator saves results of this query into the temporary table #_PW001P01.

If PKPurgeQuery is not null, it contains a purge rule. Example of a purge rule for table PW001P01:

SELECT DISTINCT P01.PIN

FROM dbo.PW001P01 AS P01

INNER JOIN dbo.PW001P03 AS P03

  ON P01.PIN = P03.PIN

  AND P03.VESSEL = #DSN# + 5

  AND P01.EMPLOYMENTENDDATE < GETDATE() - 364

Similar to the first query, Replicator saves the result of this query into #_PW001P01.

At the end of the current step, the Replicator checks the temporary table. If #_PW001P01 is not empty, Replicator saves its content to the PW001P01.da3 file. Such a file is a purge request. All formed purge requests (*.da3 files) are packed to a *.cab archive and sent to a certain satellite site where table rows with the corresponding values of the primary key will be deleted. Besides, Replicator cleans up the distrib-table:

DELETE FROM dbo.repl_Distrib_PW001P01

FROM dbo.repl_Distrib_PW001P01 AS dsb

INNER JOIN #pks_PW001P01 AS tmp

       ON dsb.PIN = tmp.PIN

WHERE dsb.SiteNr = Destination_Site_Number

Housekeeping

Replicator enables you to benefit from the purge database functionality. For this, you have to set up the database for the purge feature. First, run APM and navigate to Setup > Adonis Replicator. Now, proceed as described below:

Make sure you are on the Site Properties tab. Within the grid, in the Is Purge column, select the checkbox(es) for the site(s) for which you want to purge the database.
Switch to the Replication Tasks tab under Views -1 to define the number of days to apply purging and PK Purge Query:
- In the Data Purged After Days column, enter the number of days starting today for purging to be applied.
- In the PK Purge Query column, double-click the cell where a purge query is to be added. When the Browse button appears, click it to open the SQL Editor dialogue:
  
  In the SQL Editor dialogue, enter the SQL query or click Load to define the directory to the statement. Click Save to... to specify the directory on your computer or Save&Close to apply the changes in the editor.

As a result, the affected cell becomes yellow-highlighted.