DELETESOURCE

You can use the DELETESOURCE directive to remove records from datasets based on a matching condition, ensuring that records marked for deletion in the source system are excluded from the data processing job.

The DELETESOURCE directive deletes records during dataset transformations, based on a source dataset that tracks deletions.

Hard Delete

The DELETESOURCE directive specifies the deletion of records from a dataset based on a matching condition. You apply the directive within the dataset definition to handle the removal of records that have been flagged for deletion in a source system. When DELETESOURCE is used, it directly impacts the dataset by ensuring that records marked for deletion are excluded from the dataset. This method requires specifying a subtrahend (deletion set) dataset, which contains the records to be removed. The matching condition is used to correlate records from the deletion set with those in the target dataset.

IMPORT SOURCE SALES_LOG FILTEREDBY (ACTION = 'D') AS SALES_DEL;
IMPORT SOURCE SALES;
DEFINE DATASET SALES_F
 ROWSOURCE SALES;
 THIS = SALES;
 DELETESOURCE SALES_DEL [SALES_ID] MATCHING [SALES_ID];
END;

In this example, the DELETESOURCE directive removes records from the SALES dataset based on matching SALES_ID values from the SALES_DEL dataset, which tracks deletions from the source system.

Soft Delete

The DELETETYPE[SOFT] flag marks records as deleted and the DELETESOURCE directive identifies the source dataset containing the records to be deleted.

IMPORT SOURCE SALESDEL
IMPORT SOURCE SALES
DEFINE UPDATEABLE DATASET SALES_F
 ROWSOURCE SALES;
 THIS = SALES;
 DELETETYPE[SOFT[SALESDELETED]];
 DELETESOURCE SALESDEL[SALES_ID] MATCHING [SALES_ID];
END

In this example, the DELETESOURCE directive deletes records from SALES that match the SALES_ID values in the SALESDEL dataset. The DELETETYPE[SOFT[SALESDELETED]] flag marks the deleted records, but they aren't physically removed from the dataset.