Deleting records from CHANGE_HISTORY_TABLE and CHANGE_CAUSE_TABLE

You can remove old records to improve performance and maintain the tables at a smaller size. After you remove the records from the CHANGE_HISTORY_TABLE, you can safely remove the corresponding entries from the CHANGE_CAUSE_TABLE.

To free storage space in TADDM databases, use SQL queries to remove old data manually from the CHANGE_HISTORY_TABLE. The following command is an example of such an SQL query, where the integer 1225515600000 represents the date, 1 November 2008, expressed in the same format as that returned by the System.currentTimeMillis() Java™ method, or a number equal to the difference, measured in milliseconds, between the current time and midnight, 1 January 1970 UTC:
DELETE FROM CHANGE_HISTORY_TABLE
WHERE PERSIST_TIME < 1225515600000 (this is the Java time stamp)
To convert a date to a Java time stamp, use the following code:
import java.util.*;
import java.text.*;
import java.sql.Timestamp;

public class DateToString {

  public static void main(String args[]) {
  try {
     String str = args[0];
     SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); 
     Date date = formatter.parse(str);

    long msec = date.getTime();

    System.out.println("Date is " +date);
    System.out.println("MillSeconds is " +msec);

    } catch (ParseException e)
    {System.out.println("Exception :"+e);    }    

  }
}
Run the code as follows:
java DateToString 1/11/2008
Date is Sat Nov 01 00:00:00 EST 2008
MillSeconds is 1225515600000
Use the resulting Java time stamp in the SQL query.

If an exceptional number of records exist in the CHANGE_HISTORY_TABLE, you might want to perform incremental deletes (deleting a subset of records at a time) to avoid filling transaction logs in the database.

After you clear the CHANGE_HISTORY_TABLE, you can safely remove the corresponding entries in the CHANGE_CAUSE_TABLE. The CHANGE_CAUSE_TABLE is a link table that is used for change propagation. For example, if you add a new software component to the operating system, the table links this change to the computer system on which the operating system runs. You can remove records in the CHANGE_CAUSE_TABLE with the following command:
delete from change_cause_table where cause_id not in (select id from change_history_table)

Timeframes for removing data

To limit database growth over time, you can manage the size of the change history data stored by TADDM. When determining the optimal time frame for removing data from the change history table, consider what you use the change history data for, and whether the change history information is used by other applications.

If the change history information is being used by another application, ensure that you perform application synchronizations more frequently than the number of weeks of change history data maintained in the CHANGE_HISTORY_TABLE.

The following examples illustrate some typical scenarios:
  • If you are using change history data for problem determination and you want to investigate problems that occurred five weeks ago, keep at least five weeks of data in CHANGE_HISTORY_TABLE.
  • If you synchronize Tivoli® Business Service Manager (TBSM) weekly, maintain more than one week of change history data in the TADDM change history table.

It is important to note that in synchronization server deployments, a large amount of change history data on the domain servers increases the time a full synchronization takes to complete.

Data maintenance in a synchronization server deployment

In a domain server deployment, you can base data maintenance decisions solely on the data needs for the domain. However, in a synchronization server deployment, you must coordinate the removal of change history data between each domain server database and the synchronization server database, and you must remove the data from all of these databases.

In a synchronization server deployment, use the following guidelines for data maintenance:
  • Keep change history data at the domain level for a period of time that is greater than the period of time between each scheduled synchronization of the domain server databases with the synchronization server database. For example, if the synchronization occurs on a weekly schedule, maintain at least two weeks of change history data in each domain server database.
  • Remove data from a domain server database first. Then remove data from the synchronization server database.
  • The best practice is to maintain the same number of weeks of change history data in all TADDM databases. However, the period that change history data is kept in the synchronization server database can vary from the period that such data is kept in the domain server databases.
  • After you determine a timeframe for data removal that meets the specific needs of your environment, the best practice is to remove the data just after the occurrence of a synchronization between the domain server databases and the synchronization server database.