Sample use cases

Stored data might be monitored for compliance with international standards, corporate regulations, or storage volume restrictions. Depending on the use case, different rules can be created to meet many different kinds of requirement.

Reasons for data deletion often fall into one or more of the following categories:

Data is out of date
Data is obsolete or irrelevant in the context of the purpose of the database
Data has data privacy or security issues
Data reaches a storage volume threshold

The SQL statements in the following examples contain the kinds of conditions that are commonly used as a basis for deletion.

Note: If you are using Db2, when you are creating SQL for time-based conditions you can use the support for date and time arithmetic. PostgreSQL provides similar Date/Time Functions and Operators.

Selecting by property value

Select records of all persons who are below a specific age. The rule matches records of persons who are younger than 18 years. This example is one that you might use to delete data that does not suit the specific purpose of the database.

For Db2:

SELECT  *
FROM    IS_Public.E_Person_DV 
WHERE   p_date_of_birth > CURRENT_DATE - 18 years

For SQL Server:

SELECT  *
FROM    "IS_Public".E_Person_DV
WHERE   p_date_of_birth > DATEADD(yyyy, -18, GETDATE())

For PostgreSQL:

SELECT  *
FROM    IS_Public.E_Person_DV
WHERE   p_date_of_birth > CURRENT_DATE - INTERVAL '18 YEARS'

Metadata: data source

Select all records that are sourced from the "DMV" data source.

SELECT  *
FROM    IS_Public.E_Person_DV 
WHERE   source_names LIKE '%DMV%'

Metadata: creation date

Use SQL to select any record with a creation date that is not in the current calendar year.

For Db2:

SELECT  *
FROM    IS_Public.E_Person_DV 
WHERE   YEAR(create_time) < YEAR(CURRENT_DATE)

For SQL Server:

SELECT  *
FROM    IS_Public.E_Person_DV 
WHERE   create_time < DATEFROMPARTS(DATEPART(yyyy, GETDATE()), 1, 1)

For PostgreSQL:

SELECT  *
FROM    IS_Public.E_Person_DV
WHERE   DATE_PART('YEAR', create_time) < DATE_PART('YEAR', CURRENT_DATE)

Metadata: old data

Select communication records older than three months.

For Db2:

SELECT  *
FROM    IS_Public.L_Communication_DV 
WHERE   create_time < CURRENT_DATE - 3 MONTHS

For SQL Server:

SELECT  *
FROM    IS_Public.L_Communication_DV 
WHERE   create_time < DATEADD(mm, -3, GETDATE())

For PostgreSQL:

SELECT  *
FROM    IS_Public.L_Communication_DV
WHERE   create_time < CURRENT_DATE - INTERVAL '3 MONTHS'

Without the specified link

This case is an example where you require deletion of entities that are not linked to other entities. You might want this type of deletion as a general database cleanup, or to target links of a specific type, for example those granting access to a building or an account. Select records of people who do not have any access links.

The rule matches records of people when the item_id is not contained in a from_item_id column in the link deletion view. When your rule joins to another deletion view, it is recommended that you identify records by using the item_id columns.

SELECT  *
FROM    IS_Public.E_Person_DV 
WHERE   item_id NOT IN (
            SELECT  from_item_id
            FROM    IS_Public.L_Access_To_DV
        )

With the specified links

Select records of any person who is associated with a person named 'Michael Wilson'.

When your rule joins to another deletion view, it is recommended that you identify records by using the item_id columns.

SELECT  *
FROM    IS_Public.E_Person_DV 
WHERE   item_id IN (
            SELECT  A.from_item_id
            FROM    IS_Public.L_Associate_DV AS A
                    INNER JOIN IS_Public.E_Person_DV AS P
                        ON P.item_id = A.to_item_id
            WHERE   P.p_first_given_name = 'Michael' AND
                    P.p_family_name = 'Wilson'
        )

Data source, property, and timeframe

This case is an example of the kind of deletion that might be used to clean up a database or reduce storage requirements. Select records from a specific source based on when the last update occurred. The rule matches records of people with the named data source and an update date that is older than three years from the current date.

For Db2:

SELECT  *
FROM    IS_Public.E_Person_DV
WHERE   source_names LIKE '%LawEnforcementDB%' AND
        source_last_updated < CURRENT_DATE - 3 YEARS

For SQL Server:

SELECT  *
FROM    IS_Public.E_Person_DV
WHERE   source_names LIKE '%LawEnforcementDB%' AND
        source_last_updated < DATEADD(yyyy, -3, GETDATE())

For PostgreSQL:

SELECT  *
FROM    IS_Public.E_Person_DV
WHERE   source_names LIKE '%LawEnforcementDB%' AND
        source_last_updated < CURRENT_DATE - INTERVAL '3 YEARS'