Deleting data by rule

Use an SQL query to identify the data to be deleted, then create and store the deletion rule to identify the corresponding data records for deletion. You can define the rule to specify deletion on an automated schedule or a manual basis.

Before you begin

If you want to apply deletion by rule to a remote Information Store hosted on a Db2, you must catalog the remote database by using the local IBM Db2 client. For more information, see the first note in Configuring remote IBM Db2 database storage.

About this task

By default, deletion-by-rule privileges are granted to the Information Store database administration user. If necessary, the privileges can be granted to other users by giving them the Deletion_By_Rule role. For more information, see Authorization to delete by rule. The privileges give you access to all the deletion-by-rule views and procedures, as summarized in the following outline of the main tasks.

CAUTION: Grant the Deletion_By_Rule role only to users with sufficient knowledge and authority. Use caution when you complete deletion-by-rule tasks as they constitute a powerful mechanism for deletion of data that might be difficult to recover. Ensure that there is a reliable backup in place before you delete by rule.

Procedure

  1. Identify the data to be deleted by composing and running an SQL query to select the data from the deletion view. You can use a different view to see details of the deletion views for your database.

  2. Check the SQL results to confirm that the selected data is as you expected. You can use the condition in subsequent steps to identify the particular records in the Information Store that you want to be deleted.

  3. Create a deletion rule based on the SQL query you verified at step 2 by using the supplied stored procedure. By default when a rule is created, automated job creation is switched off. You can use another view to see a list of rules and their automation status.

  4. Create a deletion job manually by using the supplied stored procedure. The procedure creates a deletion job that contains the rule, which is queued to run.

  5. Check the status of the job, and that deletion occurred as you expected, by consulting the IS_Public.Deletion_By_Rule_Log view. For more information, see Verifying deletion by rule.

  6. Optional: Automate deletion by rule by using the supplied stored procedure to include the rule in automated job creation.