Identifying the data

How you compose a rule to delete data from the Information Store varies according to the structure and content of your database. The first step is to understand what you need to delete, and then create an SQL query that selects that data from the deletion views.

About this task

Use the deletion views as a basis to create and validate an SQL query that selects the data you want to delete. For more information, see Deletion views and columns.

You can complete this task in pgAdmin (for PostgreSQL), Microsoft SQL Server Management Studio, or IBM Data Studio.

Procedure

  1. Connect to your database as a user with the Deletion_By_Rule role.

  2. By using the data in a deletion view, identify the criteria that you can use to delete specific data.

  3. Create your SQL query. For example, based on the deletion view IS_Public.E_Event_DV, create a query that returns all event entities where the last update in the source data was more than three years ago:

    • For PostgreSQL:

      SELECT *
FROM IS_Public.E_Event_DV
WHERE source_last_updated < CURRENT_DATE - INTERVAL '3 YEARS'

    • For SQL Server:

      SELECT * 
FROM "IS_Public".E_Event_DV 
WHERE source_last_updated < DATE_ADD(yyyy, -3, GETDATE())

    • For Db2:

      SELECT * 
FROM IS_Public.E_Event_DV 
WHERE source_last_updated < CURRENT_DATE -3 YEARS

  4. Verify that the data that is returned from your SQL query is the data that you want to delete.

What to do next

Create a deletion rule based on your query. For more information, see Creating a deletion rule.