E Purge Strategy

The purge scripts helps in removing the old transactional data.

This script can run in two modes:

• Silent Mode

  Execute the procedure without passing any parameters, it calculates the period and remove the data. By default it keeps 6 months data and remove older than that. For example if some one wants to keep only 2 months of data and remove rest of the data, need to modify the procedure just by updating variable "howManyMonths NUMBER(5) := 6" to the required value.

  howManyMonths = 2;

• Interactive Mode

  Pass the fromDate and toDate parameters to the procedure to purge the specified period of data. Data will be removed for only specified period i.e fromDate to toDate.

  From and To dates format should be in below format:

  FROMDATE := TO_TIMESTAMP('2017-01-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS');

  TODATE := TO_TIMESTAMP('2017-04-30T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS');

The deleted data will be committed default in both silent and interactive modes.

Execute Purge SQL

Silent Mode

SET SERVEROUTPUT ON;
DECLARE
FROMDATE TIMESTAMP;
TODATE TIMESTAMP;
BEGIN
FROMDATE := NULL;
TODATE := NULL;
BDI_PURGE_SQL.PURGE_JOB_INT_REPO (
FROMDATE => FROMDATE,
 		TODATE => TODATE  );
END;

Interactive Mode

SET SERVEROUTPUT ON;
DECLARE
FROMDATE TIMESTAMP;
TODATE TIMESTAMP;
BEGIN
FROMDATE := TO_TIMESTAMP ('2017-01-03T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS');
TODATE := TO_TIMESTAMP ('2017-01-03T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS');
BDI_PURGE_SQL.PURGE_JOB_INT_REPO (
  			FROMDATE => FROMDATE,
  			TODATE => TODATE  );
END;

The purge sql removes the data from the following tables for the respective schema.

Schema Name	Sql Name	Table Name
job-int-schema	purge_job_int_repo.sql	BDI_DWNLDR_TRNSMITR_EXE_DSET BDI_DWNLDR_TRNSMITTR_TRANS
job-rcvr-schema	purge_job_rcvr_repo.sql	BDI_RECVR_TRANSMISSION_BLOCK BDI_RECEIVER_TRANSMISSION BDI_RECEIVER_TRANSACTION
batch-db-schema	purge_batch_db_repo.sql	JOBSTATUS STEPSTATUS STEPEXECUTIONINSTANCEDATA EXECUTIONINSTANCEDATA JOBINSTANCEDATA CHECKPOINTDATA
process-schema	purge_process_repo.sql	BDI_ACTIVITY_EXEC_INSTANCE BDI_PROCESS_CALL_STACK BDI_PROCESS_EXEC_INSTANCE