MISC SQL parameter is introduced in Siebel 7 to allow EIM to set the following Primary Child Foreign Keys:

S_CONTACT.PR_OU_ADDR_ID
S_CONTACT.PR_HELD_POSTN_ID
S_ORG_EXT.PR_BL_PER_ID
S_ORG_EXT.PR_SHIP_PER_ID
S_POSTN.PR_POSTN_ADDR_ID
S_POSTN.PR_EMP_ID

The SQL can be derived <Explicit/Implicit Primary>.<Table Name>.<Column Name>
Similarly for Siebel 7 Financial Applications <Explicit/Implicit Primary>.FINS.<Table Name>.<Column Name>

For example if to set primary in Siebel 7 Horizontal for S_CONTACT.PR_HELD_POSTN_ID:

For explicit primary the parameter included in the IFB is
MISC SQL = EXPR_S_CONTACT_PR_HELD_POSTN_ID

And for similarly for Implicit Primary the parameter is
MISC SQL = IMPR_S_CONTACT_PR_HELD_POSTN_ID

Similarly for Siebel 7 Financial Applications the SQL will be EXPR_FINS_S_CONTACT_PR_HELD_POSTN_ID and IMPR_FINS_S_CONTACT_PR_HELD_POSTN_ID.

Note: When using MISC SQL in Siebel 7 to set Primary Child Foreign Keys, EIM does NOT log any transactions for mobile users. Change Request 12-BGA6EK has been raised requesting providing this information in EIM documentation and also an "ALERT 453: When using MISC SQL in Siebel 7 to set Primary Child Foreign Keys, Enterprise Integration Manager (EIM) does NOT log any transactions for mobile users" has been published

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

1. Before using the utility, the user needs to create the view S_EIM_MAP_V in the database. The database platform independent script for creating the view is create_EIM_MAP_V.sql and can be found in <dbsrvr>\common directory.

 2. The executable utleimdiff.exe is in <tools>\bin directory. Switches for the program are as follows:

/N New Siebel Repository Name (Required)
This is the name of the new repository.
/O Old Siebel Repository Name (Required)
This is the name of the old repository.
(enclose repository names in quotation marks, .i.e. /N "New Repository" /O "Old Repository")

/I Input Filename
This file contains the list of EIM tables that will be compared. The default input file of "eim_tbl_lst.inp" is shipped in the same directory. User can edit this file.

/M Report Filename (Required)
This is the output report. The default name is "eim_diff.txt"

/L Log Filename (Default: eim_diff.log)

 3. The output file contains three parts:
Part 1: Interface Table Difference
Part 2: Interface Table Mapping Difference
Part 3: Interface Column Mapping Difference

Part 1 compares all interface tables in the two repositories, whereas Part 2 and 3 compare the interface tables listed in the input file. The program may run for several minutes, depending on the number of tables to be compared.

The output file is self-explanatory. The first column of each part is the repository name. If there is an entry in one repository but not the other, then that means it exists in one repository but not the other. If there is the same entry in both repositories, then that means it has been modified. In Part 3, "UK" means "User Key sequence", and "Req'd" specifies whether the column in the base table is required or not.

Enterprise Integration Manager (EIM) interacts with data objects (tables and columns) via SQL statements. How those queries are executed by the database in use is directly related to the EIM performance. Since optimization modes used by Siebel application differ among database platforms, index hints usages by EIM are different.

There are two EIM parameters related to index hints:

 USE ESSENTIAL INDEX HINTS (TRUE by default)
USE INDEX HINTS (FALSE by default)

Below are suggested settings of the two parameters on different database platforms.

1. Oracle

USE ESSENTIAL INDEX HINTS = TRUE
USE INDEX HINTS = TRUE

2. MS SQL Server

USE ESSENTIAL INDEX HINTS = TRUE
USE INDEX HINTS = FALSE

3. DB2

Not applicable.

The syntax of EXPORT MATCHES and DELETE MATCHES has been changed in version 7.x./8.x This information was not available in the Siebel Bookshelf.

The following are the correct syntax for the EXPORT MATCHES and DELETE MATCHES parameters:

If the interface table's target table is not S_PARTY:

- It allows syntax of:

EXPORT MATCHES = <interface table name>, (...criteria...)
or
DELETE MATCHES = <interface table name>, (...criteria...)

EXPORT MATCHES = <target base table name>, (...criteria...)
or
DELETE MATCHES = <target base table name>, (...criteria...)

EXPORT MATCHES = (...criteria...)
or
DELETE MATCHES = (...criteria...)

- It does not allow syntax of:

EXPORT MATCHES = <non-target base table name>, (...criteria...)
or
DELETE MATCHES = <non-target base table name>, (...criteria...)

If the interface table's target table is S_PARTY:

- It allows syntax of:

EXPORT MATCHES = <target base table name, i.e. S_PARTY>, (...criteria...)
or
DELETE MATCHES = <target base table name, i.e. S_PARTY>, (...criteria...)

EXPORT MATCHES = <non-target base tables name of Siebel Extension type>, (...criteria...)
or
DELETE MATCHES = <non-target base tables name of Siebel Extension type>, (...criteria...)

- It does not allow syntax of:

EXPORT MATCHES = <interface table name>, (...criteria...)
or
DELETE MATCHES = <interface table name>, (...criteria...)

EXPORT MATCHES = (...criteria...)
or
DELETE MATCHES = (...criteria...)

To check whether a base table is of Siebel Extension type, do the following:

Login to Tools

Navigate to Table control and query a table name.

Check the Type property value. If it contains 'Extension (Siebel)', it means that this table is a Siebel Extension type table.

Note: The column names included in the criteria must be columns from the target base table or table that is specified for the EXPORT MATCHES parameter.

Strong caution is recommended when using DELETE MATCHES against non-target base table of Siebel Extension type (for example, S_ORG_EXT and S_CONTACT):

S_ORG_EXT contains records for accounts, divisions, organizations and partners.

To delete all accounts, but not divisions, organizations or partners, use the following .ifb setting:
DELETE MATCHES = S_ORG_EXT, (INT_ORG_FLG = 'N' AND PRTNR_FLG = 'N')

Refer to following SupportWeb posting on similar behavior:
Service Request 38-680047351: Using EIM to delete all Accounts
(The posting can be found by searching SupportWeb using search string: EIM AND DELETE AND EIM_ACCOUNT AND S_PARTY)

To delete all rows from S_ORG_EXT (including accounts, divisions and organizations), please use the following .ifb setting:

DELETE MATCHES = S_ORG_EXT, (NAME <> 'Default Organization')
(The seed data 'Default Organization' should not be deleted.)

S_CONTACT contains records for contacts and employees.

To delete all contacts, but not employees, use the following .ifb setting:
DELETE MATCHES = S_ORG_EXT, (EMP_FLG = 'N')

To delete all rows from S_CONTACT (including contacts and employees), use the following .ifb setting:

DELETE MATCHES = S_ORG_EXT, (ROW_ID <> '0-1')
(S_CONTACT record with ROW_ID = '0-1' is the seed data for Siebel Administrator, which should not be deleted.)

In Siebel application, follow the steps below to generate an Enterprise Integration Manager (EIM) detailed log file:

1. From User Interface, navigate to View > Site Map > Server Administration > Components

2. Select Enterprise Integration Manager as the component and choose the tab 'Component Event Configuration'.
 

3. Find the following 'Event Type':

SQL Tracing
SQL Summary
Task Configuration
Component Tracing.

Change their "Log Level" values to 4, 4, 4, 3 respectively.

(Note: It is not necessary to restart the Siebel Server to apply the changes. These settings will be active in the next EIM task execution.)

4. When running an EIM task, set the following flags for the task:

Error flags 1
SQL Trace Flags 8
Trace Flags 1

Afterwards a detailed log file will be generated under Siebel_Server\log directory.

Step 1-4 can also be performed from the srvrmgr command line. The following commands can be used to turn on above tracing levels from command line:

change evtloglvl SQLSummary=4 for component eim
change evtloglvl SQL=4 for component eim
change evtloglvl TaskConfig=4 for component eim
change evtloglvl Trace=3 for component eim
 

Also following parameters must be specified in the command used to run EIM task.

Srvrmgr> run task for component eim with config=<configfile.ifb>, TraceFLags=1, ErrorFlags=1, SQLFlags=8

The following command can be used to view existing event log levels for EIM component.

Srvrmg> list evtloglvl for component eim

Further information regarding event logging administration is available in Siebel Server Administration Guide, Chapter "Using the Server Manager GUI", Section "Event Logging Administration".

The account data need to be imported into S_PARTY, S_ORG_EXT, S_ACCNT_POSTN, and S_ORG_BU among other relevant tables. S_ORG_BU is a new table in Siebel 7 and must be populated for visibility in the "All Accounts" view.

If using Siebel Industry Solutions version 7.0.x (SIS) or Siebel Industry Applications version 7.5.x (SIA), there is no mapping in the EIM_ACCNT_CUT interface table to the S_ORG_BU table. However EIM_ACCOUNT and EIM_ORG_BU interface tables are mapped to S_ORG_BU. EIM_ACCOUNT or EIM_ORG_BU can be used to populate S_ORG_BU.

In SIS and SIA, MASTER_OU_ID in S_ORG_EXT must be populated for visibility in any of the Accounts view. If S_ORG_EXT.MASTER_OU_ID were not populated, the imported accounts would only be visible in Site Map > Data Administration >Accounts/Orgs view.

The imported accounts will not be visible in
Site Map > Data Administration >Accounts view or any other view including 'My Accounts', 'All Accounts', and 'All Accounts Across Organizations'. The column S_ORG_EXT.MASTER_OU_ID is not marked as a required column in SIS or SIA Interface Tables Reference (version 7). Change Request 12-B82K5U has been logged to enhance the documentation to reflect that MASTER_OU_ID is a required column in S_ORG_EXT for visibility in SIS and SIA.

When encountering  the Primary ID not populated during an EIM task even though the corresponding EIM mapping columns are appropriately populated in the Interface table issue modify the IFB file to include the PRIMARY KEYS ONLY = TRUE parameter. This will force EIM to update only the primary key values like PR_POSTN_ID, PR_EMP_ID etc.

For example: When using the EIM_POSITION Interface Table and updating the S_POSTN.PR_POSTN_ID primary child column. EIM verifies that the primary child table, S_POSTN, is mapped to the interface table, EIM_POSITION. It then use the ONLY BASE TABLES parameter in the IFB file to determine if this table meet the criteria and gets processed.

Example of an ifb section:

[Import POSITION_PERSON]
TYPE = IMPORT
BATCH = 100
TABLE = EIM_POSITION
ONLY BASE TABLES = S_PARTY, S_POSTN, S_PARTY_PER
PRIMARY KEYS ONLY = TRUE

Refer to the 'Siebel Enterprise Integration Manager Administration Guide  >
Chapter 'EIM Configuration File' > Section 'EIM Configuration File Parameters' > Table 'EIM Configuration File Parameters' for more information on this topic.