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'