Database Considerations

Database Considerations
	Change Notification

The change notification support of TMCore relies on a set of database triggers and tables to record the details of a change event. The triggers are used to log events to the tables, and the change notification system queries these tables to determine what the most recent changes are. Updating these tables adds an overhead to every update of the topic map and for some applications, where change polling is not a requirement, this overhead may be unecessary.

By default, a TMCore installation has change logging enabled. To disable the change logging, the database owner must run the script found at INSTALL_DIR/scripts/disable-changelog.sql. To enable change logging on again, the database owner must rung the script found at INSTALL_DIR/scripts/enable-changelog.sql.

Even if change logging is enabled, to maintain database performance we recommend that you periodically purge the change event logging tables. This can be done by executing the stored procedure TM_CLEAR_CHANGELOG. This procedure takes two optional parameters as described below:

@tm: This integer parameter specifies the ID of the topic map to delete change log information for. If this parameter is not speciifed, then the change log information for all topic maps will be purged.
@leaveLast: This integer parameter specifies the maximum number of rows to leave in each change log table after the purge. The value specifies the maximum difference between the ID of the highest change entry and the ID of the entries to leave in the log after purging. The purpose of this is to ensure that any applications that are polling the database for changes do not miss a log because it is created and then purged between polling cycles. This parameter defaults to a value of 1000 which should ensure that most applications with an average update frequency (100 additions, deletions and modifications per minute) and reasonably frequent polling frequency (of the order of 5 minutes between polling cycles) should not lose events, however if you have applications with a higher frequency of changes or which poll on a less frequent interval you should specify this parameter with a higher value.

Note

By default, permission to execute the TM_CLEAR_CHANGELOG procedure is granted only to the database owner role.