Navigation

To implement a purge utility, it is necessary to execute SQL against the database to do the following:
  1. Locate the transmission records that meet the expiration criteria.
  2. Locate all TRANSACTION records for which TRANSMISSION_ID matches the transmission.
  3. Locate all BATCH records for which TRANSMISSION_ID matches the transmission.
  4. Locate all TRANSACTION records for which BATCH_ID matches the batches that are returned.
  5. For the objects returned in the above steps, locate the related object records using the OBJ_OBJ_REL table and following the CAUSE and ACK_TO (and any other appropriate) relationships. This step might need to be performed iteratively until all relationship chains are followed.
  6. Locate the TRANSMISSION and BATCH records referenced by the transactions that are returned by step 5.
  7. Locate any OBJ_VALUE, COUNTER, and ERROR records for which the OBJ_ID references any of the previous objects.
Note: These steps do not refer to TXN_PAYMENT_BASE or TXN_SECURITIES_BASE records. It might not be necessary to reference these tables when identifying which transactions to delete. However, it is necessary to delete from these tables when purging.

During a purge run, the code or utility might need to maintain lists of IDs of objects located. How and where this list is maintained depends on the implementation. It is possible to use a database table, for example, PURGE_LIST with columns such as ID, OBJ_TYPE. Another possibility is an in memory list, for example, a Java™ container or derived class. Use of a database table drives additional database work and potentially limit the scalability of the process.

The following sections contain example select statements.

Locate transmission


SELECT ID, TYPE  FROM TRANSMISSION_V
  WHERE <business criteria>
    AND <expiry criteria>
    AND <row limit criteria>


<business criteria>
    MASTER_FLAG = Y
    SUBTYPE IN (...)


<expiry criteria>
    STATUS IN (...)
    CREATED <  CURRENT_TIMESTAMP - 60 DAYS
    STATUS_DATE < CURRENT_TIMESTAMP - 60 DAYS


<row limit criteria>
    In many cases it is not practical to generate an uncapped result set size.
    It is possible to use a clause like:
       FETCH FIRST 5000 ROWS 

    However, with these types of queries you need a way to avoid reselecting the same rows 
    on subsequent selects. An alternative is to use a Row Number restricted style query:
       SELECT ....., ROW_NUMBER() OVER()  AS RN 
             WHERE ...  
               AND RN > ? 
                   AND RN <= ?

Locate child transactions (non batch)


SELECT ID, TYPE  
  FROM TRANSACTION_V 
  WHERE TRANSMISSION_ID IN (list of transmission IDs) 
    AND BATCH_ID IS NULL

Locate child batches


SELECT ID, TYPE  
  FROM BATCH_V 
  WHERE TRANSMISSION_ID IN (list of transmission IDs) 
    AND PARENT_BATCH_ID IS NULL

Locate child transactions (batch)


SELECT ID, TYPE  FROM TRANSACTION_V 
  WHERE BATCH_ID IN (list of batch IDs)

Locate related objects


SELECT OBJ2_ID, O.TYPE 
  FROM OBJ_OBJ_REL, OBJ_BASE O 
  WHERE OBJ1_ID IN (list of object IDs) 
    AND OBJ2.ID = O.ID

SELECT OBJ1_ID, O.TYPE 
  FROM OBJ_OBJ_REL, OBJ_BASE O 
  WHERE OBJ2_ID IN (list of object IDs) 
    AND OBJ1.ID = O.ID