Understanding database considerations for history inquiry

This section provides more information on the database triggers that ship with IBM® InfoSphere® Master Data Management.

The history inquiry function of InfoSphere MDM relies on a set of triggers to populate the product's audit tables. These tables are all prefixed with "H_" and otherwise generally share the same name as the operational table it stores audit information for, with a few exceptions due to table name length restrictions.

With each add or update, the triggers write a historical record of the record updated or added to the audit tables for the operational tables affected during the transaction. As such, the triggers that come with the InfoSphere MDM product allow the audit tables to store both historical and current client information. A record in a audit or history table is considered current when the HIST_END_DATE has no value. In other words, the historical record has not yet been ended.

InfoSphere MDM provides two sets of triggers with the database installation:

• A set of compound triggers – CreateTriggers_compound.sql
• A set of simple triggers – CreateTriggers_simple.sql

If the compound triggers are installed, each of the operational tables within the InfoSphere MDM product database has two active triggers.

The active triggers work with the InfoSphere MDM operational tables-all non-code tables in the database; for example, the CONTACT table is an operational table, but CDLANGTP is not. A number of admin services tables also include related history tables and triggers. Any insertion or update from the table activates one of the triggers. This trigger then populates the current image of the business object to the corresponding audit table as a new record. Each audit record has a HIST_ACTION_CODE column that shows the type of trigger that was activated to cause the audit record to be created—either a "I" value for insert, or "U" value for update. Each audit record also populates a HIST_CREATE_DT, which stores the actual date/time of the trigger activation.

The audit records also contain a HIST_END_DT column, which is populated depending on the trigger type. For an insert, HIST_END_DT is simply set as NULL. For an update, the new audit record has the HIST_END_DT set to NULL, and the update trigger finds the last audit image of the same operational record and sets HIST_END_DT to the current trigger activation time, subtracting one microsecond. Subtracting one microsecond ensures that the timeline of the audit records are synchronized.

InfoSphere MDM uses these audit records to compare how a business object has changed between two points in time. It can also retrieve a specific image of the business object for a given point in the past.

If simple triggers are installed, each of the operational tables within the InfoSphere MDM product database has only one trigger for update actions. A number of admin services tables also have related history tables and triggers included. When new records are inserted into any operational table, no audit histories are recorded. When records are updated, the update trigger is activated and audit records are created. The HIST_CREATE_DT column is populated by the LAST_UPDATE_DT column of the operational record. The LAST_UPDATE_DT is retrieved from the previous image of the updating operational record. The new image of the LAST_UPDATE_DT in the operational record is set as the HIST_END_DT and 1 microsecond is subtracted from the HIST_END_DT to ensure the history timeline is synchronized.

Optionally, delete triggers may be installed into InfoSphere MDM product database. The scripts to install either simple delete triggers and one for compound delete triggers are available with the database installation scripts. Installing the delete triggers is optional and must be run manually. Once the delete triggers are installed, all delete actions are recorded in the audit tables.