Guide for EIM Tuning

EIM batch loading always facing with performance problem and always need a tuning process in order to make it work! 

I would like to share some guidelines to tune EIM

•  Turn off Transaction Logging will improve the performance.  Please try to check with the mobile client users to make sure that they are not impacted during the EIM Run.
• Limit amount of record per batch number. Usually I use 1000 records per batch for optimum performances.
• Use Batch Ranges to limit certain amount of batch to be run. Only 1000 Batches can be run per 1 EIM Task.
• Perform scheduled Index rebuild and Reorganization of Siebel Base and EIM Tables to maintain good performance.  Frequent Insert and Delete operations on Tables can cause fragmentation.Consult your DBA based on the DB Platform that you used.
• Do not leave old batches in EIM Table after running. Purge old batches on scheduled basis.
• EIM jobs can be run in parallel, must consider DB Server capabilities in terms of Hardware Spec and Database Features.
• Set “USING SYNONYMS” parameter in IFB File to FALSE
• Limit Table and Columns by using “ONLY BASE TABLES/COLUMNS” or “IGNORE BASE TABLES/COLUMNS”  in IFB File in order to minimize the processing time during EIM Run.
• You can drop indexes that are not being used during EIM Run and rebuild after the EIM Process has finished. This will shorten the time compared to each time the EIM run, the process need to update the indexes.
• Avoid using Custom Primary MVG Field in Siebel Configurations that might needed for EIM Batch Upload, as it may cause an Implicit Update Primary which can really slow down the performance. 
• Set “UPDATE STATISTICS” parameter in IFB File to FALSE. The Index statistics can be rebuild in bulk manner afterwards which has proven to be faster.
• Disable Archive Logging during initial data load. Can enable again after load.

I often use this steps in order to troubleshoot any performance problem:

1. Try to find out the long running SQL by analyzing the time taken to execute it. There are several ways to do it such as to rerun the EIM process using parameter Trace Flag =1 and SQL Trace Flag = 8. The EIM Log sizes will be larger than usual. There you can see the SQL generated. While the other is using SQLPROFILE parameter in IFB File.
2. Monitor the DB Server to find out that there might be other activities which consuming the server resources. things i always check is the utilization level, lock dependency, rollback segment and tablespace sizes.
3. Try to Rebuild Index, Statistics or maybe reorganization if needed.

Finally, hope this might give you an insight to solve the issue.

Guide to Create a Unique Sequence Number in Siebel

How to configure unique running sequence number

Many times in a Business Applications , there is a need to have a running sequence number to represent and keep track of a single and multiple business entities.

In order to achieve this functionality in Siebel CRM Applications, there are two alternatives that can be implemented which are:
1. Use Oracle Database Sequence Object
Firstly, the Database Administrator must create the object. Below are the syntax:

CREATE <SEQUENCE OBJECT NAME> INCREMENT BY <INCREMENT VALUE> START WITH <START RANGE> MIN VALUE <MIN VALUE> MAX VALUE <MAX VALUE> CYCLE ORDER

For Example:
CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 CYCLE ORDER;

The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.
for more details of Oracle DB Sequence Objects can be found in
http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_6014.htm

you also need to grant the permission to SSE_ROLE in order for the object to function properly.

Later in Siebel Tools, you need to configure the field properties with name "Oracle Sequence Object" under Business Component > Fields. You put in the specified Oracle DB Sequence Object name that has been configured earlier.

compile and run. you will see the field is going to be populated with running sequence number when a new record is being created.

2. Use a Custom Controlled Table to handle the sequences

You can also create a control table to handle the sequences. Script can be written to handle the uniqueness and session control mechanism to perform as per expected.

In comparison of the two option, using a predefined sequence object is more preferable and has minimum risk.