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 interface 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.
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
1 Populate the following columns in the IF table (such as user keys for the S_ORG_PROD table and all the special IF 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
2 Add or modify the following process section in your .ifb configuration 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
3 Run EIM.
This will delete all rows from the S_ORG_PROD table that have user keys that match the rows in your IF table.