Deletion Strategy

It is recommended that records be purged in the following order to avoid referential integrity issues. For more information, see Suggested Order of Tables for Purge.

Once a process or utility has identified a set of objects that can be purged it must execute a series of SQL to delete associated records.

Given a list of objects, records related to these OBJ_BASE records can be deleted using the following SQL statements:
  • DELETE FROM ERROR WHERE OBJ_ID IN (<id list>)
  • DELETE FROM COUNTER WHERE OBJ_ID IN (<id list>)
  • DELETE FROM OBJ_VALUE WHERE OBJ_ID IN (<id list>)
  • DELETE FROM OBJ_OBJ_REL WHERE OBJ1_ID IN (<id list>)
  • DELETE FROM OBJ_OBJ_REL WHERE OBJ2_ID IN (<id list>)
  • DELETE FROM H_OBJ_BASE WHERE ID IN (<id list>)
For objects whose TYPE is 'TXN_PAYMENT'
  • DELETE FROM TXN_PAYMENT_BASE WHERE OBJ_ID IN (<id list>)
  • DELETE FROM TRANSACTION_BASE WHERE OBJ_ID IN (<id list>)
For objects whose TYPE is 'TXN_SECURITIES'
  • DELETE FROM TXN_ SECURITIES _BASE WHERE OBJ_ID IN (<id list>)
  • DELETE FROM TRANSACTION_BASE WHERE OBJ_ID IN (<id list>)
For objects whose TYPE is 'TRANSACTION'
  • DELETE FROM TRANSACTION_BASE WHERE OBJ_ID IN (<id list>)
For objects whose TYPE is 'BATCH'
  • DELETE FROM BATCH_BASE WHERE OBJ_ID IN (<id list>)
For objects whose TYPE is 'TRANSMISSION'
  • DELETE FROM TRANSMISSION_BASE WHERE OBJ_ID IN (<id list>)
If 'history' tables are in use for the above tables additional delete statements will be required. Once the related records are removed the OBJ_BASE record can be deleted:
  • DELETE FROM OBJ_BASE WHERE ID IN (<id list>)
Note. Objects should be deleted in the following type order:
  1. TXN_PAYMENT
  2. TXN_SECURITIES
  3. TRANSACTION
  4. BATCH
  5. TRANSMISSION

This is because:

TXN_PAYMENT_BASE & TXN_SECURITIES_BASE table records reference TRANSACTION_BASE records. TRANSACTION_BASE records reference TRANSMISSION_BASE records and may reference BATCH_BASE records. BATCH_BASE records may reference TRANSMISSION_BASE records

It is important to note that large scale deletes drive significant workload on the database transaction logs. Transaction logs have a finite size limit (determined by database settings) and regular commits must be issued during delete cycles to keep within the limits.