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.

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.

For more information, see the i2 Analyze software prerequisites.

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.

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

Run the IS_Public.Purge_Soft_Deleted_Records stored procedure.
- For example, for Db2:
```
CALL IS_Public.Purge_Soft_Deleted_Records;
```
- For example, for SQL Server:
```
EXECUTE "IS_Public".Purge_Soft_Deleted_Records;
```
You can use this procedure regardless of whether you also configure automatic purging.
It immediately creates jobs for purging soft-deleted records of every type.

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

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 or SQL Server Management Studio, 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.