Purging soft-deleted records

Users who delete records from the Information Store through Analyst's Notebook Premium have no way to recover them afterward, but by default they are not completely removed. As the database administrator, you can decide to leave these soft-deleted records in the database, or to purge them manually or automatically.

Before you begin

Purging soft-deleted records from the Information Store requires the same authorization as deleting records by rule. You must connect to the database as a user with the Authorization to delete by rule. The following instructions assume that you are connected to the database as a user with that role.

In addition, if you want to automate purging, you must be able to schedule jobs to run on the database management system:

If you are using IBM Db2, the Db2 administrative task scheduler must be enabled.
If you are using Microsoft SQL Server, the SQL Agent service must be started.
If you are using PostgreSQL, you must download a third-party tool such as pg_cron.

For more information, see the i2 Analyze software prerequisites.

About this task

When users delete records from the Information Store through Analyst's Notebook, the data remains in the Information Store (but is inaccessible to users) unless you do something to change that. The following procedures describe how to purge soft-deleted records manually, how to automate that process, and how to understand the effect of a purge operation.

Procedure

To perform a one-off, manual purge of all soft-deleted records from the Information Store:

Run IS_Public.Purge_Soft_Deleted_Records.
- For example, for Db2:
```
CALL IS_Public.Purge_Soft_Deleted_Records;
```
- For example, for SQL Server:
```
EXECUTE "IS_Public".Purge_Soft_Deleted_Records;
```
- For example, for PostgreSQL:
```
SELECT IS_Public.Purge_Soft_Deleted_Records()
```
You can use this approach regardless of whether you also configure automatic purging.
The call immediately creates jobs for purging soft-deleted records of every type.

With a Db2 or SQL Server database, you can set up the Information Store so that soft-deleted records are purged automatically, on a schedule:

Run the IS_Public.Set_Purge_Soft_Delete_Schedule stored procedure with the schedule that you want to use.
- For example, for Db2:
```
CALL IS_Public.Set_Purge_Soft_Delete_Schedule('0 0 * * *');
```
  For more information about the format of the schedule, see UNIX cron format.
- For example, for SQL Server:
```
EXECUTE "IS_Public".Set_Purge_Soft_Delete_Schedule
    @freq_type=4,@freq_interval=1,
    @freq_subday_type=1,@active_start_time = 00000;
```
  For more information about the arguments and values for the schedule, see sp_add_jobschedule.
  Note: You must not specify values for the @job_id, @job_name, @name, @enabled arguments. i2 Analyze provides the values for these arguments.
In this example, the Information Store is configured to create the jobs that purge any soft-deleted records every day, at midnight.
To turn off automatic purging, call the IS_Public.Remove_Purge_Soft_Delete_Schedule stored procedure.

With a PostgreSQL database, where there is no built-in scheduling agent, you need to set up the soft-deletion process for yourself:

Download a third-party scheduling agent for PostgreSQL, such as pg_cron.
Use your chosen tool to make the call to Purge_Soft_Deleted_Records() on a scheduled basis.
To end automatic soft deletion, delete the scheduled function call.

Regardless of whether a purge was started manually or automatically, the effect is always the same: a set of jobs is created to remove soft-deleted records from the Information Store. To inspect the status and outcome of those jobs, you can use a view that the Information Store provides:

In IBM Data Studio, SQL Server Management Studio, or pgAdmin, open the IS_Public.Deletion_By_Rule_Log view and look for blocks of jobs where the rule_name is PURGE_SOFT_DELETE.

For more information about the contents of the IS_Public.Deletion_By_Rule_Log view, see Deletion_By_Rule_Log view.