Purge utility description

Like all database operations, the efficiency of the purge process depends on good database statistics. Ensure that RUNSTATS was run on all database tables. For more information, see Postinstallation tuning. This tuning might take some time to complete. It is not necessary to rerun the RUNSTATS job before each purge.

Original algorithm

Assuming the PURGE_LIST table is empty during RUNSTATS, you might need to import statistics for this table by using previously exported statistics or the provided script. The import might be needed because the population phase of the purge process causes the PURGE_LIST table to quickly grow in size from empty. This quick growth makes it increasingly difficult to search efficiently when no statistics exist. Importing predefined statistics helps to alleviate this issue.

To import the statistics for Db2®, browse to the following directory install_directory\ftm\v3213\admin\scripts\db2\maintenance. Run the import script by using a Db2 console with administrative privileges. Use one of the following options:

import_initial_purge_stats.cmd [Database Name] [Database Schema]

import_initial_purge_stats.sh [Database Name] [Database Schema] [Database User] [Database Password]

To import the statistics for Oracle, browse to the following directory install_directory\ftm\v3213\admin\scripts\oracle\maintenance\purge_scripts. Run the import script by using one of the following options:

import_initial_purge_stats.cmd [Database Name] [Database Schema]

import_initial_purge_stats.sh [Database Name] [Database Schema]

The purge process can be started after the statistics are imported, if required, and the properties in the properties file are set correctly.

If the logging.level property is set to INFO, the progress of the purge operation is visible on the console. During the population phase, the status of object discovery as the process searches for objects that are related to the master physical transmission is displayed. This status can be useful while the process is discovering objects related through the OBJ_OBJ_REL table because it is often the most time-consuming part of the population phase. During the deletion phase, rudimentary progress bars display the progress of the deletion process as various object types are deleted. Finally, a summary of the number of objects that were purged, and the time that is taken during both the population phase and purge phase, are displayed.

New alternative algorithm

This algorithm does not use the PURGE_LIST table, so no special RUNSTATS job or statistics import are needed.

Process recovery

Process recovery depends on the algorithm that is being used.

Original algorithm: During the population phase, the purge process maintains a record within the purge list that allows the process to resume if an interruption occurs. If the process ends prematurely as objects are being discovered and inserted for a REL_LVL, rerunning the purge utility allows the process to resume in most cases. The purge utility resumes from the point of failure, discovering and inserting objects for the same REL_LVL.; Resuming the process during the purge phase is simpler because the purge list was already generated. The resume just iterates through the purge list and deletes the identified objects. To this end, the process can resume easily by acting on the first, or the next, object in the list.
New alternative algorithm: This process retains no persistent state, but is inherently restartable. If the process fails or is stopped, it can be restarted with no additional considerations. Further investigation is necessary only when the process continues to encounter a condition that causes it to end abnormally.

Example purge topologies

The standard sample application can be used to explain the possibilities with the new purge algorithm. The sample application creates six distinct sets of data that can be purged independently, following different retention criteria. The following table shows these groups with a set of fictional retention periods.

Table 1. Retention periods
Type	Transmission	Batch	Transaction	Retention period (in days)
Payment Origination	Y	Y	Y	180
Liquidity Request	Y	-	Y	30
Liquidity Response	Y	-	Y	60
Gateway Request	Y	-	Y	90
Gateway Response	Y	-	Y	120
Client Ack	Y	-	Y	150

To facilitate this requirement, it is necessary to create six distinct purge profiles. Each profile has an independent properties file as shown in the following example.

Pay_orig.properties
    purge.application.name=FTM Sample App
    purge.subtypes=PAY_ORIG
    purge.retention.days=180
    purgable.states=S_InPTComplete,S_InPTFailed

Liq_req.properties
    purge.application.name=FTM Sample App
    purge.subtypes=LIQUIDITY_REQUEST
    purge.retention.days=30
    purgable.states=S_OutPTSent,S_OutPTFailed

Liq_resp.properties
    purge.application.name=FTM Sample App
    purge.subtypes=LIQUIDITY_RESPONSE
    purge.retention.days=60
    purgable.states=S_InPTComplete,S_InPTFailed

Gateway_req.properties
    purge.application.name=FTM Sample App
    purge.subtypes=PAYMENT_INS
    purge.retention.days=90
    purgable.states=S_OutPTSent,S_OutPTFailed

Gateway_resp.properties
    purge.application.name=FTM Sample App
    purge.subtypes=GATEWAY_ACK
    purge.retention.days=120
    purgable.states=S_InPTComplete,S_InPTFailed

Client_ack.properties
    purge.application.name=FTM Sample App
    purge.subtypes=PAYMENT_ACK
    purge.retention.days=150
    purgable.states=S_OutPTSent,S_OutPTFailed

With six distinct purge profiles, you can run six purge instances in parallel by starting or scheduling multiple purge instances, which each uses a separate profile.

You can consolidate to fewer profiles, where fewer retention periods are needed. The following table shows an example with fewer retention periods.

Table 2. Retention periods
Type	Transmission	Batch	Transaction	Retention period (in days)
Payment Origination	Y	Y	Y	180
Liquidity Request	Y	-	Y	30
Liquidity Response	Y	-	Y	30
Gateway Request	Y	-	Y	90
Gateway Response	Y	-	Y	90
Client Ack	Y	-	Y	180

Pay_orig.properties
    purge.application.name=FTM Sample App
    purge.subtypes=PAY_ORIG, PAYMENT_ACK
    purge.retention.days=180
    purgable.states=S_InPTComplete,S_InPTFailed, S_OutPTSent,S_OutPTFailed

Liq_req.properties
    purge.application.name=FTM Sample App
    purge.subtypes=LIQUIDITY_REQUEST, LIQUIDITY_RESPONSE
    purge.retention.days=30
    purgable.states=S_OutPTSent,S_OutPTFailed, S_InPTComplete,S_InPTFailed

Gateway_req.properties
    purge.application.name=FTM Sample App
    purge.subtypes=PAYMENT_INS, GATEWAY_ACK
    purge.retention.days=90
    purgable.states=S_OutPTSent,S_OutPTFailed, S_InPTComplete,S_InPTFailed