Populating column with "Expr: 'RowIdToRowIdNum ([Id])'" expression in the pre-default value property of field

Sometimes the requirement is to populate columns having the expression 'Expr: 'RowIdToRowIdNum ([Id])' in the pre-default property of the field. There are few columns in siebel where the field is pre-defaulted to this expression. 

For example, SR_NUM column of S_SRV_REQ table (Service Request table), OFFER_NUM column of S_DMND_CRTN_PRG table, MTRX_RULE_NUM column of S_STDPROD_PMTRX table etc.

Let us first understand the purpose of the Expression "RowIdToRowIdNum ([Id])":

1. Pre-default value always gets evaluated whenever a new record in created from UI. While importing records through EIM, the pre-default value is ignored.

2. As the expression itself indicates, it evaluates the value of the column by converting the value of ROW_ID column into some numeric value. This conversion algorithm/formula (ROW_ID value to numeric with a hyphen '-' in-between) is internal to siebel.

3. There are very few fields in siebel which have this expression in the pre-default property and most of them are not significant. Hence the requirement of populating these columns is also very few.

4. Moreover these columns are required to populate only when they are marked as required column in siebel tools, otherwise it can be ignored.

If the requirement is to populate the column say for example S_STDPROD_PMTRX.MTRX_RULE_NUM which is a required user key column, it can be updated with either the ROW_ID value or any other unique value like sequence etc.

Updating MTRX_RULE_NUM with ROW_ID value:

1. Run EIM to import the S_STDPROD_PMTRX rows without some unique MTRX_RULE_NUM values first, for example populate it with any database sequence value.

2. Once the import is successful, select the imported rows in step 1, and re-populate them into the interface table (with different batch number) with ROW_ID values (from siebel) in the MTRX_RULE_NUM column.

3. Run EIM import again to update S_STDPROD_PMTRX.MTRX_RULE_NUM with the values populated in step 2. Please include the following parameters in the .ifb file:

       TYPE = IMPORT
       BATCH = 2
       TABLE = EIM_ADJ_GROUP
       ONLY BASE TABLES = S_ADJ_GROUP, S_STDPROD_PMTRX
       INSERT ROWS = S_ADJ_GROUP, FALSE
       UPDATE ROWS = S_ADJ_GROUP, FALSE
       INSERT ROWS = S_STDPROD_PMTRX, FALSE
       UPDATE ROWS = S_STDPROD_PMTRX, TRUE

This way we can update the column with siebel ROW_ID values i.e by first importing then exporting and then again importing with update only.

Remember, These special columns cannot be populated with exact pre-default expression values through EIM.

For more details logon to Oracle Support portal.

Importing Contact in Siebel through EIM

Import Contact:

For importing contact in siebel you need to import below siebel tables:

S_CONTACT
S_CONTACT_BU
S_POSTN_CON
S_PARTY

Brief description of above tables:

• S_CONTACT: This base table stores all the general details related to a contact like Name, Telephone No. etc.
• S_CONTACT_BU: This is an intersection table between S_CONTACT and S_BU which stores the organization of the contact.
• S_POSTN_CON: This is an intersection table between S_CONTACT and S_POSTN. The POSTN_ID of this table specifies which employee can see this contact in his My Contact List. Associating a position to a contact is mandatory, if you do not associate, the contact will not be visible on UI.
• S_PARTY: This is the parent table for S_CONTACT, S_CONTACT being the siebel extension table for S_PARTY. Whenever a new contact is loaded in siebel S_PARTY must have an entry of the contact. You cannot import a contact in siebel without populating this table.

The EIM table that we can use is EIM_CONTACT since this table populates all the above mentioned tables.

Loading Contact:

Step 1: Run the below query, this query will insert the contact details in the eim table.

INSERT INTO EIM_CONTACT

(

ROW_ID,

IF_ROW_STAT,

IF_ROW_BATCH_NUM,

CON_PERSON_UID,

CON_PRIV_FLG,

CON_BU,

CON_ACTIVE_FLG,

CON_COURT_PAY_FLG,

CON_DISACLEANSEFLG,

CON_DISPIMGAUTHFLG,

CON_EMAILSRUPD_FLG,

CON_EMP_FLG,

CON_FST_NAME,

CON_INVSTGTR_FLG,

CON_LAST_NAME,

CON_ONDMNDSYNCFLG,

CON_PO_PAY_FLG,

CON_PROSPECT_FLG,

CON_PTSHPCONTACTFL,

CON_PTSHPKEYCONFLG,

CON_SENDSURVEY_FLG,

CON_SPEAKER_FLG,

CON_SUPPRESSEMAILF,

CON_SUPPRESSFAXFLG,

CON_SUSPECT_FLG,

CON_SUSP_WTCH_FLG,

CON_VETERAN_FLG,

PARTY_UID,

PARTY_TYPE_CD,

ROOT_PARTY_FLG,

ADMIN_ADJ_FLG,

PC_POSTN_BU,

PC_POSTN_DIVN,

PC_POSTN_NAME,

PC_ROW_STATUS,

CB_BU

)

VALUES

(

1,

'FOR_IMPORT',

1,

'EIM_TEST_1',

'N',

'Default Organization',

'Y',

'N',

'N',

'N',

'N',

'N',

'Mohammed',

'N',

'Arif',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'EIM_TEST_1',

'Person',

'N',

'N',

'Default Organization',

'INDIA',

'ACCOUNT_MANAGER-123',

'N',

'Default Organization'

);

COMMIT;

Remember that while importing new contacts, you need to populate all the required columns along with the user key columns of all the 4 base tables mentioned above.

Step 2: Check the inserted records:

select count(1), if_row_stat, if_row_batch_num

from eim_contact where if_row_batch_num = 1

group by if_row_stat, if_row_batch_num;

Step 3: Run EIM job with below IFB configuration parameters:

[Siebel Interface Manager]

PROCESS = Import_Contact

[Import_Contact]

TYPE = IMPORT

BATCH = 1

TABLE = EIM_CONTACT

ONLY BASE TABLES = S_PARTY, S_CONTACT, S_POSTN_CON, S_CONTACT_BU

Provide other necessary parameters like username, password etc. if required.

Step 4: Once the job is successfully completed, Check the IF_ROW_STAT column of the EIM table by running the below query.

select count(1), if_row_stat, if_row_batch_num

from eim_contact where if_row_batch_num = 1

group by if_row_stat, if_row_batch_num;

Step 5: Check imported records from UI.

NOTE: For any kind of errors in running EIM job, check the log file placed at /siebsrv/log/ location on the server. To get appropriate messages in the log file you need to specify below parameters while running the EIM job from UI.

Trace Flags = 3

SQL Trace Flags = 8

Error Flags = 1

SBL-EIM-00204: Invalid configuration file (ifb) name.

SBL-EIM-00204: Invalid configuration file (ifb) name.

This is a very common error that is encountered when one runs the EIM job from GUI. Though the error indicates that there is a possible error in the name of the IFB file, it may not be true at all.

I remember in my early career when I was new to EIM, i faced this error though the ifb file name was absolutely correct. I was perplexed as what went wrong with the name. I checked the name of the ifb file name almost 10 times, checking for space etc.

Finally I opened the ifb file and found a character '5' just before the mandatory parameter [Siebel Interface Manager], something like this:

5[Siebel Interface Manager]
      PROCESS = Import Product

Siebel EIM bookshelf says "The first nonblank, noncomment line of the configuration file’s header section must contain the exact information shown:
[Siebel Interface Manager]"

The character was accidentally written over there when i was making changes to the batch number for test load. So silly was the mistake but the error message popped out by EIM was sillier! :-)

Recommended number of rows that can be loaded in a single batch

What is the recommended number of rows that can be loaded in a single batch?

1. For an initial load, you can use 30,000 rows for a large batch.
2. For ongoing loads, you can use 20,000 rows for a large batch.
3. You should not exceed 100,000 rows in a large batch.
4. Furthermore, you should limit the number of records in the interface tables to those that are being processed. 
5. For example, if you have determined that the optimal batch size for your system is 19,000 rows per patch and you are going to be running 8 parallel EIM processes, then you should have 152,000 rows in the interface table. 
6. Under no circumstances should you have more than 250,000 rows in any single interface table, since this will have a tremendous negative performance impact.

NOTE: The number of rows you can load in a single batch may vary depending on your physical machine setup. To reduce demands on system resources and improve performance, you should always try to use smaller batch sizes.

Data integrity issues in EIM delete and merge process

EIM Merge and Delete processes should use only one commit per batch to avoid data integrity issues

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

Important Considerations in Siebel EIM

1. For import and merge processes, you must populate the ROW_ID, IF_ROW_STAT, and IF_ROW_BATCH_NUM columns in the EIM tables. This also must be done for delete processes when you run DELETE EXACT. For merge processes, you also need to populate the IF_ROW_MERGE_ID column. Do not populate these required columns with spaces because a space does not equal a NULL value.

2. Manually creating mappings to an existing Siebel base column in Siebel Tools is not supported.

3. Before you can create an explicit primary mapping, both the parent and the primary child table must be mapped to the same EIM table. If an explicit primary mapping exists, you can use EIM to set the primary explicitly during import or update by setting the primary flag column in the EIM table.

4. EIM_NOTE_DEL and EIM_SKLI_DEL are special EIM tables used for deleting from the S_NOTE* and S_*SKILL_IT tables, which do not have the normal U1 user key.

5. If you are planning to use Unicode in your implementation, then the EIM configuration file must be saved as a Unicode text file.

6. If the batch number component parameter is set to 0, the batch number in the EIM configuration file (if any) is used. This is the only exception to the parameter hierarchy.

7. If LOG TRANSACTIONS TO FILE parameter is set to TRUE, you must make sure that the Siebel Server can write to the file system’s eim directory. During installation, the file system directory must be specified using the Uniform Naming Convention (UNC).

8. PASSWORD PARAMETER in IFB file: If you start EIM from the command line, it uses the user name and password you used to log into the srvrmgr. If you start EIM from the Siebel application, EIM looks for the user name and password in the EIM Server Component parameters first, and if they are not specified, EIM then looks in the .IFB file. If EIM cannot find the user name and password in those places, EIM cannot log into the database and it fails. If you do not want your user name and password visible in the .IFB file, then specify them in the EIM Server Component parameters.

9. USERNAME PARAMETER in IFB file: If you start EIM from the command line, it uses the user name and password you used to log into the srvrmgr. If you start EIM from the Siebel application, EIM looks for the user name and password in the EIM Server Component parameters first, and if they are not specified, EIM then looks in the .IFB file. If EIM cannot find the user name and password in those places, EIM cannot log into the database and it fails. If you do not want your user name and password visible in the .IFB file, then specify them in the EIM Server Component parameters.

10. If your configuration file has more than one process section and you want a certain parameter to act on more than one process, you must include the parameter setting within each of the process sections that correspond to the processes on which you intend for the parameter to act.

11. It is best not to use COMMIT EACH PASS parameter in delete processes. This is because if a commit occurs after each table or each pass in a delete process, then in case of errors causing exit from the process, you can be left with orphan records and dangling references. If the commit occurs for the whole batch, then in case of errors, you can roll back other table deletes.

12. It is best not to use COMMIT EACH TABLE parameter in delete processes. This is because if a commit occurs after each table or each pass in a delete process, then in case of errors causing exit from the process, you can be left with orphan records and dangling references. If the commit occurs for the whole batch, then in case of errors, you can roll back other table deletes.

13. INCLUDE parameter can be used only in shell processes. A shell process uses the INCLUDE statement to invoke a sequence of processes in a single run.

14. SESSION SQL parameter cannot be used to insert or update data in Siebel base tables. EIM sends the SQL statement directly to the database and may cause data loss for Siebel Remote and Siebel Replication Manager.

15. For performance reasons, you should limit the number of tables to export or merge in a single process section to five tables or fewer.

16. In IFB file, when the backslash is followed by a space, EIM interprets the space character as “escaped,” and the new line character then terminates the parameter definition. This can generate an error message indicating the parameter definition is incomplete.

17. You can use EIM to update only non-user key columns; EIM does not support modification of existing user key columns. To update user key columns in S_ORG_EXT, S_PROD_INT, S_PROD_EXT, S_PARTY tables use EIM_ORG_EXT_UK, EIM_PROD_INT_UK, EIM_PROD_EXT_UK, and EIM_PARTY_UK. The postfix UK denotes user key.

18. 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.

19. If the data exists in a database that uses a different character set, the import process does not work properly until you recreate the database.

20. Siebel EIM tables contain several special columns that must be populated before rows can be imported.

21. If you have active mobile Web clients, do not disable the Enable Transaction Logging system preference in the Administration - Siebel Remote screen. If you disable this system preference, the server database and mobile Web client databases will not be synchronized after the import.

22. The ONLY BASE TABLES, IGNORE BASE TABLES, ONLY BASE COLUMNS, and IGNORE BASE COLUMNS parameters can be used to improve EIM performance.

23. COMMIT EACH PASS works cumulatively with COMMIT EACH TABLE. If you set both COMMIT EACH PASS and COMMIT EACH TABLE to TRUE, a commit will occur at the end of each pass and at the end of each table.

24. COMMIT EACH TABLE works cumulatively with COMMIT EACH PASS. If you set both COMMIT EACH PASS and COMMIT EACH TABLE to TRUE, a commit will occur at the end of each pass and at the end of each table.

25. COMMIT OPERATIONS parameter is useful only for row-by-row processing (with transaction logging on). It is not used for set-based processing operations.

26. The INSERT ROWS parameter must be set to FALSE for any table with an EIM table that does not have mappings to all its required columns, such as S_ORDER for EIM_ORDER_DTL. In this example, when EIM is not able to resolve the EIM_ORDER_DTL row to an existing S_ORDER record, it attempts to insert it as a new S_ORDER record. Since EIM_ORDER_DTL does not have mappings to all the S_ORDER required columns, the process fails with a “Cannot insert null” error.

27. MISC SQL is intended for initial data loading only (with DOCKING TRANSACTIONS = FALSE), because when using MISC SQL to set primary child foreign keys, NO transactions are logged for mobile users.

28. NET CHANGE = TRUE does not work for long columns. If you want to update a long column, you must use NET CHANGE = FALSE.

29. No custom values are allowed in the PARTY_TYPE_CD column. This column must contain one of the values listed above.

30. The PAR_PARTY_ID field needs to be populated only when the PARTY_TYPE_CD is set to Organization or Position. For Positions, if the record is a position that is the child of another position, then PAR_PARTY_ID needs to be populated with the ROW_ID of the parent position. In the case of Organizations, this field applies only to internal organizations. Similarly to Positions, the PAR_PARTY_ID needs to be populated with the parent organization if it has one. Also note that Divisions and Accounts have PARTY_TYPE_CD set to Organization well, but it is not necessary to populate the PAR_PARTY_ID field.

31. Transaction logging does not occur during export operations because Siebel base table values are not modified.

32. For performance reasons, you should limit the number of tables to export in a single process section to five or less.

33. Rows from child tables of related child tables are not exported until they have been mapped. EXPORT MATCHES WHERE clause fragment.

34. Complex SQL WHERE clauses like sub queries are not supported. EXPORT MATCHES can be used only against a target base table, or against a non-target base table that is an extension table of S_PARTY when the target table is S_PARTY.

35. The column names included in the criteria (that is, in “(...criteria...)”) must be columns from the target base table or the table that is specified for the EXPORT MATCHES parameter.

36. Do not use EIM to delete organizations. Using EIM to delete data from the Products base tables is also not recommended and can lead to inadvertent data integrity loss.

37. If the record to be deleted is a parent, the child records are affected as described above. However, if a non-required foreign key is part of the user key and clearing it will create a conflict, then the record will be deleted.

38. Because the delete process affects the contents of base tables, transaction logging should be in effect during delete operations if you have active mobile Web clients, so that the appropriate transactions are captured for later docking.

39. When you are deleting records based on user keys, specify the parameter DELETE EXACT in the .IFB file.

40. You must use one of the following DELETE parameters described in this section: DELETE EXACT, DELETE MATCHES, or DELETE ALL ROWS.

41. Do not use ONLY BASE TABLES with the target base table and non-target base tables, because the EIM table record cannot specify just one record to be deleted.

42. Do not use the DELETE MATCHES parameter to delete rows from S_PARTY based tables. For example, using the criteria "DELETE MATCHES = S_PARTY, (CREATED > xxxxx)" will cause all records of S_PARTY that matches this criteria to be deleted from the database.

43. Use the DELETE ALL ROWS = TRUE setting with extreme caution. It will delete all rows in the named base table including any seed data. Do not remove unnecessary seed data by deleting all rows from the S_LST_OF_VAL base table. If you do so, you will not be able to reimport “clean” data and you will be forced to rebuild the seed data or restore from backup. To selectively delete rows, use the DELETE EXACT or DELETE MATCHES expressions.

44. Do not use ONLY BASE TABLES with the target base table and non-target base tables, because the EIM table record cannot specify just one record to be deleted.

45. Use the DELETE ALL ROWS = TRUE setting with extreme caution. It will indeed delete all rows in the target base table.

46. Using EIM to merge data in the Products and Positions base tables is not recommended and can lead to inadvertent data integrity loss.

47. Because the merge process affects the contents of base tables, transaction logging should be enabled during merge operations if you have active mobile Web clients, so that the appropriate transactions are captured for later synchronization.

48. For performance reasons, you should limit the number of tables to merge in a single process section to five or less.

49. EIM will ignore this parameter if Enable Transaction Logging is unchecked in the Remote System Preferences view of the Administration - Siebel Remote screen.

50. In Merge operation, use the UPDATE ROWS = <Table_Name>, FALSE setting carefully. Inappropriate use can result in dangling foreign key pointers.

51. EIM behavior, whether executed from the GUI or through an EIM run, does not merge data in the base record. It simply repoints the foreign keys in the dependent child records. This applies to all columns in the base table. This could lead to unintended data loss in an extension column.

52. Activating flags (Error, Trace, SQL Trace Flags) will have a direct effect on performance. Typically, activating flags should only be done when testing EIM processes. Avoid activating flags in a production environment unless absolutely necessary.

53. Do not use the IGNORE BASE COLUMNS parameter for merge processes or export processes. This parameter should only be used for import processes and delete processes.

54. If you run EIM jobs in parallel on the same base tables, you might encounter unique constraint errors if you have the same values for the unique index fields in batches being processed by two different EIM jobs.

55. Running EIM processes in parallel on a DB2 database may cause a deadlock when multiple EIM processes access the same EIM table simultaneously. To avoid this potential problem, set the UPDATE STATISTICS parameter to FALSE in the EIM configuration file. The UPDATE STATISTICS parameter is applicable only for DB2.

Run EIM mapping wizard

How should users run the EIM Table Mapping Wizard in Siebel Tools?

 

To map a new table an EIM interface table using the EIM Table Mapping Wizard

1. Lock the project.
 

2. Select Table object type in the Object Explorer.
 

3. Select an entry in the Object List Editor. Choose from the list a base table that will be mapped to an EIM Table. It will be the primary table into which data from the new IF table will be imported. This wizard is only available to tables of type Data (Public) and Data (Intersection), and Data (Extension), and Data (Extension-Siebel).
 

4. Right-click and select EIM Mapping Table from the menu. The Interface Mapping dialog box is displayed with the Base Table name field populated with the selection made in the Object List Editor.
 

5. In the "Edit the Column name prefix" field, enter a distinguishing prefix, similar to "CON" for contact or "ACCT" for account. This prefix will be pre-appended to specified EIM Interface Table Columns related to the Target Table.
 

6. In the "Select an interface table" field, select from the pick list. The pick list for selecting the EIM Interface Table that will be mapped is constrained to show the following:
a. Those standard interface tables that are mapped to tables to which the new custom table has a foreign key.
The list is sorted by EIM table name. In the list candidate interface tables are displayed.
 

7. Click next on the Interface Table Mapping dialog box. The Summary dialog box appears with a summary of the choices made.
 

8. Click Finish on the Summary dialog box to accept the choices made and generate the EIM Interface Table object.
Based on this information, the wizard creates new EIM table mapping objects and adds several child objects to an existing EIM interface table object:
 

·         EIM Interface Table Column

·         EIM Table Mapping

·         Attribute Mapping

·         EIM Explicit Primary Mapping

·         Foreign Key Mapping

·         Foreign Key Mapping Column

Foreign key value issue while importing

What should be done when Enterprise Integration Manager fails with message “This is a foreign key value in the base table and the values in the interface table did not resolve to existing values”?

Integration Manager (EIM) reports the following low-severity error when the foreign key value in the base table does not match with value in the interface table (Note: The following example is based on Siebel version 7.0.4 data model):

EIM_ORDER

------------

BL_ACCNT_BI

BL_ACCNT_LOC

BL_ACCNT_NAME

BL_ADDR_NAME

Base table:

S_ORDER

-----------

BL_ADDR_ID (Position)

This is a foreign key value in the base table and the values in the interface table did not resolve to existing values. Verify that the IF columns correspond to existing base table rows. This failure caused the rows to be eliminated from further processing for this secondary base table. However, processing of the rows WILL continue for other destination base tables.

Resolution:

The essential concept of resolving the foreign key value is to find the user key columns in the foreign key table. Based on multiple columns, user keys are used to uniquely identify a row in a table for EIM processing. The following guideline illustrates how the user key plays a role to identify the base column for corresponding EIM columns based on the above scenario.

Identify the foreign key table, which S_ORDER.BL_ADDR_ID points to. Siebel Tools or Siebel Interface Reference guide can be used to identify the foreign key table.

1. Using Siebel Tools, in the Object Explorer list, go to Table object and query for S_ORDER table

2. Navigate to Column object and query for BL_ADDR_ID column

3. Verify that the foreign key table value is S_ADDR_ORG.

4. Find the user key columns defined in the S_ADDR_ORG table.

5. Using Siebel Tools, in the Object Explorer list, go to Table object and query for S_ADDR_ORG table

6. Navigate to User Key object and select U1 index (S_ADDR_ORG_U1)

7. Navigate to User Key Column object and verify that the User Key columns for S_ADDR_ORG are ADDR_NAME and OU_ID (FK)

8. Identify the foreign key table, which S_ADDR_ORG.OU_ID points to using Siebel Tools.

S_ORG_EXT

9. Find the user key columns defined in the S_ORG_EXT table using Siebel Tools: NAME, LOC, BU_ID (FK)

10. Identify the foreign key table, which S_ORG_EXT.BU_ID points to using Siebel Tools: S_BU

11. Find the user key columns defined in the S_BU table using Siebel Tools: NAME

Based on the above result, following interface columns need to be populated correctly to resolve the S_ORDER.BL_ADDR_ID foreign key.

BL_ACCNT_BU: populated with S_BU.NAME value from step 6.

BL_ACCNT_LOC: populated with S_ORG_EXT.LOC value from step 4.

BL_ACCNT_NAME: populated with S_ORG_EXT.NAME value from step 4.

BL_ADDR_NAME: populated with S_ADDR_ORG.ADDR_NAME value from step 2.

 

How can I tune my EIM batches to improve performance?

 

You should try the following options to improve EIM performance:

1.    Verify that all indices exist for the tables involved.

2.    Limit tables and columns to be processed using ONLY BASE TABLES/COLUMNS configuration parameters to minimize EIM processing.

3.    Consider switching off transaction logging during the EIM run. This will definitely improve performance. However, it must be balanced with the need for mobile users to re-extract afterward.

4.    Try using different batch sizes. Large batch sizes are often not efficient.

NOTE: Although the limit of rows you can process in a batch depends on your physical machine setup, you should not use a batch size of more than 100,000 rows.

For import processes, and delete processes that use the DELETE EXACT parameter, it is recommended that you use around 20,000 rows in a single batch.

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.

5.  Perform regular table maintenance on interface tables. Frequent insert or delete operations on interface tables can cause fragmentation in the tables. Consult your database administrator to detect and correct fragmentation in the interface tables.

6.    Delete batches from interface tables on completion. Leaving old batches in the interface table wastes space and could adversely affect performance.

7.    Run independent EIM jobs in parallel. EIM jobs that have no interface or base tables in common can run in parallel.

8.    Set the USING SYNONYMS parameter to FALSE in the .ifb configuration file to indicate that account synonyms do not need to be checked.

9.    Avoid using the UPDATE PRIMARY KEYS parameter in the .ifb configuration file.

10.  If all else fails, set Trace Flags=1 and SQL Trace Flags=8 and rerun the batch. The resulting task log will allow you to identify slow-running steps and queries.