Share

General Guidelines for Optimizing EIM -Continued

Kirill Izotov ,Managing Director, IT has provided comments on Mandeep's article , I thought of publishing it as a separate post. Please find Kirill's original comment below. This is an interesting discussion, and I would like to offer some additions to the recommendations kindly published by Mandeep Grewal.

1. The batch size should be considered very carefully depending on the status of the system – if the system that EIM is running on is "hot" or "cold", i.e. if users and other processes are actively performing insert/update/delete operations ("hot") or the system is provided exclusively for the EIM processing ("cold").

2. Look up isolation level. Locking, for example on SQL server, may cause lock waits and even chained lock waits, and insert/update/delete operations on certain systems may cause true operational problems if the batch size is larger than – in some cases – even 2000 records. In a case of merge operations the batch size should be even smaller in most cases per the number of tables involved into processing.

 

3. If the system is "cold" – run the EIM processes in parallel (from EIM components residing on different Siebel servers if you need to run many tasks, depends on the capacity of you EIM component Siebel server) and turn on row level locks – disable all higher locking. This will help avoiding processes waiting on each other for lock releases. Run as large batches as you see fit knowing your system (start with 30K rows and go up and down in the increments of 10K, than 2K, until the optimal batch size is defined). Keep in mind that for most systems to avoid bottlenecks on I/O, CPU and memory 20K-40K batches may be optimal. Hint: the bottleneck usually resides with I/O, so plan in advance with DBAs which file groups/files you place EIM tables and EIM tables indices in, and place those onto separate sets of spindles (almost irrelevant in case of solid state drives or advance storage systems). Partition the EIM tables based on batch numbers if you need to perform large load, create partitions for 200-300K rows. Implement alerting on the db server for any lock waits >60 seconds, comes handy.      

4. It is worth putting batches into a minimum number of processing sequences; that eliminates necessity for the EIM task loading dictionary from the repository for each of the batches – it is done once for all batches in a process.

5. "Regular maintenance" is a general rule for any table in a DB that is undergoing changes; in the case of EIM when any significant number of records are processed defragmentation and statistics updates should be performed on the EIM table(s) right post populating EIM table and prior processing, and may be in parallel with processing (depends on how large is the load and the degree of indices fragmentation you observe when doing routine check during processing). This stands true for inserts, updates, deletes, exports and merges. For DB2 you can enjoy UPDATE STATISTICS parameter in the .ifb file, but defragmentation is still needed if you are not running on some very advance storage system like EMC Symmetrix DMX.

 

6. Once working on a "cold" system (when the system is dedicated to the load) interesting results in terms of speeding up large loads – up to 3-15000 times per my experience – can be obtained once you drop all indices from the target base tables (leave only clustered and user key/unique indices alone on the base tables), and drop ALL indices on the EIM table – create clustered index with a "covering" key based on the columns of the indices you see in the trace minus columns that you are not populating in the EIM table + ROW_ID for uniqueness (yes, this contradicts best practices for clustered indices but in this case works with a multi-column unique key like a charm). Of cause, if you use this approach setting of hints and essential hints to false in the .ifb file is necessary (in some cases you will still see hints in traces – use trace flag 8602 on SQL server to disable hints on db server level, I cannot recall for Oracle from the top of my head). Once you are done with EIM processing, apply all indeces as those were defined prior you started your exercise, and remove the flag.

7. After you are done with the initial planning/testing/tuning of a large load, disable all events logs (to 0 or 1 depending on your version of Siebel) and traces on the application servers EIM components. Not a large win, but once we are talking of hundreds of millions of records to process even small percentage may give you extra time for your precious resting. The same applies to dropping triggers, disabling transactions logging, etc.

5 comments on “General Guidelines for Optimizing EIM -Continued”

  1. Rahul Reply

    Hi Kirills,
    it is really very good information for performing large loads..

    it is really nice to see such type of information on this blog.

  2. Pravin Reply

    Query EIM /1

    Hi Ashish ,

    You have a very wonderful blog and it has really helped me in Siebel work.

    I wonder if you can help me with an EIM problem.

    I am trying to update rows in S_CAMP_CON table, I am using the EIM_CAMP_CON table. I have tried everything but I keep getting the same error,
    which is SBL-EIM-00425: All rows in interface table failed.

    In the log files it says
    EIMTrace EIMTraceSubEvent 3 000000084a7a0cb2:0 2009-08-06 10:13:42 8/6/09 10:13 Warning: Foreign key S_CAMP_CON.SRC_ID failed 1 rows.
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 ——————————————————————————-
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 Process [LOAD CODES] had 1 row fail
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 on EIM_CAMP_CON for batch 9000 in step 4, pass 102:
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 Failed to resolve foreign key value. (severity 6, row eliminated)
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 Interface table:
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 EIM_CAMP_CON (EIM_CAMP_CON)
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 ————
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 CON_SRC_BI (Name)
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 CON_SRC_NUM (Source Number)
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 CON_SUB_TYPE (Internal Source Type Code)
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 Base table:
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 S_CAMP_CON (Contact for Campaign)
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 ———-
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 SRC_ID (Source)
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 This is a foreign key value in the base table and the values in the interface
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 table did not resolve to existing values. Verify that the IF columns correspond
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 to existing base table rows.
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 This failure caused the rows to be eliminated from further processing for the
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 target base table. This failure will prevent these rows from being imported at
    EIMError EIMErrorSubEvent 0 000000084a7a0cb2:0 2009-08-06 10:13:42 all.
    My IFB file is
    [Siebel Interface Manager]
    USER NAME = “SADMIN”
    PASSWORD = “SADMIN”
    PROCESS = Update Campaigns

    [Update Campaigns]
    TYPE = IMPORT
    BATCH = 9000
    TABLE = EIM_CAMP_CON
    ONLY BASE TABLES = S_CAMP_CON
    INSERT ROWS = FALSE
    UPDATE ROWS = FALSE
    UPDATE ROWS = S_CAMP_CON, TRUE

    I populated a test row with,
    insert into eim_camp_con (ROW_ID, IF_ROW_BATCH_NUM, IF_ROW_STAT,CON_TARGET_NUM,LOAD_NUM,T_CAMP_CON_BU_ID,T_CAMP_CON_CAMPLD,T_CAMP_CON_CONPER,T_DCP_ID,T_CAMP_CON_PRCALL,T_CAMP_CON_SRC_ID)
    values (’1-2AR8I’, 9000, ‘FOR_IMPORT’,1001,1,’0-R9NH’,’1-2AUDU’, ‘1-2AR3V’,’1-29RDT’,’1-2AU8T’,’1-2AUDI’)

    please any assistance you can give would be great.

  3. Rajkumar Raju Reply

    Resolution Query EIM /1
    Pravin, Rajkumar has answered your query efficiently.Please read below.

    Hi Pravin,

    If you want to update SRC_ID in S_CAMP_CON, then you should not populate T_CAMP_CON_BU_ID,T_CAMP_CON_CAMPLD, which are actually temporary columns which are meant for Siebel to internally populate after after EIM Job..
    As per my understanding,
    CON_SRC_BI (Name)
    CON_SRC_NUM (Source Number)
    CON_SUB_TYPE (Internal Source Type Code)
    are the typical columns you need to populate the SRC_ID

    Please leave T_ columns for Siebel Internal things to update.

    In EIM Mapping, find the respective foreign key column mapping and update this column referring the base tables.

    In case of IFB ,

    ONLY BASE TABLES = S_CAMP_CON
    INSERT ROWS = S_CAMP_CON, FALSE

    The above is simple enough for an UPDATE, and if you want you can add the following
    UPDATE ROWS = S_CAMP_CON, TRUE

    Hope this information helps/

    Hi Pravin,

    If you want to update SRC_ID in S_CAMP_CON, then you should not populate T_CAMP_CON_BU_ID,T_CAMP_CON_CAMPLD, which are actually temporary columns which are meant for Siebel to internally populate after after EIM Job..
    As per my understanding,
    CON_SRC_BI (Name)
    CON_SRC_NUM (Source Number)
    CON_SUB_TYPE (Internal Source Type Code)
    are the typical columns you need to populate the SRC_ID

    Please leave T_ columns for Siebel Internal things to update.

    In EIM Mapping, find the respective foreign key column mapping and update this column referring the base tables.

    In case of IFB ,

    ONLY BASE TABLES = S_CAMP_CON
    INSERT ROWS = S_CAMP_CON, FALSE

    The above is simple enough for an UPDATE, and if you want you can add the following
    UPDATE ROWS = S_CAMP_CON, TRUE

    Hope this information helps/
    rajkumarmpr@gmail.com
    Rajkumar Raju

  4. Javed Reply

    I guess , SRC_ID is a User Key in S_CAMP_CON table( pls check that). If ‘Yes’.. then there is no way you can update the SRC_ID.

    If not, as you’r trying to update the FK.. make sure that the value that you’r trying to update it to already exist( i.e. s_src table record).

    If its a non-Fk column than any other value would do.

    In all above cases , you must provide the S_CAMP_CON USerkey values in EIM_CAMP_CON table.

    Thanks

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!