8Merging Data
Merging Data
This chapter covers the process of merging data into the Siebel database. This chapter is organized into the following sections:
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 remaining 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 remaining 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:
Notes
Territory Items
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:
Initialize the EIM tables for merge.
Select for merge the rows with matching user keys in the EIM tables.
Merge child rows into the replacement rows. EIM then deletes rows from the target base table that are specified in the EIM table.
For deleted rows, EIM sets T_MERGED_ROW_ID to the ROW_ID of the row that was merged into (the remaining row).
EIM sets T_DELETED_ROW_ID to the ROW_ID of the deleted base table row.
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 remaining rows (depending on the value for update rows in the configuration file).
EIM provides comprehensive status information about each merge process. When the process ends, you should review this information. For more information, see Checking Merge Results.
Each task involves multiple passes; at least one pass is required for each EIM table included in the process.
Running through the EIM merge process requires that you perform the following steps, which are discussed in the remaining sections of this chapter:
Preparing the EIM Tables for Merge Processing
This section provides assistance in loading the EIM tables with data used to control the process of merging rows in Siebel applications base tables. Your database administrator can use the loading tool provided by your database.
You must make sure that each EIM table row to be processed contains the appropriate values in the following columns. The following table shows a merge example for special columns.
| IF_ROW_BATCH_NUM | NAME | ROW_ID | IF_ROW_MERGE_ID |
|---|---|---|---|
1 |
IBM |
100 |
NULL |
1 |
IBM Japan |
101 |
100 |
1 |
IBM Europe |
102 |
100 |
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. Set this value to one of two values. For an EIM table row whose ROW_ID and IF_ROW_BATCH_NUM columns identify the remaining 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 the ROW_ID where this row will be merged. Upon completion of the merge process, the first row remains and the remaining rows are deleted. All child and intersection table rows that previously pointed to ROW_IDs 101 and 102 now point to 100.
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.
For more information on special columns, see EIM Table Columns. For general information on EIM tables, see Siebel EIM Tables.
Editing the Configuration File for Merge Processing
This section describes the header and process sections that you need in the EIM configuration file to properly configure EIM for a merge process. For general information on the EIM configuration file, see EIM Configuration File.
Before merge processing begins, you must change the configuration file to support this function. Such changes include:
Editing the header and process sections and parameters
Adjusting settings in the configuration file in the following ways:
Header Section Parameters Used for Merges
Parameters in the header section generally apply to all types of processes. For a description of the necessary contents in this section, see Header Section Parameters Generic to All EIM Processes.
Process Section Parameters Used for Merges
Parameters in the process section apply only to that specific process and override any corresponding value in the header section for the specific process. For generic parameters that can be used in all EIM processes, see Process Section Parameters Generic to All EIM Processes.
To merge data, you must define at least one process with type = merge. The following example contains lines that can be used in the EIM configuration file to define a merge process for the Accounts table.
[Merge Accounts] TYPE = MERGE BATCH = 1 TABLE = EIM_ACCOUNT UPDATE ROWS = TRUE
Parameters Used for Merges in Both the Header and Process Sections
The following information describes the parameters that can appear in either the header section or a process section, and are specific to a merge process. For generic parameters that can be used in all EIM processes, see Process Section Parameters Generic to All EIM Processes.
| Parameter | Description |
|---|---|
SET BASED LOGGING |
Specifies whether set-based logging is enabled. The default value is TRUE.
Note: EIM will ignore this parameter if Enable Transaction Logging is unchecked in the Remote System Preferences view of the Administration - Siebel Remote screen.
For more information on this parameter, see SET BASED LOGGING Parameter. |
UPDATE ROWS |
Specifies whether the foreign key (or keys) that reference the merged rows in the named table need to be adjusted. Valid values are TRUE (the default) and FALSE.
Note: Use the
UPDATE ROWS = Table_Name, FALSE setting carefully. Inappropriate use can result in dangling foreign key pointers.
|
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 TABLE = FALSE
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:
LOG TRANSACTIONS= TRUE
SET BASED LOGGING = FALSE
For information on the LOG TRANSACTIONS parameter, see Optional Keywords for Process Parameters. For information on the SET BASED LOGGING parameter, see Process Section Parameters Used for Merges.
Specifying Remaining Records for Merge Processes
In a merge process, data from the record you select as the remaining record is preserved, while data from the other records is lost. Do not specify the same record as both the remaining record and the record to be deleted, or it will be deleted. You should also make sure that a record is specified as a remaining record only once in a batch.
Running a Merge Process
You can run a merge process after you have:
Identified the data for merge processing
Prepared the related EIM tables
Modified the EIM configuration file accordingly
Run the merge process by completing the procedures in Running EIM.
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:
T_DELETED_ROW_ID contains the ROW_ID of the deleted base table row.
T_MERGED_ROW_ID contains the ROW_ID of the remaining base table row.
To view the results of a merge
Query the appropriate EIM table for rows whose IF_ROW_BATCH_NUM equals the batch number for the merge process.
Inspect the values of T_DELETED_ROW_ID and T_MERGED_ROW_ID.
If error flags, SQL trace flags, or trace flags were activated for the EIM process, you can also use the trace file to view the results of the EIM process. For more information on viewing the trace file, see Viewing the EIM Log File.