WTC Data Purge Syntax

Document ID : KB000117214
Last Modified Date : 10/10/2018
Show Technical Document Details
Introduction:
The MDM database DBA found that the purge process doesn’t seem to be replicating on our DR database, so she wants to write a cronjob to run it manually. The issue is, we don’t know the syntax on how to run the purge process manually. It looks like the stored procedure question is this: SP_A_SCHEDULED_DB_CLEANUP. It needs parameters for the table name, column name, date, and max rows to cleanup. Can we get a list of the commands and parameters necessary to clean up all the tables ourselves? We need this within the next couple days because we’re about to have more than 15 days of data in the DR database.
Environment:
MDM 18.1
 
Instructions:
Below is the sample script to execute the cleanup.
 
DECLARE
  STABLENAME VARCHAR2(200);
  SCOLUMNNAME VARCHAR2(200);
  DOLDERTHAN DATE;
  NMAXROWCLEANUP NUMBER;
BEGIN
  STABLENAME := 'A_LOG_MESSAGE';
  SCOLUMNNAME := 'DATETIME';
  DOLDERTHAN := to_date('2018-09-22','yyyy-MM-dd');
  NMAXROWCLEANUP := 250000;
 
  SP_A_SCHEDULED_DB_CLEANUP(
    STABLENAME => STABLENAME,
    SCOLUMNNAME => SCOLUMNNAME,
    DOLDERTHAN => DOLDERTHAN,
    NMAXROWCLEANUP => NMAXROWCLEANUP
  );
--rollback;
END;
 
This should be executed for below tables and columns.
 
Table A_LOG_MESSAGE Column  DateTime
Table A_LOG_SESSION Column  StartTime
Table A_LOG_REPLICATION Column  DateTime
Table A_LOG_ALL_RAISED_EVENT Column  DateTime
Table A_LOG_CLOSED_ALERT Column  DateTime
Table A_LOG_CLOSED_EVENT Column  DateTime
Table A_LOG_CLOSED_EVENT_ASSOC Column  DateTime
Table A_LOG_TRIGGER_ACTION Column  ActionCompletionServerTime
Table A_LOG_TRIGGER_EVENT_DATA Column  TriggerEventServerTime
Table A_LOG_FILE_TRANSFER Column  EndTime
Table A_LOG_EVENT_DETAIL Column  EndTime
Table A_LOG_SEC_MGR_HH Column  DateTime
Table A_LOG_POLICY_DELIVERY Column  DeliveryTime
Table A_IPHONE_LOG Column  TimeStamp
Table A_LOG_IOS_POLICY_DELIVERY Column  DeliveryTime
Table A_WINPHONE_CONNECTION_LOG Column  StartTime
Table A_PACKAGE_FEEDBACK Column  Received
Table A_LOG_ADMIN_ACTIVITY Column  ActionTime