Delete Data Through Siebel EIM
EIM Delete Process:
2. Insert EIM tables rows that correspond to matching base table rows
3. Select rows with matching user keys in the EIM tables
Deletion Methods Supported:
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.
3. Delete all rows in the base table regardless of EIM table row contents or configuration file WHERE clause expressions.
Delete Process Flow:
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.
Common Header and Process Section Parameters:
CASCADE DELETE ONLY:
CLEAR INTERFACE TABLE:
DELETE ALL ROWS:
DELETE EXACT:
DELETE SKIP PRIMARY:
DELETE MATCHES:
DELETE ROWS:
IGNORE BASE COLUMNS:
UPDATE ROWS:
DELETE EXACT Parameter:
DELETE MATCHES Parameter:
DELETE ALL ROWS Parameter:
Deleting All Data Rows:
TYPE = DELETE
BATCH = 200
TABLE = EIM_ACCOUNT
DELETE ALL ROWS = TRUE
Deleting Rows from Extension Tables:
Deleting File Attachments:
2. After all file attachments have been deleted, run the Siebel File System Maintenance Utility named sfscleanup.exe to clean up the file
attachment directory.
Handling Aborts of EIM Delete Processing:
COMMIT EACH PASS = FALSE
ROLLBACK ON ERROR = TRUE
EIM Delete Process:
- EIM reads information from the EIM tables and the EIM configuration file to identify rows to delete from the Siebel base tables.
- 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:
2. Insert EIM tables rows that correspond to matching base table rows
3. Select rows with matching user keys in the EIM tables
- EIM updates other tables with rows containing foreign keys that point to newly deleted rows.
Deletion Methods 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:
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.
3. Delete all rows in the base table regardless of EIM table row contents or configuration file WHERE clause expressions.
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:
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.
Common Header and Process Section Parameters:
CASCADE DELETE ONLY:
- Default is FALSE
- Set this parameter to TRUE to delete child records with nullable 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.
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 reimported 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 reimported using the EIM table rows written by the EIM delete process, because they will not contain all the original information.
Deleting All Data Rows:
- If you want to delete all data rows in a target base table, you must perform the following procedure:
- To delete all rows in a target base table
- 1. Set the DELETE ALL ROWS parameter in the EIM configuration file to TRUE; its default value is FALSE. The following example contains lines that can be used in the EIM configuration file to delete all rows from the accounts table:
TYPE = DELETE
BATCH = 200
TABLE = EIM_ACCOUNT
DELETE ALL ROWS = TRUE
Deleting Rows from Extension Tables:
- You cannot delete a row from one-to-one extension tables (*_X type) without removing its parent row.
- For example, to remove a row from S_CONTACT_X, you must drop the parent row from S_CONTACT.
- If you have to delete data in an extension column, update it with NULL by setting NET CHANGE = FALSE in the configuration file, and if necessary, use ONLY BASE COLUMNS.
Deleting File Attachments:
- In order to delete file attachments, EIM deletes the row pointing to the file attachment.
- After all file attachments have been deleted, use the Siebel File System Maintenance Utility named sfscleanup.exe during hours when the network is least laden to clean the file attachment directory of any unused file attachments.
- To delete file attachments
2. After all file attachments have been deleted, run the Siebel File System Maintenance Utility named sfscleanup.exe to clean up the file
attachment directory.
Handling Aborts of EIM Delete Processing:
- If an EIM delete process is aborted, base tables associated with deleted rows may not be updated.
- Orphans rows may be created because foreign keys may not have been updated. This may cause critical data integrity issues.
- To avoid this problem, you should set the following parameters in the .IFB file to make sure that the EIM delete process performs only one commit and rollback when aborted:
COMMIT EACH PASS = FALSE
ROLLBACK ON ERROR = TRUE