EIM Delete Process
Identification of Rows for Deletion The EIM Delete Process involves reading information from the EIM tables and the EIM configuration file to determine which rows should be deleted from the Siebel base tables. During multiple passes through the EIM tables, EIM carries out specific tasks, including initialization, applying filter logic, and updating related tables.
Deletion Methods Supported EIM offers various methods for selecting and deleting rows, depending on the combination of EIM table row contents and configuration file parameter values. These methods include deleting rows based on user keys, matching column criteria defined in the configuration file, or deleting all rows from the base table.
Delete Process Flow To execute an EIM delete process successfully, a thorough understanding of the parameter settings specifying delete criteria is crucial. The deletion process itself comprises several steps:
1. Initialization of EIM Tables
• Suppose the configuration file's CLEAR INTERFACE TABLE parameter is set to TRUE. In that case, all rows with the specified batch number are deleted.
• If CLEAR INTERFACE TABLE is set to FALSE, EIM uses EIM table values to identify rows for deletion.
2. Deletion of Rows
• Suppose the DELETE EXACT parameter in the configuration file is TRUE. In that case, EIM deletes rows from the table that match the user key defined in the EIM table.
• Suppose the DELETE MATCHES parameter is set to a base table in the configuration file. In that case, EIM deletes rows from the target base table that match the predicate specified in the parameter.
• If the DELETE ALL ROWS parameter is set to TRUE, EIM deletes all rows from the target base table.
3. Updating Row Status
• After successful deletion, EIM sets the IF_ROW_STAT column to DELETED for the processed rows.
Handling Foreign Key References When a foreign key column references a deleted record, the behavior depends on whether the foreign key is mandatory:
• If the foreign key is required, the record with the foreign key is also deleted.
• If the foreign key is not mandatory, the foreign key column is cleared.
Cascade Deletion vs. Cascade Clear EIM deletion of a parent row causes cascade deletion of child rows only when the foreign key column in the child table is a mandatory field. Otherwise, a cascade clear operation is performed.
Parameters in the Common Header and Process Section
CASCADE DELETE ONLY This parameter, with a default value of FALSE, determines whether child records with nullable foreign keys should be deleted when the parent record is deleted. If set to TRUE, it enables the cascade deletion of child records. When set to FALSE, EIM sets the foreign keys of child records to NULL upon deleting the parent record.
CLEAR INTERFACE TABLE This parameter controls whether existing rows in the EIM table for a specific batch number should be deleted. It accepts two valid values: true (the default if DELETE EXACT = TRUE) and false (the default if DELETE EXACT = FALSE).
DELETE EXACT This parameter, with a default value of FALSE, instructs EIM to delete rows from the target base table using a user key matching algorithm with rows in the EIM table.
DELETE SKIP PRIMARY Specifies whether EIM should perform a cascade update to the primary child column. The default value is TRUE.
DELETE MATCHES This parameter allows you to specify SQL WHERE fragment deletion criteria. For example: DELETE MATCHES = EIM_ACCOUNT, (NAME LIKE "TST_ACCT%").
DELETE ALL ROWS Used to specify whether all rows in a table should be deleted. The default value is FALSE.
DELETE ROWS Determines whether rows from the target base table can be deleted. The default value is TRUE, but you can set it to FALSE to prevent deletions from a specific table while allowing them in others.
UPDATE ROWS Specifies whether foreign key references can be updated. By default, this parameter is set to TRUE and affects all tables. You can also specify a table name to affect only specific tables, for example UPDATE ROWS = S_CONTACT, TRUE. Setting this parameter to FALSE prevents EIM from updating rows in the specified base table. If you need to select multiple tables, use one UPDATE ROWS statement for each table.
IGNORE BASE COLUMNS This feature allows you to select which columns from the base table should be disregarded during the import process. You have the flexibility to list multiple column names, separating them with commas. Additionally, you can qualify these column names with their respective base table names if needed.
However, it's important to note that you cannot ignore columns that are marked as required or those that serve as user keys. These columns are integral to the data and must be considered in the import process.
The primary advantage of using this parameter is its ability to enhance performance. By excluding certain columns, you can streamline the update process, especially when dealing with a large dataset. By default, all columns in the base table are processed unless you specify otherwise.
DELETE MATCHES Parameter
The DELETE MATCHES parameter serves to specify a WHERE clause expression used for filtering base table rows during the EIM process. This expression comprises the Siebel base table name and the filter expression applied to the target base table. For instance, an example usage might appear as follows: DELETE MATCHES = S_ORG_EXT, (LAST_UPD > '2000-06-22' AND LAST_UPD < '2000-06-23').
The expression constitutes a self-contained WHERE clause expression, excluding the "WHERE" keyword. It should exclusively employ literal values or column names (with optional base table name prefixes). Additionally, a space should separate the operator from the operand in the expression (a space should be inserted between '>' and '2000-06-22'). When deleting rows for a specific date, it is recommended to utilize date ranges, as shown in the example, rather than specifying a single date.
By default, DELETE MATCHES expressions are not utilized within the EIM process. This parameter solely writes the user key values of the deleted target table rows into the EIM table columns. It does not populate values of non-user key columns or non-target table rows' column values into the EIM table.
It is important to note that the deleted rows cannot be reimported using the EIM table rows generated by the EIM delete process because they will not contain all the original information. Consequently, this parameter should exclusively be employed to delete rows from target base tables. Rows will be deleted from the target base table even if the DELETE ROWS parameter is configured as FALSE for that specific table.
DELETE ALL ROWS Parameter
Overview: The DELETE ALL ROWS parameter is a configuration setting that determines whether all rows within a target base table should be deleted during an EIM process. By default, this parameter is set to FALSE, indicating that not all rows will be removed.
Usage: When this parameter is set to TRUE, it instructs EIM to delete all records within the specified target base table, effectively clearing the entire table of its data. Significantly, it disregards existing values within the EIM table and any DELETE MATCHES expressions.
Data Capture: It's important to note that when using the DELETE ALL ROWS parameter, only the user key values of the deleted target table rows are recorded in the EIM table columns. This parameter does not capture or store values from non-user key columns or the column values of non-target table rows in the EIM table.
Reimport Limitation: Deleted rows that have been removed using this parameter cannot be reintroduced into the system by utilizing the EIM table rows generated by the EIM delete process. These rows will lack the original information necessary for a successful reimport.
Performing Bulk Data Deletion
Procedure: When the requirement is to eliminate all data rows within a specific target base table, follow these steps:
1. Parameter Configuration: Adjust the DELETE ALL ROWS parameter within the EIM configuration file to TRUE, as its default setting is FALSE.
2. Example Configuration: Here is an example configuration entry that can be incorporated into the EIM configuration file to initiate the deletion of all rows from a hypothetical "accounts" table:
sqlCopy code
[Delete Accounts] TYPE = DELETE BATCH = 200 TABLE = EIM_ACCOUNT DELETE ALL ROWS = TRUE
Deleting Rows from Extension Tables
Extension Table Deletion: Removing a row from one-to-one extension tables of the "*_X" type requires the deletion of its associated parent row. For instance, if you wish to eliminate a row from "S_CONTACT_X," you must also delete the parent row from "S_CONTACT." When dealing with data deletion in an extension column, update it with NULL by setting NET CHANGE = FALSE in the configuration file. Additionally, if necessary, employ the ONLY BASE COLUMNS option.
Deleting File Attachments
File Attachment Removal: The deletion of file attachments is managed by EIM through the removal of the row associated with each file attachment. Following the deletion of all file attachments, it is advisable to use the Siebel File System Maintenance Utility, named "sfscleanup.exe," during periods of minimal network activity to cleanse the file attachment directory of any unused file attachments.
Procedure for File Attachment Deletion:
1. Execute an EIM delete process specifically designed for all file attachments intended for deletion.
2. After successfully deleting all file attachments, initiate the Siebel File System Maintenance Utility, "sfscleanup.exe," to perform cleanup operations on the file attachment directory.
Handling Aborts of EIM Delete Processing
Aborted EIM Processes: In cases where an EIM delete process is prematurely aborted, it may result in base tables associated with deleted rows not being updated as expected. Consequently, orphaned rows may be created, potentially leading to critical data integrity issues.
Preventive Measures: To mitigate this issue, consider configuring the following parameters.IFB file to ensure that the EIM delete process handles aborts effectively:
• COMMIT EACH TABLE: Set to FALSE to prevent excessive commits for each table.
• COMMIT EACH PASS: Set to FALSE to avoid excessive commits for each pass.
• ROLLBACK ON ERROR: Set to TRUE to ensure a rollback in case of an error, preventing data integrity problems.
Comments
Post a Comment