Purging

The purge process starts by locating all OBJ type records to be purged (see steps 2 to 7 in Navigation) and creating corresponding records in the PURGE_LIST table. The result of this can be committed.

Note: The code examples in this section are shown using DB2® syntax. They may not be complete and may need to be modified for a specific use.

The following is an example of step 2 in Navigation:

-- Locate completed master transmissions that have expired
-- The set of states may vary depending on the defined process.
INSERT INTO PURGE_LIST (OBJ_ID, ID, OBJ_TYPE)
   SELECT O.ID, T.OBJ_ID, O.TYPE AS OBJ_TYPE
   FROM OBJ_BASE O, TRANSMISSION_BASE T
   WHERE T.OBJ_ID = O.ID AND O.MASTER_FLAG = 'Y' AND DATE(O.CREATED) <= EXPIRE_DATE
         AND O.STATUS IN ('S_InPTComplete', 'S_InPTFailed');

-- Locate Transactions for those transmissions
INSERT INTO PURGE_LIST (OBJ_ID, ID, OBJ_TYPE)
   SELECT O.ID, T.OBJ_ID, O.TYPE AS OBJ_TYPE
   FROM OBJ_BASE O, TRANSACTION_BASE T
   WHERE T.OBJ_ID = O.ID
         AND T.TRANSMISSION_ID IN (SELECT ID FROM PURGELIST WHERE OBJ_TYPE='TRANSMISSION');

The presence of records in the PURGE_LIST table can be used to signify that the process is being re-run; in which case, step 2 in Navigation should be skipped. Note also, if this list is very large, intermediate commits may be required to avoid an overrun of log files. If so, a separate indicator may be required to handle the case of failures during step 2 in Navigation.

Once the complete list of objects is built in the PURGE_LIST table, this information can be used to identify the rows to be deleted from each of the tables. Rows should be deleted from tables following the order identified in Referential Integrity.

For example, using the full select form of the DELETE statement, the following statement may be repeatedly issued to delete all appropriate records from the TXN_PARTY_REL table.

DELETE FROM (SELECT TXN_ID FROM TXN_PARTY_REL
             WHERE TXN_ID IN (SELECT ID FROM PURGE_LIST
                              WHERE OBJ_TYPE = 'TRANSACTION')
             FETCH FIRST 10000 ROWS ONLY);
COMMIT;

A commit should be issued after each DELETE. This ensures that the units of work do not grow too large.

For the EVENT table, use the expiry date to exclude records referenced by the remaining OBJ records.

DELETE FROM (SELECT ID FROM EVENT
             WHERE DATE(CREATED)<=EXPIRE_DATE
                   AND ID NOT IN (SELECT EVENT_ID FROM OBJ_BASE
                                  WHERE EVENT_ID IS NOT NULL)
             FETCH FIRST 10000 ROWS ONLY);

Because the complete list of objects that must be deleted is identified up front, it is possible to re-run the process after a failure.

Once all records have been deleted the contents of the PURGE_LIST table should be deleted so the next run of the process will start with an empty list.