Tuesday, January 14, 2014

Data integrity issues in EIM delete and merge process

EIM Merge and Delete processes should use only one commit per batch to avoid data integrity issues

An aborted Enterprise Integration Manager (EIM) Merge or Delete process will leave orphan records if the EIM process is terminated abnormally.

The following EIM .ifb file parameters determine when an EIM process will commit and if the pending SQL statements will be committed if an error is encountered.

COMMIT EACH TABLE = TRUE
COMMIT EACH PASS = TRUE
ROLLBACK ON ERROR = FALSE

The default settings for these .ifb file parameters specify that the EIM process will execute a commit after each EIM pass or each table is processed and that the pending SQL statements will be committed If the process is aborted. Generally with EIM import, or export types of processes, these settings are appropriate. If the EIM process terminates in the middle of processing, the work done so far is committed. Then the batch can be reloaded into the EIM interface tables and the batch can be re-run. The remaining work that was not processed in the aborted EIM import, or export type of process will be done in the next run.

These default settings are not appropriate for an EIM Merge or Delete process because if the process terminates in the middle of processing there can be data integrity issues. For example if contact A is merged into contact B, the EIM Merge process does the following steps. Initialize the interface tables for merge. Delete the contact A record in the base table. Update the base tables that have foreign keys that point to the deleted contact A record to point to the contact B "survivor" record.

EIM will commit after each pass and each table is processed and will not rollback the transaction on an abort if the default parameters are used. This can cause data integrity issues if the EIM Merge or Delete process has not completed. If the commit and rollback .ifb file parameters for an EIM merge or delete process are not changed, the Siebel data can have "orphan" records in the base table, which have foreign keys that point to the deleted Contact A record. In that case the EIM Merge process was terminated before EIM could update all of those tables foreign keys to point to the survivor record.
If this situation occurs, make a backup of the EIM interface table since the temporary columns will have information about the EIM merge or delete processing.

Therefore, using the following settings for an EIM process is recommended:

COMMIT EACH PASS = FALSE
COMMIT EACH TABLE = FALSE
ROLLBACK ON ERROR = TRUE

This ensures that EIM will either process the entire merge or delete batch or rollback the batch.
The database resources need to be large enough to accommodate this large transaction. It is also recommended that the EIM Merge batch be limited to only 100 records for performance reasons.

Likelihood of Occurrence:

The data integrity issue can happen when an EIM Merge or Delete type process is aborted.

Possible Symptoms:

A possible symptom is for the EIM server task to error out before completing. Reviewing the records in the Siebel tables reveals that the base table records associated to the target base table, which was deleted, still point to the deleted target base table record.

Workaround or Resolution:

Using the following settings for an EIM Merge or Delete type process is recommended:

COMMIT EACH PASS = FALSE
COMMIT EACH TABLE = FALSE
ROLLBACK ON ERROR = TRUE