Purge process

Each inbound message that an FTM application receives typically results in several related database records that are spread over several tables. An inbound message usually has a master record in the TRANSMISSION_BASE table, one or more records in the TRANSACTION tables and possibly the BATCH_BASE table, with corresponding entries in the OBJ_BASE table. Further entries in these tables are created to correspond to the outbound messages and inbound responses to those messages. Entries in the OBJ_OBJ_REL table track the relationships between the records. Events that are related to the process can be logged in the EVENT table. Further records can be created in the ERROR and the OBJ_PARTY_REL table. In addition, as part of the process, records are recorded in the history tables (for example, H_OBJ_BASE, H_TRANSMISSION_BASE, and H_BATCH_BASE).

The FTM data model describes all the database tables, including the foreign key relationships. For more information about obtaining the documentation for the FTM data model, see How to obtain restricted IBM® Financial Transaction Manager (FTM) documentation.

It is reasonable to assume that in a production system, purgeable items are those items whose business lifecycle is complete and whose age passed a predetermined limit. All operational data in FTM is associated with a record in the OBJ_BASE table. Operational data that logically extends OBJ_BASE is referred to as an object. The OBJ_BASE table contains the following columns that are considered important to purge.
  • The CREATED column can be used to determine the raw age of the object.
  • The STATUS column can be used to identify objects whose business lifecycle is complete.
  • The STATUS_DATE column can be used to determine how long it is since the last change in STATUS value.
A combination of STATUS and either CREATED or STATUS_DATE is a good combination to identify expired objects.

FTM has the concept of master objects. These objects are typically defined as the records that are created in the database during mapping when the initial primary business message is received. Typically, these objects represent the fundamental business activity that is being handled and a customer-defined FSM usually manages them. To locate all objects that were created as the master objects were processed, you can start at the master transmission objects and then traverse the database relationships.

Conceptually, the simplest and most obvious approach to purge is to do the following steps:
  • Locate the master objects that expired (transmission, batch, transaction).
  • Locate all related objects (walking all relationships iteratively).
  • Delete records that are related to all objects. For example, OBJ_VALUE, ERRORS, and OBJ_OBJ_REL.
  • Delete all the related objects.
  • Delete the master objects.
Another approach is to consider the different types of records that are created during the business process. You can apply specific age criteria to each of these different types of business records. This approach has the following steps:
  • Locate the specific business type objects (transmission, batch, transaction).
  • Delete records that are related to all objects. For example, OBJ_VALUE, ERRORS, OBJ_OBJ_REL, and others.
  • Delete the specific business type objects.
To illustrate the differences between the approaches, the FTM sample application can be used as an example. The sample application accepts and creates the following types of records during the business process:
  1. Master records (payment originations)
  2. Liquidity request records
  3. Liquidity response records
  4. Gateway request records
  5. Gateway acknowledgment records
  6. Client acknowledgment records

Following the first approach, you begin with the expired master objects. Then, you locate all the request objects that are related by using OBJ_OBJ_REL. Next, locate all the response/acknowledgement objects that are related to the requests by using OBJ_OBJ_REL. Finally, delete all the records that are related to these records and then delete all the located objects.

Following the second approach, you might decide to purge just liquidity objects. Start by locating the expired liquidity request and response objects. Then, delete all the records that are related to these records. Finally, delete all the located objects.

The difference is that in approach 1 all objects that are related to the master record are removed at one time, based on the expiry criteria of the master records. In the second approach, specific sets of data can be purged based on specific expiry criteria. For example, you might decide that liquidity objects expire after 30 days, gateway records after 60 days, and the master and client records after 90 days. In the context of the sample application, you can define six levels of granularity if you want (1 per object type).

The two approaches that are outlined here might not be the only valid approaches.