Prior to running EIM it is important to clean up fragmented objects, especially those that will be used during the EIM processing. The following SQL statement can be used to identify objects with greater than 10 extents:

SELECT segment_name,segment_type,tablespace_name,extents
FROM dba_segments
WHERE owner = (Siebel table_owner)
and extents > 9;

To fix fragmentation, the objects will need to be rebuilt with appropriate storage parameters. Always be careful when rebuilding objects because of defaults, triggers, etc. on the objects.

Optimizer Mode

Oracle optimization mode can also affect EIM performance. The Siebel application has been found to perform better under RULE based optimization under normal application usage. While there have been cases where it has been found that Cost based optimization has improved EIM performance this should only be attempted as a last resort and must be switched back to RULE for online usage. Be aware that Siebel only supports Rule-Based optimization. Optimization mode can be verified by running the following query:

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = ‘OPTIMIZER_MODE’;

(must have DBA privilege)

When purging data from the EIM table, use the TRUNCATE command as opposed to DELETE. The TRUNCATE command will release the data blocks and reset the high water mark while DELETE will not, thus causing additional blocks to be read during processing. Also, be sure to drop and re-create the indexes on the EIM table to release the empty blocks.

One other measure that can help performance is to pin small tables that are frequently accessed in cache. The value of BUFFER_POOL_KEEP determines the 'keep pool', example, the portion of the buffer cache that will not be flushed by the LRU algorithm.

The 'keep pool' allows one to 'pin' certain tables in memory, thus improving performance for accessing those tables. This will ensure that after the first time that the table is accessed it will always be in memory. Otherwise it is possible that the table will get pushed out of memory and will require disk access the next time used. Keep in mind that the amount of memory allocated to the ‘keep’ area is subtracted from the overall buffer cache memory (defined by DB_BLOCK_BUFFERS).

A good candidate for this would be the S_LST_OF_VAL table. The syntax for pinning a table in cache is as follows:

ALTER TABLE S_LST_OF_VAL CACHE;

Multiple EIM processes can be executed against an EIM table provided they all use different batches or batch ranges. The concern is that contention for locks on common objects may be experienced. To run multiple jobs in parallel against the same EIM table, check that the ‘FREELIST’ parameter is set appropriately for the tables and indexes used in the EIM processing. This would include the EIM tables and indexes as well as the base tables and indexes.

The value of this parameter specifies the number of block ids that will be stored in memory which are available for record insertion. As a rule of thumb, users should set this to at least half of the intended number of parallel jobs to be run against the same EIM table (example, a FREELIST setting of 10 should permit up to 20 parallel jobs against the same EIM table). This parameter is set at the time of object creation and the default for this parameter is 1. To check the value of this parameter for a particular object the following query can be used:

SELECT SEGMENT_NAME, SEGMENT_TYPE, FREELISTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME=’’;

To change this parameter the object must be rebuilt. Again, be careful when rebuilding objects because of defaults, triggers, etc. on the objects. To rebuild the object follow the steps below:

1. Export the data from the table with the grants.
2. Drop the table.
3. Re-create the table with the desired FREELIST parameter.
4. Import the data back into the table
5. Rebuild the indexes with the desired FREELIST parameter.

Siebel Enterprise Integration Manager (EIM) manages the bi-directional exchange of data between the Siebel database tables and other corporate databases. This exchange of information is accomplished through intermediary tables called, EIM tables (in earlier releases of Siebel application, these were known as Interface Tables). The EIM tables act as a staging area between the Siebel application database and other databases. Customers should use EIM to perform bulk imports, exports, updates and deletes. Clear examples of when to use EIM are:

When initially implementing a Siebel application, users should use EIM to load the Siebel database tables with data and file attachments created by external applications. For example, users might import information about product lines and products from an Inventory Control database into the “Products” entity in the Siebel database.

As part of maintaining the database, users can leverage EIM for data archival. This not only provides customers with a Siebel database that is optimally utilizing the resources available to it, but also streamlines the implementation of a corporate data archival strategy.

When initially implementing a non-Siebel application, users can export data from the Siebel database tables for use by that application. For example, users might export employee information to a human resources application.

As part of maintaining a non-Siebel database, users can update it with information from the Siebel database. For example, users might add new customers to an accounting database from the Siebel databases.

Clear examples when customers should consider using other Siebel products (example, Siebel eAI) over Siebel EIM:

As part of maintaining the database, where users have a large number of very thin updates (example, 1 – 10 columns) to a very narrowly defined set of Siebel base tables (3 tables maximum). In this scenario Siebel eAI, utilizing an integration object based on a business component with only the columns users require, this will provide users not only with the required functionality but may also provide a performance increase.

When the business requirement dictates that real-time feeds be available from other enterprise applications that their employees must utilize in order to accomplish their jobs.

After upgrading Siebel applications to a higher release, certain columns that were previously not List-of-Value (LOV) bounded became LOV bounded that could cause EIM import failure Oracle Siebel highly recommends that users evaluate the potential impact of schema upgrade before they perform any EIM loading in a new release.

If a field in a business component is associated with a bounded pick list, the base column that the field is mapped to will become LOV bounded to maintain consistency. More columns become LOV bounded after upgrading Siebel applications to a higher release.

EIM import would fail in a new release if a user does not populate the valid LOV data in EIM interface table. You may get the following error in EIM log files:
"This column contains a bounded picklist value and the value given does not correspond to a value in the list-of-values table for the given picklist type."

The following approaches are strongly recommended:

Run utleimdiff.exe utility to compare the differences of EIM interface tables between the old repository and new repository. For detailed instruction on how to run this utility, please refer to FAQ 1737: How can users find differences in all interface tables between two repositories? On Oracle Siebel support web.

The following SQL scripts are used to identify all the changes of LOV bounded columns between two repositories:

lov_diff_check.sql

This script compares differences between the older repository and the new repository. It will generate a list of LOV columns that were changed from LOV un-bounded to LOV bounded in the new repository. Users need to include the names of the two repositories in the first two lines of the SQL script. For example:
Define repository1 = "Siebel Repository 14068 FINS"
Define repository2 = "Siebel Repository 14010 FINS"

In addition, users can run this script to generate a list of LOV columns for the specific EIM tables by modifying the following statement in the script:
And IT2.NAME in ('EIM_FN_CENSUS')

In the "Advanced Usage" chapter of the Siebel "Enterprise Integration Manager and Administration Guide", it is recommended that batch sizes of 5,000 records or less be used. Running Enterprise Integration Manager Tasks with batch sizes greater than 5,000 records could result in a stalled Transaction Router or memory leak in Replication Agent server component when merging records into regional node database. If the Transaction Router is stalled, remote users will not receive updates from the Server Database. In order to resolve the behavior, all remote users must be re-extracted. When using replication manager with a batch size over 5000 records, , the router needs to run more SQL to determine visibility and in doing this large amounts of memory will be used and this may lead to memory related failure of the Replication Agent server component.

Workaround or Resolution

It is recommended that when running Enterprise Integration Manager, batch sizes should be no larger than 5,000. Note also that smaller batch sizes (no larger than 2,000) are recommended to improve performance, as stated in FAQ 1097: How can I tune my EIM batches to improve performance?
Note that for importing or updating data, the batch size can explicitly be set. For deleting records using Delete Exact=TRUE, the batch size can be explicitly set.

However, if deleting records using Delete Matches, do not explicitly set a batch size in the Interface Table. In order to ensure that the delete affects 2,000 or fewer records, simply run a select statement including the Delete Matches criteria directly against the base table to determine the total number of records that will be deleted.

An aborted Enterprise Integration Manager (EIM) Merge or Delete process will leave orphan records if the EIM process is terminated abnormally.

The following EIM .ifb file parameters determine when an EIM process will commit and if the pending SQL statements will be committed if an error is encountered.
COMMIT EACH TABLE = TRUE
COMMIT EACH PASS = TRUE
ROLLBACK ON ERROR = FALSE

The default settings for these .ifb file parameters specify that the EIM process will execute a commit after each EIM pass or each table is processed and that the pending SQL statements will be committed if the process is aborted. Generally with EIM import, or export types of processes, these settings are appropriate. If the EIM process terminates in the middle of processing, the work done so far is committed. Then the batch can be reloaded into the EIM interface tables and the batch can be re-run. The remaining work that was not processed in the aborted EIM import, or export type of process will be done in the next run.

These default settings are not appropriate for an EIM Merge or Delete process because if the process terminates in the middle of processing there can be data integrity issues.

For example if contact A is merged into contact B, the EIM Merge process does the following steps.

Initialize the interface tables for merge.
Delete the contact A record in the base table.
Update the base tables that have foreign keys that point to the deleted contact A record to point to the contact B "survivor" record.

EIM will commit after each pass and each table is processed and will not rollback the transaction on an abort if the default parameters are used. This can cause data integrity issues if the EIM Merge or Delete process has not completed. If the commit and rollback .ifb file parameters for an EIM merge or delete process are not changed, the Siebel data can have "orphan" records in the base table, which have foreign keys that point to the deleted Contact A record. In that case the EIM Merge process was terminated before EIM could update all of those tables foreign keys to point to the survivor record.
If this situation occurs, make a backup of the EIM interface table since the temporary columns will have information about the EIM merge or delete processing.

Therefore, using the following settings for an EIM process is recommended:

COMMIT EACH PASS = FALSE
COMMIT EACH TABLE = FALSE
ROLLBACK ON ERROR = TRUE

This ensures that EIM will either process the entire merge or delete batch or rollback the batch.

The database resources need to be large enough to accommodate this large transaction.

It is also recommended that the EIM Merge batch be limited to only 100 records for performance reasons.

Likelihood of Occurrence

The data integrity issue can happen when an EIM Merge or Delete type process is aborted.

Possible Symptoms

A possible symptom is for the EIM server task to error out before completing. Reviewing the records in the Siebel tables reveals that the base table records associated to the target base table, which was deleted, still point to the deleted target base table record.

Workaround or Resolution

Using the following settings for an EIM Merge or Delete type process is recommended:

COMMIT EACH PASS = FALSE
COMMIT EACH TABLE = FALSE
ROLLBACK ON ERROR = TRUE