Overview of EIM Merge Processing:
The EIM (Enterprise Integration Manager) merge process is essential in data management. It's like an orchestra conductor, using a combination of information from EIM table rows and settings in a configuration file to orchestrate data merging in tables.
When discussing merging, we mean deleting specific rows from a base table and ensuring that other rows intersecting with the deleted ones are updated to point to the remaining rows. It's a bit like tidying up a messy room. The data from the record we choose as the "survivor" is preserved, while data from the other records gets thrown out.
Suppose there are other related records linked to the ones we're merging. In that case, those records (except for the duplicates) become associated with the survivor. Think of it as combining two address books; the entries from one get added to the other.
However, when dealing with child records (like Contacts) of the deleted rows, things can get tricky. The system updates the CONFLICT_ID of duplicate child records during the merge process to avoid confusion.
For example, suppose we're merging two parent Accounts, and they both have a Contact with the same name. In that case, the system will ensure these Contacts are correctly sorted out with updated conflict IDs.
However, there are some limitations to this process. You can only merge records that have primary user keys. Some tables, like Notes, Territory Items, and Fulfillment Items, don't have these primary keys, so they can't be merged.
Another essential thing to remember is that you can't merge rows with the same primary user key but different conflict IDs using EIM. EIM relies on user keys to tell records apart. When faced with such a situation, it marks the row as "AMBIGUOUS."
Lastly, EIM can only merge rows from specific base tables, not secondary ones. For instance, working with EIM_ASSET can help you combine two or more S_ASSET rows into a single S_ASSET row. But you can't use it to merge S_ASSET_CON rows into each other.
EIM Merge Process:
Now, let's take a peek at how EIM goes about merging data:
1. Initialization: EIM gets everything ready for the merge. It's like prepping the ingredients before cooking a meal.
2. Row Selection: EIM identifies the rows that should be merged based on matching user keys in the EIM tables. It's like picking out the ingredients you'll use for your dish.
3. Child Row Merging: Child rows are those related to the ones being merged. EIM makes sure these child rows are correctly combined with the replacement rows. Then, it deletes rows from the main table as instructed by the EIM table. It's like combining different recipe parts to create the final dish and removing unnecessary ingredients.
4. Dealing with Deletions: For the rows that have been deleted, EIM keeps a record of them. It sets T_MERGED_ROW_ID to the ROW_ID of the row that was merged into (the survivor) and T_DELETED_ROW_ID to the ROW_ID of the row that was deleted from the main table. It's like keeping track of what went where.
5. Updating Foreign Keys: Sometimes, other tables have foreign keys pointing to deleted rows. Depending on the configuration settings, EIM updates these foreign keys to point to the surviving rows. It's like ensuring that if you move an ingredient from one dish to another, you update the recipe accordingly.
Getting EIM Tables Ready for Merging:
Before diving into the complex world of EIM merging, there are some essential steps you need to take to prepare your data. It's like getting everything ready before embarking on a big project. Here's what you need to do:
1. Populate the Following Columns:
IF_ROW_BATCH_NUM: Think of this as giving each group of EIM table rows a unique batch number. It's a way of organizing and grouping the data that will be part of the merge process.
ROW_ID: When combined with the batch number in IF_ROW_BATCH_NUM, this value must result in a unique identifier. It's like labeling each item in your inventory with a specific code to avoid confusion.
IF_ROW_MERGE_ID: This is a critical step. Depending on whether a row is the one that will survive the merge or if it's going to be merged with another and then deleted, you set this value differently:
• For a row representing the surviving or merged-into data (the one that will stay intact), set this value to NULL.
• For rows that need to be merged (and subsequently removed), set this value to the ROW_ID of the row where this data will be connected. It is deciding which ingredients will be combined to create the final dish.
After the merge process, the first row survives, and the rest are deleted. It's akin to selecting the best ingredients for your recipe and discarding the ones you don't need.
IF_ROW_STAT: In each row that will be merged, set this column to "FOR_MERGE." This is like tagging specific items to indicate they are ready to join. After the processing is done, if specific rows can't be merged due to data issues, here's what you should do:
1. Change the IF_ROW_BATCH_NUM value for the rows that need to be merged again.
2. Adjust the BATCH NUMBER line in the configuration file to match your changes.
Here's the crucial part: If you don't correctly fill in all the user key columns, your merge process will encounter problems. It's similar to attempting a recipe without all the necessary ingredients. In this case, the IF_ROW_STAT column in the EIM table will be set to "NO_SUCH_RECORD." This indicates that EIM couldn't find the correct rows to merge using the provided user keys. So, ensuring your data is properly organized and labeled is essential for a successful merge process.
Parameters Found in Both Header and Process Sections:
SET BASED LOGGING: This setting determines whether set-based logging is turned on. By default, it's set to TRUE, meaning it's enabled.
UPDATE ROWS: This parameter decides whether foreign keys pointing to the merged rows in a table need to be adjusted. The default is TRUE, meaning it's enabled.
SET BASED LOGGING Parameter: When set-based logging is enabled, EIM generates separate log entries for all rows affected by its actions in each table. This is a performance boost because EIM can execute operations as sets in SQL rather than going through each row individually for transaction logging. Set-based logging is particularly useful when a table is read-only for mobile web clients. For merge operations, set-based logging is always the default. Still, you can set it to FALSE to enable transaction logging.
Updating Affected Rows: In a merge operation, some rows in a base table might be deleted, and others updated. You can use the UPDATE ROWS parameter to control updates to specific base tables. By default, it's set to TRUE, allowing updates. You can change this setting to control which tables get updated.
Avoiding Aborts of EIM Merge Processing: If an EIM merge process is unexpectedly halted, base tables related to merged rows may not be updated correctly, leading to potential data integrity problems. To prevent this, you can set specific parameters in the.IFB file:
• COMMIT EACH TABLE = FALSE: This ensures that EIM performs only one commit or rollback when aborted.
• COMMIT EACH PASS = FALSE: This setting ensures that only one commit or rollback occurs.
• ROLLBACK ON ERROR = TRUE: This directs EIM to undo any modifications in the event of an error.
• Enabling Transaction Logging for Merge Processing: To enable transaction logging for an EIM merge process, set these parameters in the.IFB file:
• LOG TRANSACTIONS= TRUE: This turns on transaction logging.
• SET BASED LOGGING = FALSE: It switches off set-based logging.
Specifying Survivor Records for Merge Processes: Data from the record chosen as the "survivor" is retained in a merge operation. In contrast, data from other records is discarded. It's crucial not to select the same record as the survivor and the victim, as it will be deleted. Also, ensure that a record is designated as a survivor only once in a batch.
Checking Merge Results: After a merge process concludes, it's essential to carefully review the results to confirm that data was merged successfully. EIM provides comprehensive status and diagnostic information during each procedure. Specifically, during a merge process, EIM stores the following values in two unique columns in the EIM tables:
• T_DELETED_ROW_ID: This contains the ROW_ID of the deleted base table row.
• T_MERGED_ROW_ID: This holds the ROW_ID of the surviving base table row. These details help track what happened during the merge process.
Comments
Post a Comment