Creating a deletion rule

Use a valid SQL query as a basis for a deletion rule. Store the corresponding deletion rule for use in the deletion process.

Before you begin

Verify the condition that you need for the deletion rule by creating an SQL query that returns the appropriate data. For more information, see Identifying the data.

Procedure

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

  2. Run the IS_Public.Create_Deletion_Rule stored procedure. You must specify the mandatory parameter values for the stored procedure.

    1. To give the rule a name by which it can be identified, enter a unique name in Rule_Name. Rule names are not case-sensitive.

    2. To identify the deletion view that the rule targets, enter the name in View_Name. Enter the view name without the IS.Public schema name prefix.

    3. Enter the WHERE clause conditions from your SQL query in Conditions. Enter the code that follows the WHERE keyword in the SQL query.

    For example, see the sample parameter values in the following table. Mandatory values are marked with an asterisk. | Name | Type | Value | |--------------|---------------|----------------------------------------------| | Rule_Name* | VARCHAR(50) | OLD EVENTS | | View_Name* | VARCHAR(128) | E_Event_DV | | Conditions* | VARCHAR(1000) | source_last_updated < CURRENT_DATE -3 YEARS |

    Running this procedure does not run the rule. However, the rule is validated to inform you of any errors. If the rule has no conditions, you receive a warning message to ensure that you are aware of the consequences when you do create a deletion job based on the rule

    CAUTION: When a job is created from a rule that has an empty value for the conditions parameter, it deletes every record in the view.

What to do next

To review the rule, browse the data in the IS_Public.Deletion_Rules view. For example, see the following rule details.

rule_name

view_name

conditions

automated

OLD EVENTS

E_Event_DV

source_last_updated < CURRENT_DATE -3 YEARS

N

By default, the rule is set up so that you can use it to create jobs manually. For more information, see Creating a deletion job. You can also configure the rule so that jobs are created automatically, according to a schedule. For more information, see Automating deletion by rule.

You can update a stored rule by a procedure similar to rule creation, or use a procedure to delete a rule. For more information, see Functions and stored procedures.