Deleting Data

This chapter covers the process of deleting selected data from the Siebel database. This chapter is organized into the following sections:

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:
- 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
EIM updates other tables with rows containing foreign keys that point to newly deleted rows.

EIM provides comprehensive status information about each delete process. When the process ends, you should review this information. For further details, see the following.

The EIM delete function requires you to perform the following tasks:

The delete process performed by EIM is called a cascade delete. When a cascade delete is performed, all of the contents of a data structure, including all of its substructures, are deleted. In other words, the data deleted is not restricted to the base tables mapped to the EIM table that you specified in the delete process, but all child records as well. To delete data, EIM performs a sequence of tasks. Each task involves multiple passes; at least one pass is required for each EIM table included in the process. You should be very careful and specific when specifying delete criteria. For example, using the criteria “DELETE MATCHES = S_PARTY, (CREATED > xxxxx)” causes all records of S_PARTY that match this criteria to be deleted from the database.

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:

Delete rows in a Siebel base table with user key values specified in the corresponding EIM table.
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 all rows in the base table regardless of EIM table row contents or configuration file where clause expressions.

Caution: Do not use EIM to delete organizations. Using EIM to delete data from the Products base tables is also not recommended and can lead to inadvertent data integrity loss.

Delete Process Flow

Preparing for an EIM delete process requires a thorough understanding of the parameter settings that specify delete criteria. You should be very careful and specific when setting delete-criteria parameters to avoid unintentional data loss. The EIM parameters mentioned in the following process flow are discussed in depth in Parameters Used for Deletes in Both the Header and Process Sections.

To delete data, EIM performs the following steps.

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.
EIM deletes rows.
1. 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.
2. 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.
3. If the DELETE ALL ROWS parameter in the configuration file is set to TRUE, EIM deletes all rows from the target base table.
  For information on configuration file parameters to use in a delete process, see Parameters Used for Deletes in Both the Header and Process Sections.
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.
  
  Note: If the record to be deleted is a parent, the child records are affected as described in this procedure. However, if a non-required foreign key is part of the user key and clearing it will create a conflict, then the record will be deleted.
- 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.
Note: Because the delete process affects the contents of base tables, transaction logging should be in effect during delete operations if you have active mobile Web clients, so that the appropriate transactions are captured for later docking.

Preparing the EIM Tables for Delete Processing

This section provides assistance in loading the EIM tables with data used to control deletion of rows from Siebel base tables.

You must make sure that each EIM table row to be processed contains both data that correctly identifies the exact base table rows to delete and the appropriate values in the following columns.

ROW_ID. This value in combination with the nonempty contents of IF_ROW_BATCH_NUM must yield a unique value.

IF_ROW_BATCH_NUM. Set this to an identifying number for all EIM table rows to be processed as a batch.

IF_ROW_STAT. In each row to be deleted, set this column to FOR_DELETE to indicate that the row has not been deleted. After processing, if certain rows were not deleted due to a data error:

Change the IF_ROW_BATCH_NUM value for the rows that require redeleting.
Change the BATCH NUMBER line in the configuration file.

It is not possible to delete 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 these rows. In such case, the IF_ROW_STAT field of the row in the EIM table will be marked as AMBIGUOUS.

Note: When you are deleting records based on user keys, specify the parameter DELETE EXACT in the .IFB file.

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 Delete Processing

This section describes the header and process sections that you need in the EIM configuration file to properly configure EIM for a delete process. It also discusses the parameters in the configuration file that must be adjusted for the delete process. For general information on the EIM configuration file, see EIM Configuration File.

Before delete 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 for the following purposes:

Header Section Parameters Used for Deletes

Parameters in the header section generally apply to all types of processes. For a description of the necessary contents in the header section, see Header Section Parameters Generic to All EIM Processes.

Process Section Parameters Used for Deletes

Parameters in the process section apply only to that specific process and override any corresponding value in the header section for the specific process. This section describes the parameters used in the process section that are specific to a delete process. For generic parameters that can be used in all EIM processes, see Process Section Parameters Generic to All EIM Processes.

To delete data, you must define at least one process with TYPE = DELETE.

If the process is defined with TYPE = DELETE, the DELETE ROWS parameter will be automatically set to TRUE. In some cases, you may not want to delete data from a nontarget base table as a result of cascade action. In this case, use the DELETE ROWS parameter to prevent deletion of rows from a specified table. The following example contains lines that can be used in the EIM configuration file to define a delete process for the accounts table while preventing rows from being deleted in the S_ADDR_ORG table.

[Delete Accounts]
  TYPE = DELETE
  BATCH = 200
  TABLE = EIM_ACCOUNT
  DELETE ROWS = S_ADDR_ORG, FALSE
  DELETE EXACT = TRUE
  ONLY BASE TABLES = S_ORG_EXT

Parameters Used for Deletes in Both the Header and Process Sections

This section describes the parameters that can appear in either the header section or a process section and are specific to a delete process. For generic parameters that can be used in all EIM processes, see Header Section Parameters Generic to All EIM Processes and Process Section Parameters Generic to All EIM Processes.

The following table provides descriptions of the parameters that can appear in the header and process sections of the EIM configuration file, and which are specific to delete processes.

Parameter	Description
CASCADE DELETE ONLY	(Default = 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	This parameter 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. Note: Use this parameter with caution. For more information on this parameter, see DELETE ALL ROWS Parameter.
DELETE EXACT	Delete using user key matching algorithm with rows in EIM table; default is FALSE. For more information on this parameter, see DELETE EXACT Parameter.
DELETE SKIP PRIMARY	This parameter specifies whether EIM should perform a cascade update to the primary child column. The default value is TRUE.
DELETE MATCHES	SQL WHERE fragment deletion criteria. Example: DELETE MATCHES = EIM_ACCOUNT, (NAME LIKE “TST_ACCT%”).
DELETE ROWS	This parameter specifies whether rows from the target base table can be deleted. Valid values are TRUE (the default) and FALSE. This parameter can prevent 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` Note: Use the FALSE setting for DELETE ROWS carefully. Inappropriate use can result in dangling foreign key pointers.
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. Use this parameter to improve 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. The default value is TRUE, which affects all tables. To affect only specific tables, you can specify a table name. For example: `UPDATE ROWS = S_CONTACT, TRUE` The UPDATE ROWS parameter 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. Note: Use the FALSE setting for UPDATE ROWS carefully. Inappropriate use can result in dangling foreign key pointers.

Note: You must use one of the following delete parameters described in this section: DELETE EXACT, DELETE MATCHES, or DELETE ALL ROWS.

DELETE EXACT Parameter

This 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.

Note: Do not use ONLY BASE TABLES with the target base table and nontarget base tables, because the EIM table record cannot specify just one record to be deleted.

Although this parameter can be used to delete rows from both target and nontarget base tables, use the delete exact parameter to delete only nontarget base tables containing user keys. Rows in nontarget 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 nontarget 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.

Deleting from Base Tables Other Than the Target Base Table explains how to delete data from base tables other than the target base table using the DELETE EXACT parameter with the following scenario as an example. In this example, EIM_ACCOUNT is mapped to base tables including S_ORG_EXT, S_ORG_PROD, and S_ORG_INDUST. You should delete data only from S_ORG_PROD, and you should not delete data from S_ORG_EXT or any other base tables.

DELETE MATCHES Parameter

This 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 nontarget 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.

Caution: Do not use the DELETE MATCHES parameter to delete rows from S_PARTY based tables. For example, using the criteria "DELETE MATCHES = S_PARTY, (CREATED > xxxxx)" will cause all records of S_PARTY that matches this criteria to be deleted from the database.

DELETE ALL ROWS Parameter

This parameter specifies that all rows in the target base table are to be deleted. Valid values are true and false (the default). 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 nontarget 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.

Caution: Use the DELETE ALL ROWS = TRUE setting with extreme caution. It will delete all rows in the named base table including any seed data. Do not remove unnecessary seed data by deleting all rows from the S_LST_OF_VAL base table. If you do so, you will not be able to reimport “clean” data and you will be forced to rebuild the seed data or restore from backup. To selectively delete rows, use the DELETE EXACT or DELETE MATCHES expressions.

Deleting All Data Rows

If you want to delete all data rows in a target base table, you must perform the following procedure. Typically, this would only be performed in a test environment.

To delete all rows in a target base table

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:

[Delete Accounts]
  TYPE = DELETE
  BATCH = 200
  TABLE = EIM_ACCOUNT
  DELETE ALL ROWS = TRUE

Caution: Use the DELETE ALL ROWS = TRUE setting with extreme caution. It will indeed delete all rows in the target base table.

Deleting Data Rows Identified by User Key Values

You must complete the following procedure to delete rows identified by user key values.

To delete rows with user key values appearing in the EIM tables

Set the DELETE EXACT parameter in the EIM configuration file to TRUE; its default value is FALSE.
Add the ONLY BASE TABLES parameter and set this parameter to the name of the base table you want to delete.

The following example contains lines that can be used in the EIM configuration file to delete rows with user key values in the EIM tables from the Accounts table:

  TYPE = DELETE
  BATCH = 200
  TABLE = EIM_ACCOUNT
  DELETE EXACT = TRUE
  ONLY BASE TABLES = S_ACCNT_POSTN

Note: Although you can use the DELETE EXACT parameter to delete rows from both target and nontarget base table, you should only use it to delete nontarget base tables that contain user keys. Rows in nontarget base tables that do not contain user keys will not be deleted.

Rows from the following tables do not have primary user keys and thus cannot be deleted using this parameter:

Notes
Territory Items
Fulfillment Items

Deleting from Base Tables Other Than the Target Base Table

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 that are 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.

The following procedure explains how to delete data from base tables other than the target base table using the DELETE EXACT parameter with the following scenario as an example. In this example, EIM_ACCOUNT is mapped to base tables including S_ORG_EXT, S_ORG_PROD, and S_ORG_INDUST. If you want to delete data only from S_ORG_PROD, and not delete data from S_ORG_EXT or any other base tables, complete the following procedure.

To delete data from base tables other than the target base table

Populate the following columns in the EIM table (such as user keys for the S_ORG_PROD table and all the special interface columns):
- ACCNT_NAME
- ACCNT_LOC
- INS_PROD_NAME
- INS_PROD_VENDR
- INS_PROD_VENDR_LOC
- INS_DT, ROW_ID
- IF_ROW_BATCH_NUM
- IF_ROW_STAT
- ROW_ID
Add or modify the following process section in your .IFB file:
TYPE = DELETE
BATCH NUMBER = <number used to populate IF_ROW_BATCH_NUM column>
TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_ORG_PROD
DELETE EXACT=TRUE
Run EIM.
This deletes all rows from the S_ORG_PROD table that have user keys that match the rows in your EIM table.

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 remove 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

You can also delete file attachments that have previously been imported into the Siebel database.

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

Run an EIM delete process for all file attachments that you want to delete.
After all file attachments have been deleted, run the Siebel File System Maintenance Utility named sfscleanup.exe to clean up the file attachment directory.
For information on using sfscleanup.exe, see Siebel System Administration Guide.

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 TABLE = FALSE

COMMIT EACH PASS = FALSE

ROLLBACK ON ERROR = TRUE

Running a Delete Process

You may run a delete process after you have:

Identified the data for delete processing
Prepared the related EIM tables
Modified the EIM configuration file accordingly

Run the delete process by completing the procedures in Running EIM.

Checking Delete Results

When a delete process ends, you should carefully check the results to verify that data was successfully deleted. During each process, EIM writes comprehensive status and diagnostic information to several destinations.

EIM uses a special column named T_DELETED_ROW_ID in the EIM tables. EIM writes the ROW_ID of each deleted base table row to this column.

To view a list of deleted base table rows

Query the appropriate EIM table for rows whose IF_ROW_BATCH_NUM equals the batch number for the delete.
The value of T_DELETED_ROW_ID identifies deleted rows.

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.