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'

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'