Merge Data Through Siebel EIM
Overview of EIM Merge Processing:
2. Territory Items
3. Fulfillment Items
EIM Merge Process:
2. Select for merge the rows with matching user keys in the EIM tables.
3. Merge child rows into the replacement rows. EIM then deletes rows from the target base table that are specified in the EIM table.
4. For deleted rows, EIM sets T_MERGED_ROW_ID to the ROW_ID of the row that was merged into (the surviving row).
5. EIM sets T_DELETED_ROW_ID to the ROW_ID of the deleted base table row.
6. Update child rows containing foreign keys that point to newly deleted rows. For base tables that have foreign keys in newly deleted rows, EIM updates the foreign keys to point to surviving rows (depending on the value for UPDATE ROWS in the configuration file).
Preparing the EIM Tables for Merge Processing:
For an EIM table row whose ROW_ID and IF_ROW_BATCH_NUM columns identify the surviving or merged-into row, set this value to NULL.
For EIM table rows whose ROW_ID and IF_ROW_BATCH_NUM columns identify a row to be merged (and subsequently deleted), set this value to
Parameter the ROW_ID where this row will be merged.
Upon completion of the merge process, the first row survives and the remaining rows are deleted.
Common Header and Process Section Parameters:
SET BASED LOGGING:
UPDATE ROWS:
SET BASED LOGGING Parameter:
Updating Affected Rows:
Avoiding Aborts of EIM Merge Processing:
COMMIT EACH PASS = FALSE
ROLLBACK ON ERROR = TRUE
Enabling Transaction Logging for Merge Processing:
SET BASED LOGGING = FALSE
Specifying Survivor Records for Merge Processes:
Checking Merge Results:
2. T_MERGED_ROW_ID contains the ROW_ID of the surviving base table row.
Overview of EIM Merge Processing:
- EIM uses a combination of EIM table row contents and configuration file parameter values to control the merge process.
- A merge process deletes one or more existing rows from the base table and makes sure that intersecting table rows are adjusted to refer to the remaining rows.
- Data from the record you select as the surviving record is preserved. Data from the other records is lost.
- If there are other records associated with the records you merge, those records—with the exception of duplicates—are associated with the surviving record.
- Duplicate child records of the deleted rows will have CONFLICT_ID updated during the merge process.
- For example, when merging two Accounts (parent), the user keys of the Contacts (child) will be compared, and if the same Contact belongs to both Accounts, the Contact of the deleted Account will have its CONFLICT_ID updated.
- You can only merge records that have primary user keys. Because records in the following tables do not have primary user keys, these records cannot be merged:
2. Territory Items
3. Fulfillment Items
- It is not possible to merge rows that have the same primary user key and different conflict IDs using EIM, because EIM relies on user keys to identify rows in base tables.
- If there are two rows in the base table that have the same user key but different conflict IDs, EIM cannot distinguish between these rows.
- In such cases, the IF_ROW_STAT field of the row in the EIM table will be marked as AMBIGUOUS.
- EIM can only be used to merge rows from target base tables and not secondary tables.
- For example, the target base table for EIM_ASSET is S_ASSET. EIM can only be used to merge two or more S_ASSET rows into single S_ASSET rows. You cannot use EIM to merge two or more S_ASSET_CON rows into single S_ASSET_CON rows.
EIM Merge Process:
- During its multiple passes through the EIM tables, EIM completes the following tasks within a merge process:
2. Select for merge the rows with matching user keys in the EIM tables.
3. Merge child rows into the replacement rows. EIM then deletes rows from the target base table that are specified in the EIM table.
4. For deleted rows, EIM sets T_MERGED_ROW_ID to the ROW_ID of the row that was merged into (the surviving row).
5. EIM sets T_DELETED_ROW_ID to the ROW_ID of the deleted base table row.
6. Update child rows containing foreign keys that point to newly deleted rows. For base tables that have foreign keys in newly deleted rows, EIM updates the foreign keys to point to surviving rows (depending on the value for UPDATE ROWS in the configuration file).
Preparing the EIM Tables for Merge Processing:
- Populate the following columns:
- IF_ROW_BATCH_NUM: Set this to an identifying number for all EIM table rows to be processed as a batch.
- ROW_ID: This value in combination with the nonempty contents of IF_ROW_BATCH_NUM must yield a unique value.
- IF_ROW_MERGE_ID:
For an EIM table row whose ROW_ID and IF_ROW_BATCH_NUM columns identify the surviving or merged-into row, set this value to NULL.
For EIM table rows whose ROW_ID and IF_ROW_BATCH_NUM columns identify a row to be merged (and subsequently deleted), set this value to
Parameter the ROW_ID where this row will be merged.
Upon completion of the merge process, the first row survives and the remaining rows are deleted.
- IF_ROW_STAT: In each row to be merged, set this column to FOR_MERGE to indicate that the row has not been merged. After processing, if certain rows were not merged due to a data error, you should change:
- IF_ROW_BATCH_NUM value for the rows that require remerging.
- BATCH NUMBER line in the configuration file.
- If you do not correctly populate all the user key columns, the merge process will fail and the IF_ROW_STAT column in the EIM table will be set to the value NO_SUCH_RECORD. This indicates that EIM cannot find the appropriate rows to merge using the specified user keys.
Common Header and Process Section Parameters:
SET BASED LOGGING:
- Specifies whether set-based logging is enabled.
- Default is TRUE.
UPDATE ROWS:
- Specifies whether the foreign key (or keys) that reference the merged rows in the named table need to be adjusted.
- Default is TRUE.
SET BASED LOGGING Parameter:
- When set-based logging is enabled, a separate log entry is generated for all rows in each table affected by EIM.
- This allows greater performance improvement because EIM can perform the operations as set operations in SQL, without resorting to row-by-row processing to support the transaction log.
- Set-based transaction logging is most useful when a table is read-only to mobile Web clients.
- Set-based logging is always the default for merge.
- The SET BASED LOGGING parameter must be set to FALSE to allow transaction logging for merge.
Updating Affected Rows:
- During a merge operation, a specific base table may have some rows deleted and others updated.
- You can use the UPDATE ROWS parameter to prevent updates to one base table while allowing updates to another.
- By default, UPDATE ROWS = TRUE.
Avoiding Aborts of EIM Merge Processing:
- If an EIM merge process is aborted, base tables associated with merged rows may not be updated.
- Orphan rows may be created because foreign keys may not have been updated. This may cause critical data integrity issues.
- To avoid this problem, set the following parameters in the .IFB file so the EIM merge process performs only one commit or rollback when aborted:
COMMIT EACH PASS = FALSE
ROLLBACK ON ERROR = TRUE
Enabling Transaction Logging for Merge Processing:
- To enable transaction logging for an EIM merge process, set the following parameters in the .IFB file so the EIM merge process runs in ongoing (row-by-row) mode:
SET BASED LOGGING = FALSE
Specifying Survivor Records for Merge Processes:
- In a merge process, data from the record you select as the surviving record is preserved, while data from the other records is lost.
- Do not specify the same record as both the survivor and the victim or it will be deleted. You should also make sure that a record is specified as a survivor only once in a batch.
Checking Merge Results:
- When a merge process ends, you should carefully check the results to verify that data was successfully merged.
- During each process, EIM writes comprehensive status and diagnostic information to several destinations.
- During a merge process, EIM writes the following values to two special columns in the EIM tables:
2. T_MERGED_ROW_ID contains the ROW_ID of the surviving base table row.