Siebel EIM: Siebel EIM Made Easy

Click the download link below to download the complete document in PDF format.

Download Here

Chapter 1: Siebel EIM: An Overview

About Siebel Enterprise Integration Manager (EIM):

Siebel EIM is a server component in the Siebel EAI component group that transfers data between the Siebel database and other corporate data sources.
Exchange of information is accomplished through intermediary tables called EIM/Interface tables.
The EIM tables act as a staging area between the Siebel application database and other data sources.
EIM is the primary method of loading mass quantities of data into the Siebel database.
EIM can be used to perform bulk imports, updates, merges, and deletes of data.

How does EIM Works?

Siebel applications use Siebel Base tables for storing and retrieving data. The data that is to be imported can come from various sources like legacy database, external database etc.
The external data cannot be directly imported into siebel base tables. This is due to the reason that database structure of the external databases, their formats, schema etc would differ from siebel database structures. In fact Siebel does not allow this.
For the consistency of data and load process, the external data first stages into the EIM tables and then subsequently loaded into siebel tables through EIM jobs.
So the data exchanges between the Siebel database and external databases occur in two parts:

1. Load data into EIM tables.

2. Run Siebel EIM jobs to import the data from the EIM tables into the Siebel base tables.

It is worthy to note that the first part of this data-exchange process involves only the EIM tables from siebel side, only the second part of the process involves the functionality of Siebel EIM.
Also, EIM should be used to perform bulk imports, exports, merges, and deletes, instead of direct SQL. Oracle does not allow this.

EIM Process/Functions:

There are 4 different process/functions in Siebel EIM namely:

1. Import

2. Delete

3. Merge

4. Export

For each EIM process, you need to complete the following sequence of steps.

1. Prepare the EIM tables:

For delete, merge, or import operations, the EIM tables require loading with representative data that allows EIM to identify the specific Siebel base table on which to operate.
You can use either an SQL program utility or native SQL to perform this function.
The EIM export processes require minimal preparation of the EIM tables.
When an export operation takes place, the EIM tables are populated with data from the Siebel base tables. Therefore, you can use either an SQL program or native SQL to transfer data from the Siebel application to a non-Siebel application.

2. Edit the EIM configuration file:

An ASCII or Unicode (binary) text file of extension type .IFB that resides in the Siebel Server/admin directory allows you to define the type of EIM processes to be performed: export, delete, merge, or import.

3. Run EIM:

EIM is submitted as a Siebel Server batch component task (EIM job) either from the UI i.e. Administration - Server Management views or from the Server Manager command line interface.

4. Check results:

The EIM component task produces a log file, which provides tracing information about the process.
The tracing information produced is variable dependent upon the EIM component task parameters used and the Siebel Server event logging deployed for the EIM component.
As always, during testing operations you should check the EIM processes using increased tracing information, and then reduce tracing when the process is deployed to production.

Chapter 2: Siebel EIM Tables

EIM Tables Overview:

Siebel EIM tables are intermediate database tables that act as a staging area between the base tables in the Siebel database and other databases.
EIM tables are designed to be simple and straightforward so they can be loaded or read by way of external programs.
Before EIM can be used in a merge, update, or import process, EIM tables must be populated with data, using any method supported by the database.
Once EIM tables are populated, EIM job can be invoked to process this data.
Each EIM table usually supports a group of base tables that can be imported or exported in a single batch.

EIM Table Columns:

In EIM tables, several columns are mandatory, others are conditionally mandatory, depending on the conditions of your import.

ROW_ID:

For an EIM table row to be eligible for processing, you must initialize its ROW_ID.
The ROW_ID, in combination with the value of IF_ROW_BATCH_NUM, must yield a unique value.
The ROW_ID values in the EIM tables are not the ROW_ID values that are assigned to the row when it is loaded into the base table.
An EIM-generated ROW_ID has a XX-XXX-XXX format.
A regular Siebel row ID that is assigned to the row has a X-XX format.

IF_ROW_BATCH_NUM:

You must set the values in this column to the same integer, greater than or equal to 0, as an identifying number for all rows to be processed as a batch.
The maximum value is 2147483647.
Use this column as the first key of any new indexes created on an EIM table.

IF_ROW_MERGE_ID:

You can set this column to one of two values:
NULL- This value identifies the surviving or merged-into-row.
ROW_ID- This value identifies the ROW_ID number in the EIM table where the row will be merged.

IF_ROW_STAT:

EIM updates this column after processing the row to indicate the status of the record.
When populating the EIM tables, you can set this column to any value except NULL. E.g. FOR_IMPORT, indicating that the row has not been processed yet.
After processing, if certain rows were not imported due to a data error, you should change:

IF_ROW_STAT_NUM:

After processing, this column contains a zero (0) if a row was successfully processed to completion.
If processing failed, this column contains the pass number where the pass failed.

Temporary columns:

EIM uses temporary columns to manipulate data during processing.
These column names begin with T_ and indicate the table or column for which they are used.
Because EIM uses these columns internally during processing, do not manipulate these columns in the EIM tables.

EIM Table and Column Mappings:

EIM uses EIM table mappings to map columns from EIM tables to Siebel base tables.
Siebel predefined EIM mappings are fixed and cannot be remapped.
Some base tables may not be mapped to a corresponding EIM table. In such case, EIM Table Mapping Wizard can be used to add missing mappings.

Creating New EIM Table Mappings to Existing Base Tables:

You can create new EIM table mappings from an EIM table into a base table if either of the following conditions is true:

1. Mappings already exist from the EIM table to the base table.

2. The base table is an extension table and mappings already exist from the EIM table to the corresponding base table.

For example, you could create a new column in EIM_ACCNT_DTL and map this either to a new extension column in S_ORG_EXT or to an existing column in the extension table S_ORG_EXT_X.

If you create an extension column to a base table, then run the EIM Table Mapping Wizard, the Wizard creates the following mappings:

1. The mapping for the newly added extension column

2. The mappings for all unmapped columns in the base table, including unmapped Siebel base columns.

Explicit Primary Mappings:

The Siebel Data Model uses primary foreign keys (called as primaries) to point from a parent base table to a child base table.
Primaries enable business logic in the Siebel Data Model, such as identifying the primary position for an account.
Primaries improve performance by eliminating repeating subqueries when data from both the parent table and the primary child table are displayed.
Primary foreign keys are columns that have names usually beginning with PR_ and are defined as primaries in the data model.
If both the parent table and the primary child table of a primary foreign key are mapped to the same EIM table, then you should see an explicit primary mapping for this primary foreign key under the table mapping of the primary child table.
E.g. Parent Table: S_PROD_INT Child Table: S_PROD_LN_PROD Explicit Primary Mapping: S_PROD_INT.PR_PROD_LN_ID (Product Line Id)

How to check which Base tables are mapped to a particular EIM table?

Start Siebel Tools.
In the Object Explorer, click the Types tab.
Click EIM Interface Table.
In the EIM Tables window, select the EIM table for which you want to view the mappings.
In the Object Explorer, expand EIM Interface Table.
Click EIM Table Mapping.

The EIM Table Mappings window displays all base table mappings for the selected EIM table. Expand EIM Table Mapping and click on Attribute Mappings to find the base table column mappings to EIM table columns.

How to check which EIM tables can populate a particular base table?

Start Siebel Tools.
In the Object Explorer, click the Flat tab.
Click EIM Interface Table.
Query for the base table in the Target Table properties.
The Name property will give the list of all EIM tables that can populate the base table.

EIM Table Mappings to Base Tables without User Keys & Related Topics:

Some EIM tables contain table mappings to base tables not having any user keys. Most of them are those starting with S_NOTE*

Things to keep in mind when performing EIM processes involving base tables without user keys:

IMPORT (Insert): Import works but EIM does not check and prevent duplicate records from being imported into the base tables without user keys.
If an import batch is executed repeatedly, the same records will get imported repeatedly because EIM cannot check whether the records to be imported already exist in the base table without user keys.
IMPORT (Update): Update on base tables without user keys cannot work, because EIM cannot uniquely identify the record to update.
EXPORT: Exporting data from base tables without user keys is done the same way as exporting data from base tables with user keys.
DELETE: DELETE ALL ROWS and DELETE MATCHES can be used to delete data in target base tables.
If a table without a user key is the target table, then delete works as it does for base tables with user keys.
In most cases, however, a table without a user key is a secondary table and its data can only be deleted with the table as a child of its parent table.
MERGE: Merge does not work on base tables without user keys.

Chapter 3: EIM Configuration File

Using the EIM Configuration File to Define a Process:

EIM reads a configuration file that specifies the EIM process (import, update, merge, delete, or export) to perform using the appropriate parameters.
The EIM configuration file is an ASCII text file of extension type .IFB that resides in the Siebel Server/admin directory.
Before running the EIM process, ifb file needs to be changes as per the requirement of the load.
EIM accepts parameter values from three sources:

1. The command line entered by the user who invokes the EIM process

2. The Siebel Server Manager GUI

3. The configuration file specified, or default.ifb if none is specified

Parameter value searches are performed according to a specific hierarchy: command line, component parameter, and configuration file.
Command-line parameters thus override component parameters, and component parameters override configuration file parameters.

Defining EIM Configuration File Parameters:

Header section: Specifies global parameters that apply to all process sections.
Process Section: At least one process is required.

Header Section Parameters and their descriptions:

CONNECT: The ODBC source name for connecting to the database server.

LOG TRANSACTIONS TO FILE: Default is True. Transactions can be logged in a file or a table. By default, EIM logs transactions into files. Log files are saved in the file system’s EIM directory. If you do not want transactions to be logged in files, then setting this parameter to FALSE logs transactions to a table.

PASSWORD: Database password. This parameter is inherited for the EIM component from the Gateway Name Server, so it should already be set. However, you can specify this in the IFB file if you are running EIM from the Siebel application (not the command line) and if you have not already set this value in the EIM Server Component parameters.

PROCESS: Identifies the specific process to run during this invocation of EIM. The named process must be defined in the process section of the IFB file.

[Siebel Interface Manager]: Header section must use this reserved name.

TABLEOWNER: The database logon name that owns the tables to be operated on, used as the prefix for table names, defined during installation (generally SIEBEL).

USERNAME: Database/employee logon name. This parameter is inherited for the EIM component from the Gateway Name Server, so it should already be set. However, you can specify this in the .IFB file if you are running EIM from the Siebel application (not the command line), and if you have not already set this value in the EIM Server Component parameters.

Process Section Parameters and their descriptions:

BATCH: Required, Specifies a required batch number for the process to be run, gets stamped in IF_ROW_BATCH_NUM column, must be a positive integer between 0 and 2147483647. E.g. BATCH = 100-110 or BATCH = 100,103,106,110

COMMIT EACH PASS: Optional, Commit after each EIM pass, Default is True.

COMMIT EACH TABLE: Optional, Commit after each EIM pass, Default is True.

IGNORE BASE TABLES: Optional, Do not process these tables.

INCLUDE: Optional, Sub process to execute. INCLUDE names a process to be included as part of this process. More than one process may be included in another process. All included processes execute before the process itself.

LOG TRANSACTIONS: Optional, Default value depends on system preference, controls the logging mode, if true EIM logs changes when mobile clients synchronize. If set to FALSE, changes are not logged. LOG TRANSACTIONS = TRUE operates in row-by-row mode. LOG TRANSACTIONS = FALSE operates in set-based mode.

ONLY BASE TABLES: Optional, Process only base tables.

ROLLBACK ON ERROR: Optional, Error rollback behavior, default is FALSE.

SESSION SQL: Optional, Specifies a user-defined SQL statement to be sent to the database server before other SQL statements for this process. This string is sent directly to the database and must be a single SQL statement suitable for immediate processing. Only one SESSION SQL parameter can be used in each process section.

SKIP BU_ID DEFAULT: Optional, Specifies whether the virtual null key is to be skipped for the BU_ID column. The default value is FALSE.

TABLE: Required, Specifies the name of an EIM table used in this process. Multiple TABLE parameters may be used to define a process using more than one table.

TRANSACTION SQL: Optional, Post-commit SQL statement. Specifies a user-defined SQL statement to be sent to the database before other SQL statements, and immediately after each commit or rollback operation during the process.

TYPE: Required, specifies the type of process being defined (IMPORT, EXPORT, DELETE, MERGE, SHELL).

USE ESSENTIAL INDEX HINTS: Optional, For MS SQL Server and Oracle databases only, default is TRUE, enables a subset of index hints for MS SQL Server.

USE INDEX HINTS: Optional, For Oracle databases only. Controls whether EIM issues optimizer hints to the underlying database to improve performance and throughput, default is FALSE.

USING SYNONYMS: Optional, Controls the queries of account synonyms during import processing. When set to FALSE, this parameter saves processing time because queries that look up synonyms are not used, default is TRUE.

Important points to remember while setting EIM IFB parameters:

Lines in the default.ifb file that begin with a semicolon (;) are comment lines and are ignored.
For multiline parameters definition, make certain that the backslash character (\) is the last character on the line.
The backslash character denotes continuation.
Do not combine comments (;) with new lines (/) because this format creates difficulties finding a comment in the middle of a line.
If multiple lines have the backslash character (\) at the end, this means they are a single parameter line. So, if a semi-colon (comment character) is placed among these lines, EIM ignores the column with the semi-colon and all columns linked through the continuation character.
PASSWORD and USERNAME values are generally not used for access authentication or as a security measure. EIM acquires access authentication from the component parameters.
PASSWORD and USERNAME values in the .IFB file are only used if the parameters are not set at the enterprise or component level.

Chapter 4: Import Data

11 Steps in EIM Import Process:

1. EIM initializes any temporary columns:

It compares values in IF_ROW_BATCH_NUM with the batch number provided by the Component task that initiated this import process.
It sets all temporary columns to NULL and counts the rows to be processed.
If there are rows where required columns contain only blanks, the complete EIM process will fail at this step. Rows will not be imported or updated.

2. EIM applies any DEFAULT_COLUMN and FIXED_COLUMN values defined for this import process.

3. EIM applies any filter queries defined for this import process. If a row fails the filter query, EIM eliminates the row from further processing.

4. EIM generates foreign key references for rows with corresponding existing rows in the Siebel base tables.

It writes these foreign key values into EIM table temporary columns.
If foreign keys fail for required columns, EIM eliminates these rows from further processing.
It also validates bounded picklist values against the List of Values table (S_LST_OF_VAL).

5. EIM writes the appropriate ROW_ID values in the EIM table rows’ temporary columns, for rows with corresponding base table rows.

6. EIM creates a ROW_ID with a unique value in the base table for each EIM table row without a corresponding row in the base tables.

7. EIM eliminates rows with invalid values for user keys from further processing. It then generates foreign key references for rows without corresponding rows in the Siebel database tables, and writes these foreign key values into EIM table temporary columns:

If foreign keys fail for required columns, EIM eliminates these rows from further processing.
For EIM table rows with data that will reside in multiple destination tables, EIM fails rows with foreign keys that cannot be generated.

8. EIM updates contents of existing base table rows with contents from corresponding EIM table rows that have successfully passed all earlier steps:

If any rows contain content that differs from the existing base table row, EIM writes these rows to the Master Transaction Log (if Enable Transaction Logging is enabled).
If multiple EIM table rows have the same user primary key for a base table, EIM uses only the first EIM table row to update the base table, and ignores the data in other rows.

9. EIM inserts any new EIM table rows that have successfully passed all earlier steps in the Siebel database tables:

It writes new rows to the Master Transaction Log (if Enable Transaction Logging is enabled).
If multiple EIM table rows use the same user primary key for a base table, EIM uses only the first EIM table row to update the base table, and ignores the data in other rows.

10. EIM updates primary child relationships in the Siebel database tables as necessary. EIM populates all primary child columns with Primary Child Col property set to TRUE.

11. Finally, EIM runs optional miscellaneous SQL statements.

Import Data Process Flow:

1. Identify and validate the data to be imported. To perform this task, you must:

Determine the data to load and whether it already exists in another database. You should review existing data for completeness. For example, the Siebel database may require both an area code and a telephone number, while your existing database may not.
Determine the number of opportunities, contacts, and accounts you plan to import. This information assists you in estimating the time and resources required to import, process, and store your data.

2. Identify the column mappings and user key columns of the data to be imported. To perform this task, you must:

Identify the mapping between the data and Siebel base columns.
Identify the EIM table columns that map to these base table columns.
Identify the user key columns and make sure they are populated uniquely.

3. Make sure that your hardware and software environments are ready. Before you use Siebel EIM tables to import data, the Siebel application must be properly installed. Work with your Siebel representative and MIS personnel to verify that the required hardware and software resources are available.

4. Back up your existing database. Before undertaking any significant change such as installing a new application, importing data, or upgrading an installed application, you should first perform a comprehensive backup of your database. This facilitates an easy recovery if problems occur.

5. Copy file attachments to the Siebel Server subdirectory named “input.” If you want to import file attachments, you can:

Copy the files to the input subdirectory under the Siebel Server root directory.
Store files attachments in the location specified in the ATTACHMENT DIRECTORY .IFB file header parameter.
Siebel EIM tables support all file attachment formats, including common file types such as Word documents (.doc), Excel spreadsheets (.xls), and text files (.txt).

6. Load and verify the EIM tables.

After the EIM tables are loaded, check the number of loaded rows against your existing database to make sure that the appropriate rows were loaded.
Check the contents of several rows to make sure that the tables are ready for the import process.

7. Edit the EIM configuration file. This file customizes the behavior of EIM by defining the data you will import and identifying the batch number to use.

8. Test your import process. Run a small test batch (perhaps 100 records) to verify that the EIM tables load correctly, and that the correct parameters are set in the configuration file and on the srvrmgr command line.

9. Run the import process.

Although your batch sizes depend on the volume of data you must import, consider using multiple smaller batches (1,000 to 5,000 rows) rather than one large batch.
Smaller batches place fewer demands on resources.
Also, when using smaller batches, the fixing of problems is simpler. If a batch is not imported correctly, it is easier to isolate the condition, correct it, and rerun the batch.

10. Verify results.

Data Load Hierarchy for Siebel Entities:

To make sure that the necessary data is present to establish relationships between data entities, use the following sequence to import data:

1. Administrative

2. Business Unit

3. Positions

4. Accounts

5. Contacts

6. Employees

7. Products

8. Opportunities

9. Personal Accounts

10. Quotes

11. Documents

12. Forecasts

13. Fulfillment

14. Marketing Campaigns

15. CPG Promotion Management

16. CPG Product Movement

17. Service Requests

18. Product Defects

19. Activities and Appointments

20. Notes

21. File Attachments

This import order reflects most import processes. In some cases, the import order for your import process may vary slightly depending on your requirements.
While the import order is most critical when performing the initial import of legacy data, this recommended order should be followed for all subsequent data imports as well.

Updating the Siebel Database:

After you have completed the initial import of enterprise data, you can periodically use EIM to update the Siebel database.
By default, when importing information, EIM performs both inserts and updates based on the content of the batch set. EIM first examines the set of information to determine which rows in the batch already exist in the Siebel database:
Batch rows matching existing base rows are used to update the database.
Batch rows that do not match base rows are used to perform inserts.
You may need to update the Siebel database with a batch that contains a record to be inserted as well as an update to that same row. When you use EIM to do this, a record will be inserted, but the update will be flagged as a duplicate.
EIM processes a record once for each batch, so for each record, MIN(ROW_ID) is processed, and the other record is marked as a duplicate (IF_ROW_STAT is set to DUP_RECORD_IN_EIM_TBL for the duplicate record). If you enter the user key of a record with different attributes twice in the EIM table, only the record with the MIN(ROW_ID) will be imported or updated. The duplicate will be ignored.
You cannot update system fields. All Siebel system fields are fields reserved only for use by Oracle for internal Siebel processes. They are not to be populated with customer data.

The following are reserved system fields that cannot be updated:

CONFLICT_ID

CREATED

CREATED_BY

LAST_UPD

LAST_UPD_BY

MODIFICATION_NUM

ROW_ID

DB_LAST_UPD

DB_LAST_UPD_SRC

Preparing the EIM Tables for Import Processing:

To import data, EIM reads data in the EIM tables and writes data in the appropriate Siebel base tables by making multiple passes through the EIM tables to:

Set initial values for some columns in the EIM tables
When importing new data, make sure to populate the columns marked required in the EIM table.
When updating existing records you do not need to populate the required columns, but the user key columns must be populated.
Apply filter logic to select rows for importing.
Generate foreign key references and internal values.
Add or update relevant Siebel database rows.
Update each EIM table row to indicate its import status.

Required Initial Values for Special 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 value to an identifying number for all rows to be processed as a batch.

IF_ROW_STAT: In each row to be imported, set this column to FOR_IMPORT to indicate that the row has not been imported.

Required Initial Values for File Attachment Columns:

FILE_NAME: Set this column to the root filename of the file attachment.

FILE_EXT: Set this column to the extension type of the file attachment (such as DOC, XLS, or TXT).

FILE_SRC_TYPE: This column must be set to FILE.

Editing the Configuration File for Import Processing:

Process Section:

COMMIT OPERATIONS: Docking Log row commit frequency; default is 0.

FILTER QUERY:

SQL preprocess filter query fragment. Example: FILTER QUERY=(ACCNT_NUM = "1500")
Runs before the import process.
Prescreens certain rows in the import batch, using data values in the EIM tables.
Rows that do not meet the filter criteria are eliminated.
Should be a self-contained WHERE clause expression (without the WHERE keyword) and should use only unqualified column names from the EIM table or literal values (such as name is not null).
By default, the FILTER QUERY parameter is not used.

IGNORE BASE COLUMNS:

Specifies base table columns to be ignored by the import process.
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.

IGNORE BASE TABLES:

Specifies base tables to be ignored by the import process.
Target tables for EIM tables cannot be ignored.
The default is to not ignore any base tables.
Improves performance when updating all but a few tables

ONLY BASE COLUMNS:

Specifies and restricts base table columns for the import process.
Include all user key columns and required columns.
Improves performance when updating many rows but few columns.
The default is to process all interface columns mapped to the base table.
Example: ONLY BASE COLUMNS = S_ORG_EXT.NAME, S_ORG_EXT.LOC, S_ORG_EXT.BU_Id

ONLY BASE TABLES:

Specifies and restricts selected base tables for the import process.
Target tables for EIM tables must be included.
The default is to process all base tables into rows that can be imported from the EIM tables.
Improves performance when updating only a few tables.
Example: ONLY BASE TABLES = S_CONTACT, S_ORG_EXT

UPDATE ROWS: Optional base table, TRUE/FALSE toggle, default is TRUE.

Common Header and Process Section Parameters:

ATTACHMENT DIRECTORY:

Default = SIEBEL_HOME\INPUT
Specifies the directory to be used for importing attachments.
Make sure the directory exists on a Siebel Server machine and you have read and write access to the directory.
Example: ATTACHMENT DIRECTORY = SIEBEL_HOME\INPUT

COMMIT EACH PASS:

Specifies whether a separate transaction should be used for each EIM pass through each EIM table.
DEFAULT is TRUE, which invokes commits after each pass.
Reduces the database resources required for the import process and provides a checkpoint to which you can return in the event of unexpected results.

COMMIT EACH TABLE:

Specifies whether a separate transaction should be used for each EIM table.
DEFAULT is TRUE, which invokes commits after each table.
Reduces the database resources required for the import process.

COMMIT OPERATIONS (IMPORT Only):

Specifies the number of insert and update operations to be performed before a commit is invoked.
Value is an integer > 0.
Prevents the transaction rollback space from overflowing when large data sets are imported.
The default for COMMIT OPERATIONS is not set; a commit is thus invoked only at the end of the import by default.
This setting is ignored if you have turned off Enable Transaction Logging.

DEFAULT COLUMN (IMPORT Only):

Specifies a default value for an EIM table column.
Example: DEFAULT COLUMN = CURCY_CD , "USD"
The given value will be used only if the column is null in the EIM table.

FIXED COLUMN (IMPORT Only):

Specifies the value for an EIM table column.
Example: FIXED COLUMN = ORG_CD, "Commercial"
The given value will be loaded into the Siebel base table, overriding the value in the EIM table column.

INSERT ROWS:

Specifies that nonexistent rows in the EIM table be inserted into the Siebel base table.
DEFAULT is TRUE.
Example: INSERT ROWS = EIM_ACCOUNT, FALSE
If the named table is an EIM table, as in the example, the setting applies to all Siebel base tables imported from this EIM table. If the named table is a Siebel base table, the setting is applied when data is imported from any EIM table.

MISC SQL:

Sets specific explicit or implicit primaries.
Explicit is when you have specific values to set as primaries.
Implicit is when any of a group of values is acceptable.
For example, you are importing one account with nine addresses. If any of the addresses is acceptable as being the primary, then set primary to implicit. EIM then selects one of the addresses as primary. If a specific address should be the primary, then set primary to explicit and indicate the primary account by setting its flag column (EIM_ACCOUNT.ACC_PR_ADDR) to Y.

NET CHANGE (IMPORT Only):

Specifies the handling of null (non-user key) column values when importing a row that already exists in the Siebel database table.
If NET CHANGE = TRUE, the null value will be ignored; otherwise, the column in the base table will be updated with NULL.
Ignored if UPDATE ROWS = FALSE.
DEFAULT is TRUE

ROLLBACK ON ERROR:

Specifies whether the current transaction should be rolled back (aborted) when an error, such as an SQL database failure, is encountered.
DEFAULT is FALSE.
If you set this parameter to TRUE, you should also set COMMIT EACH PASS and COMMIT EACH TABLE to FALSE, and make sure that the database transaction space is large.

TRIM SPACES (IMPORT Only):

Specifies whether the character columns in the EIM tables should have trailing spaces removed before importing.
DEFAULT is TRUE.

NET CHANGE:

By default, EIM does not update non-user key columns, i.e., columns with a null value.
The NET CHANGE parameter specifies the handling of null (non-user key) column values when importing a row that already exists in the Siebel database table.
If NET CHANGE = TRUE, the null value will be ignored.
If NET CHANGE = FALSE, the column in the base table will be updated with NULL.

Effect of NET CHANGE = FALSE on IF_ROW_STAT:

When NET CHANGE = FALSE, there are 3 possible outcomes:

For a null value, EIM updates the base table column to NULL and sets the EIM table’s IF_ROW_STAT to IMPORTED.
For a non-null value that is a duplicate, nothing is done to the base table column and the EIM table’s IF_ROW_STAT is set to DUP_RECORD_EXISTS.
For a non-null value that is not a duplicate, EIM updates the base table column with the value in the EIM table and sets IF_ROW_STAT to IMPORTED.
EIM only updates the non-user key columns with NULL if you set the NET CHANGE parameter to FALSE.

Also note that when EIM updates non-user key columns with NULL for the columns that had a non-null value beforehand, then the status of IF_ROW_STAT becomes IMPORTED. This is because EIM has performed the update transaction for this table.
The second case mentioned above shows, however, that if a column had a null value beforehand, and EIM has performed the update with all the same records (including this NULL column), then in effect, EIM has ignored this null value and has not performed an update transaction for this NULL column (regardless of whether NET CHANGE is set to FALSE). So in this case, EIM populates IF_ROW_STAT with DUP_RECORD_EXISTS.
If in cases like this you want to update certain columns with NULL, then you can specify the ONLY BASE COLUMNS parameter in the .IFB file.

MISC SQL:

See Bookshelf for list of EIM tables that can be used with the MISC SQL parameter, as well as the values.
The table lists the values of the MISC SQL parameter when you want to set a field
For Explicit Promary mapping, use EXPR (EXplicit PRimary) and for Implicit Primary mapping use IMPR (IMplicit PRimary).
Syntax: MISC SQL = EXPR_S_CONTACT_PR_OU_ADDR_ID, MISC SQL = IMPR_S_CONTACT_PR_OU_ADDR_ID
The most flexible method is to use explicit primaries on the records for which you have specified a primary, and to automatically use implicit primaries on the records where you have not specified a primary. The following example shows this syntax: MISC SQL = EXPR_S_CONTACT_PR_OU_ADDR_ID, IMPR_S_CONTACT_PR_OU_ADDR_ID

Importing Party Records:

The PARTY_TYPE_CD column can have the following values:

Person: Contact, User, Employee or Partner

Organization: Organization, Division, or Account

Household: Household

Position: Internal Division Position

AccessGroup: Bundling of Party entities

UserList: Siebel Persons as its members

PARTY_UID:

PARTY_UID is populated by default through the Siebel upgrade process and the application UI with the ROW_ID of the party record that is being created.
The value does not have to remain identical with the ROW_ID.
With EIM, the PARTY_UID gets populated with the value specified in the EIM table for this column.
PARTY_UID may have a calculated value with logic, such as a combination of email and other data. For this reason, PARTY_UID is defined as VARCHAR100.

ROOT_PARTY_FLG:

ROOT_PARTY_FLG supports performance for Oracle.
The following are possible queries to retrieve top-level Positions, Organizations, or Access Groups.

WHERE ROOT_PARTY_FLG = 'Y', ROOT_PARTY_FLG is set to 'Y' for top-level Positions, Organizations, and Access Groups as it applies only to these party subtypes. It is set to 'N' for other party subtypes.
WHERE PAR_PARTY_ID IS NULL, Oracle cannot use an indexed access path because there are no index entries for NULL, so ROOT_PARTY_FLG was added.

IF_ROW_STAT values after Import:

AMBIGUOUS:

There are two rows in the base table that have the same user key but different conflict IDs.
EIM cannot distinguish these rows.

DUP_RECORD_EXISTS:

The row exactly matches rows that already exist in the destination tables.
Note that a row may have a duplicate in the target base table, but not in other destination base tables. In this situation, EIM adds the new relation (a child or intersection table) in the other destination base tables, and does not mark the EIM table row as a duplicate.

DUP_RECORD_IN_EIM_TBL:

The row was eliminated because it is a duplicate (has the same user key) of another row in the EIM table with the same batch number.
MIN(ROW_ID) is the record processed, and the other records with the same user key are marked as DUP_RECORD_IN_EIM_TBL.
DUP_RECORD_EXISTS status indicates that the same record already exists in the base table, while DUP_RECORD_IN_EIM_TBL status indicates that there are two or more EIM table records having the same user key values.

FOREIGN_KEY: A required foreign key column in the target table could not be resolved.

IMPORTED:

The row was successfully processed against all its destination base tables.
This status is set after the import has been completed.

IMPORT_REJECTED: A user-specified filter query failed for this row.

IN_PROGRESS:

EIM sets IF_ROW_STAT to this initial value for all rows in the batch.
If rows still have this status value after EIM exits, a failure occurred that aborted processing for this table.

NON_UNIQUE_UKEYS: The user key was not unique in all the user key specifications on the table.

PARTIALLY_IMPORTED: The row did not fail for the target table (although it may have been a duplicate), but did fail during processing of a secondary base table.

PICKLIST_VALUE:

A required picklist value in the target table could not be resolved.
This error occurs for NULL or invalid bounded picklist values.

REQUIRED_COLS:

One or more required columns for the target table were NULL.
This error occurs for missing user key columns or when inserting new rows.

ROLLBACK:

EIM encountered an error, such as an SQL database failure, and rolled back the transaction.
This status is only used when ROLLBACK ON ERROR = TRUE.

SQL_ERROR:

An SQL error occurred during an attempt to import this row.
This error occurs for rows processed when Enable Transaction Logging is set to TRUE.

Chapter 5: Export Data

EIM Export - Overview:

To export data, EIM reads the data in the Siebel database tables and places the information in the appropriate EIM tables.
The export process generally populates the applicable EIM table with a row for every Siebel base table row encountered.
As a consequence, where EIM tables have mappings to multiple Siebel base tables, one export operation can generate multiple rows within the EIM table governing the rows encountered within the Siebel base tables.
During its multiple passes through the EIM tables, EIM performs the following tasks:

1. EIM initializes the EIM tables for export.

2. It applies filter logic to select rows for exporting.

3. EIM updates EIM table rows to indicate the export status.

EIM Export Process:

To export data to EIM tables, EIM performs the following steps:

1. EIM initializes EIM tables for export:

If CLEAR INTERFACE TABLE in the configuration file is TRUE, all rows with the specified batch number are deleted. Otherwise, a warning is issued if rows already exist with the specified batch number.

2. It uses export parameter expressions in the configuration file to locate and export table rows:

If EXPORT ALL ROWS is TRUE, ignore any EXPORT MATCHES parameters and export all rows.
If EXPORT ALL ROWS is FALSE, use EXPORT MATCHES parameters to locate specific rows.

For parent tables, EIM locates child table rows and exports them to their corresponding EIM tables.

Preparing the EIM Tables for Export Processing:

Requires minimal preparation of the EIM tables.
EIM inspects each EIM table involved in the process. If EIM finds a row whose IF_ROW_BATCH_NUM matches the batch number for this export process, it does one of the following:

Clear the row if the CLEAR INTERFACE TABLES parameter is set to TRUE in the EIM configuration file.
Issue a warning if the CLEAR INTERFACE TABLES parameter is set to FALSE in the EIM configuration file.

Checking Existing Rows Batch Numbers:

Before you initiate an export process, you should verify that rows do not contain an IF_ROW_BATCH_NUM matching the batch number you plan to use.
If such rows do exist, you should either make sure that they do not contain data you need to preserve, or change the batch number for the export process.
In each row that you are exporting, you may also want to set the IF_ROW_STAT column to FOR_EXPORT.

Preserved Column Values:

The values for the LAST_UPD and CREATED columns in the EIM tables always contain the values for the LAST_UPD and CREATED columns from the target base table. For example, if you use the EIM_CONTACT interface table to export data from the S_CONTACT and S_ADDR_PER base tables, the values of the EIM_CONTACT.LAST_UPD and EIM_CONTACT.CREATED columns contain the data from the S_CONTACT.LAST_UPD and S_CONTACT.CREATED columns, respectively.

EIM Tables Not Supported for Export Processes:

Due to the complexity of the associated base tables, EIM export processes to the following interface tables are not supported:

1. EIM_ACCSRCPIDTL

2. EIM_CRSE_TSTRUN

3. EIM_IC_CALC

4. EIM_IC_PERF_HST

5. EIM_MDF

Editing the Configuration File for Export Processing:

Process Section:

To export data, you must define at least one process with TYPE = EXPORT.

Common Header and Process Section Parameters:

ATTACHMENT DIRECTORY:

Default is SIEBEL_HOME\OUTPUT.
Specifies the directory to be used for exporting attachments.
Example: ATTACHMENT DIRECTORY = SIEBEL_HOME\OUTPUT (for Windows)

CLEAR INTERFACE TABLE:

Specifies whether existing rows in the EIM table for the given batch number should be deleted.
DEFAULT is TRUE.

EXPORT ALL ROWS:

Specifies that all rows in the target base table and secondary tables are to be exported.
DEFAULT is FALSE.
Existing values in the EIM table and export matches’ expressions are ignored.
For all columns to export using an EIM table (both data from the base table and data from related child tables), you need to make sure this parameter is set to TRUE (you may need to add this line if it does not currently exist) in the .IFB file.
Example: EXPORT MATCHES = (NAME LIKE "GEN%")

EXPORT MATCHES:

Specifies a WHERE clause expression for filtering base table rows.
The value is in two parts: the Siebel EIM table name and the filter expression that goes against the target base table. The expression is applied against the target base table for the EIM table.
The expression is a self-contained WHERE clause expression (without the WHERE) and should use only literal values or unqualified column names from the base table. There must also be a space separating the operator from the operand.
The syntax to use with the EXPORT MATCHES parameter depends on whether the target base table is S_PARTY or not.
Syntax for EXPORT MATCHES with S_PARTY as the Target Base Table.

Exporting All Data Rows:

To export all rows from the tables that are mapped in an EIM table, set the EXPORT ALL ROWS parameter for the file to TRUE.

Example:

[Export Accounts]

TYPE = EXPORT

BATCH = 2

TABLE = EIM_ACCOUNT

EXPORT ALL ROWS = TRUE

Chapter 6: Delete Data

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.

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.

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.

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

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

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

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.

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

Chapter 7: Merge Data

Overview of EIM Merge Processing:

EIM uses a combination of EIM table row contents and configuration file parameter values to control the merge process.
A merge process deletes one or more existing rows from the base table and makes sure that intersecting table rows are adjusted to refer to the remaining rows.
Data from the record you select as the surviving record is preserved. Data from the other records is lost.
If there are other records associated with the records you merge, those records—with the exception of duplicates—are associated with the surviving record.
Duplicate child records of the deleted rows will have CONFLICT_ID updated during the merge process.
For example, when merging two Accounts (parent), the user keys of the Contacts (child) will be compared, and if the same Contact belongs to both Accounts, the Contact of the deleted Account will have its CONFLICT_ID updated.
You can only merge records that have primary user keys. Because records in the following tables do not have primary user keys, these records cannot be merged:

1. Notes

2. Territory Items

3. Fulfillment Items

It is not possible to merge 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 between these rows.
In such cases, the IF_ROW_STAT field of the row in the EIM table will be marked as AMBIGUOUS.
EIM can only be used to merge rows from target base tables and not secondary tables.
For example, the target base table for EIM_ASSET is S_ASSET. EIM can only be used to merge two or more S_ASSET rows into single S_ASSET rows. You cannot use EIM to merge two or more S_ASSET_CON rows into single S_ASSET_CON rows.

EIM Merge Process:

During its multiple passes through the EIM tables, EIM completes the following tasks within a merge process:

Initialize the EIM tables for merge.
Select for merge the rows with matching user keys in the EIM tables.
Merge child rows into the replacement rows. EIM then deletes rows from the target base table that are specified in the EIM table.
For deleted rows, EIM sets T_MERGED_ROW_ID to the ROW_ID of the row that was merged into (the surviving row).
EIM sets T_DELETED_ROW_ID to the ROW_ID of the deleted base table row.
Update child rows containing foreign keys that point to newly deleted rows. For base tables that have foreign keys in newly deleted rows, EIM updates the foreign keys to point to surviving rows (depending on the value for UPDATE ROWS in the configuration file).

Preparing the EIM Tables for Merge Processing:

Populate the following columns:

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

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

IF_ROW_MERGE_ID:

Set this value to one of two values.
For an EIM table row whose ROW_ID and IF_ROW_BATCH_NUM columns identify the surviving or merged-into row, set this value to NULL.
For EIM table rows whose ROW_ID and IF_ROW_BATCH_NUM columns identify a row to be merged (and subsequently deleted), set this value to the ROW_ID where this row will be merged.
Upon completion of the merge process, the first row survives and the remaining rows are deleted.

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

1. IF_ROW_BATCH_NUM value for the rows that require re-merging.

2. BATCH NUMBER line in the configuration file.

If you do not correctly populate all the user key columns, the merge process will fail and the IF_ROW_STAT column in the EIM table will be set to the value NO_SUCH_RECORD. This indicates that EIM cannot find the appropriate rows to merge using the specified user keys.

Common Header and Process Section Parameters:

SET BASED LOGGING:

Specifies whether set-based logging is enabled.
Default is TRUE.

UPDATE ROWS:

Specifies whether the foreign key (or keys) that reference the merged rows in the named table need to be adjusted.
Default is TRUE.

SET BASED LOGGING Parameter:

When set-based logging is enabled, a separate log entry is generated for all rows in each table affected by EIM.
This allows greater performance improvement because EIM can perform the operations as set operations in SQL, without resorting to row-by-row processing to support the transaction log.
Set-based transaction logging is most useful when a table is read-only to mobile Web clients.
Set-based logging is always the default for merge.
The SET BASED LOGGING parameter must be set to FALSE to allow transaction logging for merge.

Updating Affected Rows:

During a merge operation, a specific base table may have some rows deleted and others updated.
You can use the UPDATE ROWS parameter to prevent updates to one base table while allowing updates to another.
By default, UPDATE ROWS = TRUE.

Avoiding Aborts of EIM Merge Processing:

If an EIM merge process is aborted, base tables associated with merged rows may not be updated.
Orphan rows may be created because foreign keys may not have been updated. This may cause critical data integrity issues.
To avoid this problem, set the following parameters in the .IFB file so the EIM merge process performs only one commit or rollback when aborted.

COMMIT EACH TABLE = FALSE

COMMIT EACH PASS = FALSE

ROLLBACK ON ERROR = TRUE

Enabling Transaction Logging for Merge Processing:

To enable transaction logging for an EIM merge process, set the following parameters in the .IFB file so the EIM merge process runs in ongoing (row-by-row) mode:

LOG TRANSACTIONS= TRUE

SET BASED LOGGING = FALSE

Specifying Survivor Records for Merge Processes:

In a merge process, data from the record you select as the surviving record is preserved, while data from the other records is lost.
Do not specify the same record as both the survivor and the victim or it will be deleted. You should also make sure that a record is specified as a survivor only once in a batch.

Checking Merge Results:

When a merge process ends, you should carefully check the results to verify that data was successfully merged.

During each process, EIM writes comprehensive status and diagnostic information to several destinations.

During a merge process, EIM writes the following values to two special columns in the EIM tables:

T_DELETED_ROW_ID contains the ROW_ID of the deleted base table row.
T_MERGED_ROW_ID contains the ROW_ID of the surviving base table row.

Chapter 8: Running EIM Job

Using Trace Flags, SQL Trace Flags, and Error Flags:

You can activate trace flags and error flags to log transactions. This topic covers the following types of flags:

Error flags: Setting the Error Flags parameter to 1 produces a detailed explanation of rows that were not successfully processed.
SQL Trace flags: Setting the SQL Trace Flags parameter to 8 creates a log of all SQL statements that make up the EIM task. The lower values for SQL Debug Flags (1, 2, and 4) are used for logging at the ODBC level.
Trace flags: Trace flags contain logs of various EIM operations. Trace flags are bit-based. Available trace flags include 1, 2, 4, 8, and 32. To activate multiple trace flags, set the Trace Flags parameter to the sum of individual trace flag numbers. For example, to log trace flags 2 and 4, set the Trace Flags parameter to 6.

Recommended settings for error flags, SQL trace flags, and trace flags include the following:

To display errors and unused foreign keys:

Start with the following setting combination. The setting Trace Flag=1 provides a summary (after each batch) of the elapsed time in EIM steps 10 and 11.

Error Flag 1

SQL Trace Flag 1

Trace Flag 1

To determine SQL performance: The following setting combination produces a log file with SQL statements including the elapsed time for each statement.

Error Flag 1

SQL Trace Flag 8

Trace Flag 3

To determine optimal batch size and monitor performance in a particular step: The following setting combination produces a log file showing the elapsed time for each EIM step.

Error Flag 0

SQL Trace Flag 0

Trace Flag 1

Optimizing EIM Performance:

There are several ways you can improve EIM run-time performance. The best practices suggested in this section optimize EIM performance. This section discusses ways that you can optimize tables for EIM processing.

1. Configuration Parameters Limit base tables and columns to be processed. Four EIM parameters can help improve performance by limiting the affected tables and columns:

ONLY BASE TABLES

IGNORE BASE TABLES

ONLY BASE COLUMNS

IGNORE BASE COLUMNS

The ONLY BASE COLUMNS parameter is critical for the performance of an EIM process updating a few columns in many rows.

2. Indexes:

Verify that all indexes exist for the tables involved. In most implementations, the tables and corresponding indexes in the following list tend to be the most heavily used and should be separated across devices. In general, the following indexes should be on different physical devices from the tables on which they are created.

S_ACCNT_POSTN

S_OPTY

S_ADDR_ORG

S_OPTY_POSTN

S_CONTACT

S_POSTN_CON

S_DOCK_TXN_LOG

S_PARTY_RPT_RE

S_SRV_REQ

S_EVT_ACT

S_OPTY

S_ORG_EXT

For organizations that plan to use EIM extensively, you should put your key EIM tables (based on your unique business requirements) on different devices from the Siebel base tables, because all tables are accessed simultaneously during EIM operations. You can speed up deletes and merges involving S_ORG_EXT by adding an index to one or more columns.

3. Maintenance of EIM Tables:

Perform regular table maintenance on EIM tables. Frequent insert or delete operations on EIM tables can cause fragmentation in the table. Ask your database administrator to detect and correct fragmentation in the EIM tables. Always delete batches from EIM tables upon completion. Leaving old batches in the EIM table wastes space and can adversely affect performance.

4. Batch Processing Optimization for EIM:

This section suggests ways in which you can optimize EIM batch processing. Try using different batch sizes. Large batch sizes are often not efficient. For import and delete processes that use the DELETE EXACT parameter, use approximately 20,000 rows in a single batch. Limiting the Number of Records and Rows for Merge Processes You can improve performance by limiting the number of records in a batch.

5. Using Batch Ranges:

Try using batch ranges (BATCH = x–y). This allows you to run with smaller batch sizes and avoid the startup overhead on each batch. The maximum number of batches that you can run in an EIM process is 1,000.

6. Run-Time Optimization for EIM:

This section describes the ways you can optimize EIM performance at run time.
Parallel Processing:
Run independent EIM jobs in parallel.Two or more EIM processes can be started simultaneously by using the Siebel Server Manager. A special setup is not required to run EIM processes in parallel. For parallel processing, the following conditions must be met:
No duplicate unique keys between runs for inserts.
No duplicate updates or deletes between runs.
No lock escalations on either EIM tables or target tables can be tolerated. Set LOCKLIST and MAXLOCKS as high as necessary to prevent this.

7. Transaction Logging:

Consider disabling the Enable Transaction Logging system preference in the Administration - Siebel Remote screen during the EIM run. Switching off transaction logging improves performance; however, this benefit must be balanced with the need for mobile users to re-extract afterward.

8. Parameter Settings Optimization for EIM:

This section discusses ways that you can optimize EIM performance through parameter settings.
USING SYNONYMS Parameter for Optimizing EIM:
Ignore account synonyms. Set the USING SYNONYMS parameter to FALSE in the .IFB file to indicate that account synonyms can be ignored during processing. This logical operator indicates to EIM that account synonyms do not require processing during import, thus reducing the amount of processing. Do not set the USING SYNONYMS parameter to FALSE if you plan to use multiple addresses for accounts. Otherwise, EIM will not attach addresses to the appropriate accounts. You can use EIM_ACCOUNT to import accounts with multiple addresses and then specify the primary address for an account by setting ACC_PR_ADDR to Y.

Trace Flag Settings for Optimizing EIM:

Generate a task log to identify slow-running steps and queries by using Trace Flags. To use Trace Flags, set Error Flags=1, Trace Flags=1, and SQL Trace Flags=8. Rerun the batch and use the resulting task log to determine which steps and queries are running especially slowly.

9. Database Server Optimization for EIM:

The overall performance of EIM is largely dependent on the overall performance of the database server. To achieve optimal database server performance, it is critical that the tables and indexes in the database be arranged across available disk devices in a manner that evenly distributes the processing load.
The mechanism for distributing database objects varies by RDBMS, depending on the manner in which storage space is allocated. Most databases have the ability to assign a given object to be created on a specific disk.
A redundant array of independent disks (or RAID) can provide large amounts of I/O throughput and capacity, while appearing to the operating system and RDBMS as a single large disk (or multiple disks, as desired, for manageability).
The use of RAID can greatly simplify the database layout process by providing an abstraction layer above the physical disks while achieving high performance. Regardless of the RDBMS you implement and your chosen disk arrangement, be sure that you properly distribute the following types of database objects:
Database log or archive files.
Temporary workspace used by the database.

By following these suggestions, you should be able to improve the performance of the database server.

Pages

Siebel EIM Made Easy

No comments:

Post a Comment