EIM Delete process explained!

EIM delete is not as complex as merge process  but a proper understanding of its functioning is very important as one may happen to accidentally delete critical customer data in the process. Below are few things that one must understand for delete process to work as per expectation:

How does EIM delete process work?

1. EIM reads information from the EIM tables and the EIM configuration file to identify rows to delete from the Siebel base tables.
2. During its multiple passes through the EIM tables, EIM performs the following tasks:
• EIM initializes the EIM tables for deletion.
• It applies filter logic to do one of the following:
• Select rows for deleting
• Insert EIM tables rows that correspond to matching base table rows
• Select rows with matching user keys in the EIM tables
3. EIM updates other tables with rows containing foreign keys that point to newly deleted rows.

What delete methods are supported?

• EIM uses a combination of EIM table row contents and configuration file parameter values to determine the method for selecting rows to be deleted. The following methods are supported:

1. Delete rows in a Siebel base table with user key values specified in the corresponding EIM table. (DELETE EXACT)
2. Delete rows in the base table where the contents of a named column match those specified by a WHERE clause expression in the configuration file. (DELETE MATCHES)
3. Delete all rows in the base table (DELETE ALL ROWS)

How does the delete process flow?

• Preparing for an EIM delete process requires a thorough understanding of the parameter settings that specify delete criteria.
• To delete data, EIM performs the following steps:

1. EIM initializes EIM tables for delete.
• If CLEAR INTERFACE TABLE in the configuration file is TRUE, all rows with the specified batch number are deleted. CLEAR INTERFACE TABLE must be FALSE for a delete process that uses EIM table values to identify rows for deletion.
2. EIM deletes rows.
• If the DELETE EXACT parameter in the configuration file is set to TRUE, EIM deletes the rows from the table that match the user key defined in the EIM table.
• If the DELETE MATCHES parameter in the configuration file is set to a base table, EIM deletes the rows from the target base table that match the predicate specified in the parameter.
• If the DELETE ALL ROWS parameter in the configuration file is set to TRUE, EIM deletes all rows from the target base table.
3. EIM sets IF_ROW_STAT to DELETED for rows that are successfully processed.
• When a foreign key column that references the deleted record is a required one, the record with the foreign key is deleted. Otherwise, the foreign key column is cleared. 
• EIM deletion of a parent row causes cascade deletion of child rows only if the foreign key column in the child table is a mandatory column. Otherwise a cascade clear is performed.

DELETE EXACT Parameter:

• Specifies the base table rows to delete by using user key values specified in the EIM table.
• By default, DELETE EXACT = FALSE.
• If DELETE EXACT is set to TRUE, you must use the ONLY BASE TABLES parameter in conjunction with this parameter to identify the base tables.
• Although this parameter can be used to delete rows from both target and non-target base tables use the DELETE EXACT parameter to delete only non-target base tables containing user keys.
• Rows in non-target base tables that do not contain user keys will not be deleted. For example, you cannot use the DELETE EXACT parameter to update the S_ACTION_ARG table and the S_ESCL_ACTION table because there are no user keys defined for these tables.
• As another example, you can use DELETE EXACT to delete any of the non-target base tables such as S_ADDR_PER and S_ACCNT_POSTN using the EIM_ACCOUNT table. In this case, the EIM_ACCOUNT table would need to be loaded with records that would singularly identify the S_ACCNT_POSTN or the S_ADDR_PER record to be deleted.
• To use the DELETE EXACT parameter to delete data from base tables other than the target base table, specify the user key columns only for a single base table for each row in the EIM table.
• When specifying rows for exact deletion, make sure any columns not necessary to specify the row to be deleted are NULL to avoid problems with deleting from the wrong base table.
• EIM tries to enforce this behavior by requiring other user key columns to be NULL. If a row cannot be identified as clearly referring to a row in a single base table, that row will fail to be deleted.

DELETE MATCHES Parameter:

• Specifies a WHERE clause expression for filtering base table rows.
• The value is in two parts: the Siebel base table name and the filter expression that goes against the target base table.
• An example would be: DELETE MATCHES = S_ORG_EXT, (LAST_UPD > ‘2000-06-22’ AND LAST_UPD < ‘2000-06-23’)
• The expression is a self-contained WHERE clause expression (without the WHERE) and should use only literal values or column names (optionally prefixed with the base table name). There must also be a space separating the operator from the operand in this expression (a space must be added between > and ‘). When deleting rows for a specific date, you should use date ranges as shown in the example instead of setting the date equal to a specific date.
• By default, DELETE MATCHES expressions are not used.
• This parameter will only write the user keys values of the deleted target table rows to the EIM table columns. It will not write values of nonuser keys columns or non-target table rows column values to the EIM table.
• The deleted rows cannot be re-imported using the EIM table rows written by the EIM delete process, because they will not contain all the original information.
• Only use this parameter to delete rows from target base tables. Rows will be deleted from the target base table even if the DELETE ROWS parameter is set to FALSE for that table.

DELETE ALL ROWS Parameter:

• Specifies that all rows in the target base table are to be deleted.
• Default is FALSE.
• Existing values in the EIM table and DELETE MATCHES expressions are ignored.
• This parameter will only write the user keys values of the deleted target table rows to the EIM table columns. It will not write values of nonuser keys columns or non-target table rows column values to the EIM table.
• The deleted rows cannot be re-imported using the EIM table rows written by the EIM delete process, because they will not contain all the original information.

Common header and process section parameters for delete process:

PARAMETERS	DESCRIPTION
CASCADE DELETE ONLY	Default is FALSE, Set this parameter to TRUE to delete child records with null able foreign keys when the parent record is deleted. If FALSE, then when EIM deletes a parent record, it sets the foreign keys of the child records to NULL.
CLEAR INTERFACE TABLE	Specifies whether existing rows in the EIM table for the given batch number should be deleted, Valid values are true (the default unless DELETE EXACT = TRUE) and false (the default if DELETE EXACT = FALSE).
DELETE ALL ROWS	Used for deleting all rows in table, Default is FALSE.
DELETE EXACT	Delete using user key matching algorithm with rows in EIM table, Default is FALSE.
DELETE SKIP PRIMARY	Specifies whether EIM should perform a cascade update to the primary child column, Default is TRUE.
DELETE MATCHES	SQL WHERE fragment deletion criteria, Example: DELETE MATCHES = EIM_ACCOUNT, (NAME LIKE "TST_ACCT%")
DELETE ROWS	Specifies whether rows from the target base table can be deleted, Default is TRUE, Prevents deletions from one table while allowing them in others. For example, the following parameter setting prevents deletion of rows from the S_ADDR_ORG table: DELETE ROWS = S_ADDR_ORG, FALSE
IGNORE BASE COLUMNS	Specifies base table columns to be ignored by the import process, Use commas to separate column names, which can be qualified with base table names, Required and user key columns cannot be ignored, Improves performance when updating all but a few columns, The default is to not ignore any base table columns.
UPDATE ROWS	Specifies whether foreign key references can be updated, This parameter can be used to prevent the updating of foreign key references with a setting of FALSE, Default is TRUE, which affects all tables, To affect only specific tables; you can specify a table name. For example: UPDATE ROWS = S_CONTACT, TRUE. Also prevents updates in one table while allowing them in others, If this parameter is set to FALSE, EIM does not update rows in the specified base table, If you need to specify multiple tables, use one UPDATE ROWS statement for each table.

Image of Delete process parameters: