Deleting source data after archiving

Edit online

Configure IBM® Optim Archive to automatically delete archived data from the source database after archiving to reduce storage costs, comply with data retention policies, and implement data lifecycle management strategies.

Important: This capability applies only to archive flows.

Deleting source data after archiving simplifies data lifecycle management by allowing you to permanently remove archived data from the source database with a single action. The deletion process validates data integrity before removal, runs in reverse dependency order to avoid foreign key violations, and tracks job status in real time. You can perform the following tasks:

Deleting source data from the Archives page
Pre-deletion validation
Deletion modes
Configuring deletion at the pipeline level
Deletion process behavior

Restriction: Data deletion works only if all participating tables have at least one primary key. If you attempt to delete source data from a table without a primary key, internal validation flags the issue and the job fails immediately. No deletion is attempted for any table in this case.

Deleting source data from the Archives page

You can trigger source data deletion directly from the Archives page without modifying a pipeline configuration file.

Navigate to Archives.
In the Archives table, locate the archive file whose source data you want to delete.
Click the overflow menu icon for that archive row and select Delete source data.
In the Delete source data modal, review the archive name and the warning that the operation is irreversible.
Select a deletion mode:
- Leave the checkbox unchecked to delete with validation (recommended).
- Select Delete source data without validation to skip pre-deletion checks.
Click Delete to confirm and start the deletion job.
Monitor the job status on the Jobs page.

A deletion job named flow_name-delete-src-unique_id is created automatically based on the original archive configuration.

Note: Delete source data is unavailable when a deletion job for that archive is already running or has completed successfully, preventing accidental duplicate deletions. When a subsequent flow run overwrites the archive file, the option becomes available again.

Tip: If a deletion job fails, triggering deletion again reuses the existing job without creating a duplicate.

When the job finishes, open the overflow menu for the job and select View reports to open the deletion report. The report includes the following sections:

Job summary: The driver ID, job ID, created-by user, job start time, job end time, and job duration in seconds.
Row statistics: Aggregate counts across all tables, including total tables, successful tables, failed tables, partial success tables, total source rows, total deleted rows, total not found rows, total failed rows, and overall success rate.
Table operations: A row for each processed table showing the schema, table name, rows attempted, rows deleted, rows not found, rows failed, success rate, duration in seconds, and status.
Error summaries: Errors are grouped by table, with each entry showing the error code, count, and error message. Expand a table entry to view the full error details.

To save a copy of the report, click Download report in the upper right of the report panel.

Pre-deletion validation

Before source data deletion begins, the following validations run for every table in the flow:

Row count validation: Confirms that the number of archived rows matches the corresponding rows in the source so that all archived records still exist in the source.
Hash validation: Computes and compares hashes for the matched source and archived rows to confirm that the data is identical and unchanged.

For each table, row count validation runs first, followed by hash validation. If any validation fails, the deletion job stops immediately and no source data is deleted. All validations across all tables must pass before deletion begins.

Important: Pre-deletion validation applies to the offline_delete mode. If you use offline_delete_without_validation, the job skips these checks and proceeds directly to deletion.

Deletion modes

You can configure deletion behavior by setting the deletion_mode field in the pipeline configuration. The following modes are available:

offline_delete: Performs deletion after validating row count and hash comparison of the rows in the source database with archived data. This mode ensures data integrity before deletion.
offline_delete_without_validation: Performs deletion directly without validation. Use this mode when validation is not required or when you verified data integrity through other means.
null or field not set: The job runs as a normal archive flow without deletion. This is the default behavior.

Configuring deletion at the pipeline level

To configure deletion of source data after archiving, set the deletion_mode field in the pipeline configuration. Use the same flow JSON that you used to create or overwrite the archive, and add the deletion mode setting.

Edit your flow configuration JSON file and locate the pipeline configuration section.

In the app_data section of the pipeline, add the deletion_mode parameter and set its value to offline_delete or offline_delete_without_validation.

For example:

{
  "app_data": {
    "optim_archive": {
      "version": "2.0.0"
    }
  },
  "doc_type": "pipeline",
  "id": "f2cbff59-0262-4a4e-9d8e-6d0acdb6031c",
  "pipelines": [
    {
      "id": "f4ef2f18-dbd6-43cf-952e-1f594f9081b5",
      "name": "Full Archive",
      "nodes": [
        ...
      ],
      "app_data": {
        "ui_data": {
          ...
        },
        "deletion_mode": "offline_delete"
      },
      "runtime_ref": "optim-runtime:1.0.0"
    }
  ],
  "primary_pipeline": "f4ef2f18-dbd6-43cf-952e-1f594f9081b5",
  "version": "3.0",
  "name": "Full Archive"
}

Save the configuration.

Deletion process behavior

When the archive flow runs with deletion mode enabled:

Processes tables in reverse dependency order to avoid foreign key violations.
Deletes only rows that are not referenced by other tables.
Uses database transactions with commit and rollback for batch operations.
Skips deletion when tables without primary keys are detected or cyclic dependencies exist.
Continues processing remaining tables even if deletion fails for individual tables.

Important: Rows that foreign key constraint violations prevent from deletion are silently skipped. The job status shows as successful even when some rows remain undeleted. To ensure complete deletion, include all related tables that participate in foreign key relationships in your flow's access definition.

Note: The deletion targets the source data from the most recent archive run only, not from previous runs.

Results

When the archive flow runs with deletion mode enabled, archived rows are deleted from the source database after each archive operation.

What to do next

Monitor the archive flow execution logs to verify that row deletion succeeds. Deletion job status updates in real time. If deletion fails for specific tables, review the error messages to identify and resolve issues such as missing primary keys or dependency conflicts.