In a typical data migration application using EIM, the IFB file contains a large number of sub-processes, which are called from the main process (SHELL). Often while running the EIM process, there are situations, when we would like to run only a sub set of these processes. Such situations normally arise in testing environments, when we prefer running only those processes for which testing needs to be performed.

For example consider a section of a typical IFB File, which includes the following processes. 

[Import Position]

TYPE  = IMPORT

BATCH = 11100000-11100005

TABLE = EIM_POSITION

ONLY BASE TABLES = S_PARTY, S_POSTN

[Import Position Organization] 

TYPE  = IMPORT

BATCH = 11200000-11200005

TABLE = EIM_POSITION

ONLY BASE TABLES = S_PARTY, S_ACCNT_POSTN

USE INDEX HINTS = TRUE

INSERT ROWS = S_PARTY, FALSE

UPDATE ROWS = S_PARTY, FALSE

;***************************************************************************

; IMPORT

;***************************************************************************

[Import Everything]

TYPE = SHELL

INCLUDE = "Import Organization"

INCLUDE = "Import Organization Address"

INCLUDE = "Import Org-Address Relationship"

INCLUDE = "Import Position"

INCLUDE = "Import Position Organization"

INCLUDE = "Employee Position Rel"

Suppose, the logic for migrating Position Organization relationship has undergone some change. Hence the “Import Position Organization” Process needs to be tested. So, in the IFB file we comment out all other processes and keep only INCLUDE = "Import Position Organization" uncommented. We delete the records in the S_ACCNT_POSTN table, which stores the Position Organization Relationship, and then run the EIM process. This works fine.

However, suppose the logic for migrating Position data has undergone some change, which is why the “Import Position” Process needs to be tested. But in this case, if we comment out all other processes and run the EIM only for “Import Position”, after deleting records from S_PARTY and S_POSTN, then the Position Organization relationship data records would become orphans. Similarly, records in all other tables, which have a foreign key relationship with S_POSTN, would become orphan records. This is because every time we delete a Base table (either manually or through EIM), and then run the EIM process to migrate records into it, SIEBEL generates new ROW_IDs for each of these records. Hence the foreign key reference that other tables have with this record would be lost.

Hence the thumb rule that should be followed is:

When testing data migration for a Master table (which has foreign key relationship with one or more child tables), run the EIM process not only to import the Master table records, but also to import records to all tables that have foreign key relationship with the Parent table.

In our example, therefore if we need to test the migration of Position Master records into S_POSTN, we would need to run the EIM process for   “Import Position”, "Import Position Organization" and"Employee Position Rel" to ensure that foreign key references are not lost.

The settings for the TRACE FLAGS, SQL TRACE FLAGS and ERROR FLAGS can be specified either from the GUI or from the Command Prompt when starting an EIM task.

If the EIM process is being run from the GUI, then the flags can be set in the COMPONENT REQUEST PARAMETERS list in the COMPONENT REQUESTS View of the Enterprise Operations Screen.

If the EIM Process is being run from the command prompt, then the flags can be set within the command for running the EIM task, as shown in the sample below:

srvrmgr /g siebelserver /e AspireEnt753 /s SIEBELSERVER /u sadmin /p sadmin /c "run task for component eim with config=<<filename.ifb>>,Errorflags=1,Sqlflags=8,Traceflags=3

Note: Task parameters can override component-level event logging to create additional information in the log. In Siebel, parameters can be set, from highest to lowest, at Enterprise, Server, Component Definition, Named Subsystem, Sever Component and Task level. A parameter set at a lower level can override the same parameter set at a higher level.

Error Flags:

By setting the Error Flags to appropriate levels, a detailed explanation of rows that were not successfully processed, can be obtained.

Setting the task parameter "Error Flags" = 1 can be used to write a detailed description of the cause of each error to the task log during processing. 

SQL Trace Flags:

The SQL Trace flags parameter is used for logging SQL statements that make up the EIM task.

Setting the SQL Trace Flags parameter to 8 creates a log of the summary SQL statements that make up the EIM task. Since it dramatically impedes EIM performance and created a large log file, hence this option is recommended only for test environments. The lower values for SQL Debug Flags (1, 2, and 4) are used for logging at the ODBC level.

Trace Flags:

Trace flags contain logs of various EIM processing steps. Siebel 7.x onwards, Event Logging needs to be set for the EIM component, in order to activate Trace Flags. 

For settingEvent Logging, the following sequence needs to be followed: 

1. Click the Server Administration screen tab.
2. From the Show drop-down list, select Component.
3. Select Enterprise Integration Manager as the component.
4. Click Component Event Configuration.
5. Perform a query and enter the Log Level values for the following: 

 Trace Flags are bit-based. Available Trace Flags include 1, 2, 4, 8, and 32. To activate multiple trace flags, the Trace Flags parameter has to be set to the sum of individual trace flag numbers. For example, to log trace flags 2 and 4, the Trace Flags parameter needs to be set to 6.

 Setting Trace Flags to 1:

Setting the Trace Flags parameter to 1 creates a step-oriented log of the task. This can be used to determine the amount of time EIM spends on each step of the EIM task, or for each interface table processed.

 Setting Trace Flags to 2:

Setting the Trace Flags parameter to 2 creates a file log that traces all substitutions of user parameters.

 Setting Trace Flags to 4:

Setting the Trace Flags parameter to 4 creates a file log that traces all user-key overrides.

 Setting Trace Flags to 8:

Setting the Trace Flags parameter to 8 creates a file log that traces all Interface Mapping warnings.

 Setting Trace Flags to 32:

Setting the Trace Flags parameter to 32 creates a file log that traces all file attachment status. The trace file contains four labels, three of which are used to trace file attachment processes as described.

Note: Activating flags will have a direct negative effect on performance since, since a lot of information will be recorded in the log file. Typically, activating flags should only be done when testing EIM processes, and should be avoided in a production environment unless absolutely necessary.

When migrating data using the EIM, the process can fail due to several reasons. In certain cases, it is found that the EIM process was successful for migration into some of the Base tables, but failed for some other base tables. In other cases, records get imported into the base tables, but some of the columns fail migration. This section discusses the various causes of EIM failure, how they can be tracked and finally resolved.

 After EIM process is complete, the following SQL query has to be run, to check for possible errors: 

SELECT if_row_batch_num, if_row_stat, if_row_stat_num, count(*)

FROM <<EIM Table Name>>

GROUP BY if_row_batch_num, if_row_stat, if_row_stat_num;

Common Errors and Resolution 

The following chart lists the possible values of IF_ROW_STAT, what they indicate and some possible resolutions.

The unique identifier associated with every record in Siebel Enterprise databases is known as a Row ID. The column in which this value is found is ROW_ID and it is present on every table. The ROW_ID is unique for an entity. For example, the ROW_IDs for the same person in S_PARTY, S_CONTACT, and S_CONTACT_X are the same because they each refer to the same person.

Row IDs are used extensively throughout Siebel Enterprises to access specific records. Although users access records by a User Primary Key (such as Opportunity Name), it is more efficient for the Siebel Enterprise to store and access related data via the Row ID.  

The Row ID is a base-36 sequence number generated using a confidential, proprietary algorithm that ensures no duplication, thus protecting the referential integrity of the database. The ROW_ID column is VARCHAR(15), which may contain one or two non-numeric symbols (plus signs or hyphens, or both).

 The format of the ROW_ID is one of the following:

where:

The maximum length of the ROW_ID is 15 alphanumeric characters.

The corporate prefix will always be unique for any database (main or local). The server maintains its original value, and mobile databases created against that particular server database are always assigned a new, unique value.

The Siebel ROW_ID is a combination of the S_SEQUENCE_S and information from S_SSA_ID table.

All connected users share the same prefix, which is obtained from the table S_SSA_ID on the server database. Remote Users are assigned a unique prefix when they are db-extracted. This value is also stored in the S_SSA_ID table on the local database.

Suffix is generated using an internal algorithm handled by the source code. When a new record is created through the user interface, the Siebel application reads the value of the current NS column from S_SSA_ID table and increments this value by a value more than 1 – for performance reasons, generally 50. The client caches these fifty potential ROW_IDs for future inserts. A new record entered from the user interface may result in many inserts to the underlying tables, depending on the business components used. When the client disconnects, cached ROW_IDs are lost.

 The combination of the prefix and suffix generates a unique row_id.

 Since Siebel does not expose the algorithm behind generation of row ids, the ROW_ID generation is internal to the EIM process. Therefore Siebel provides user keys to map to a unique record in the base table.

 During EIM, the EIM table columns, which map to the user keys of the base table, are populated with values so as to map to a unique record. If that record is to be updated by EIM later, the same user key values have to be populated in the corresponding EIM table columns. Otherwise, it will not resolve into the correct base table record.

 For example, PERSON_ID and BU_ID constitute the user keys for S_CONTACT. The corresponding EIM_CONTACT columns are CON_PERSON_UID and CON_BU. When a new record is imported in S_CONTACT using EIM_CONTACT, the CON_PERSON_UID and CON_BU should uniquely identify a Contact record. Now, for updating some fields for this Contact using EIM_CONTACT, CON_PERSON_UID and CON_BU have to be populated with the same set of values that were used during the initial load in order to map to the same Contact record in the base table.

Similarly while loading a child table, which references the row id of the master table, the EIM table columns that map to the foreign-key column of the child table must be populated in the same way the user keys of the master table were populated.

For example, S_CONTACT_XM is a child table of S_CONTACT. The column PAR_ROW_ID of S_CONTACT_XM references the ROW_ID of S_CONTACT. S_CONTACT_XM is loaded by EIM_CON_DTL. The columns of EIM_CON_DTL that need to be populated to generate the PAR_ROW_ID of S_CONTACT_XM are CON_BU, CON_PERSON_UID and CON_PRIV_FLG. These EIM columns should be populated with the same set of values that were used to populate its parent S_CONTACT record through EIM_CONTACT. Note that though PRIV_FLG is not a part of the user key of S_CONTACT, but it is a required column and is part of the foreign-key mapping of its child table.

It is mandatory to load the required columns of the base tables. If one or more required fields are missed, then the EIM process will fail to load the base table and generate the REQUIRED_COLS error status. To identify which are the required columns for a base table, the following query can be used:

SELECT TBL.NAME, COL.NAME
FROM   S_COLUMN COL, S_TABLE TBL
WHERE  COL.TBL_ID=TBL.ROW_ID
AND    COL.REQUIRED='Y'
AND    TBL.NAME = <<give Base Table Name here>>;

Similarly to determine the user keys for a base table, the following query can be used:

SELECT distinct  TBL.NAME,
    COL.NAME
FROM     S_COLUMN COL,
      S_TABLE TBL
WHERE    COL.TBL_ID=TBL.ROW_ID
AND      TBL.NAME = <<give Base Table Name here>>
AND      COL.USR_KEY_SEQUENCE IS NOT NULL;

If the above query does not return any row, then that implies that the corresponding base table does not have any user key.

Using EIM task user cannot normally update User key attributes of the base table. However, there are some special interface tables EIM_ORG_EXT_UK and EIM_PROD_INT_UK that can be used to update user key columns for some key tables S_ORG_EXT and S_PROD_INT.

Updating user key columns using EIM task with these special tables will not update denormalized columns. For example, updating S_ORG_EXT.LOC will not update S_ACCNT_POSTN.ACCT_LOC AND S_ORG_BU.ORG_LOC.

Some base tables like Note tables do not have user keys. Since user keys play a vital role in migration of data using EIM, the EIM behavior is significantly different in case of Base Tables without user keys.
For such tables, although IMPORT and EXPORT processes work, but Merge process does not work.  The DELETE process, when using DELETE MATCHES and DELETE ALL ROWS parameters, works fine, but does not work with DELETE EXACT parameter. This is because the EIM process cannot identify the rows to delete, in the absence of user keys.

However, Siebel 7.5.x versions have made deletion of data from Notes tables, using a special EIM, EIM_NOTE_DEL. The EIM_NOTE_DEL interface table should only be used to delete notes from the S_NOTE_* tables. To use it, users need to populate ROW_IDs of records to be deleted into corresponding columns in the interface table and then use DELETE EXACT to delete them.

For further details, please refer the Other Issues Relevant to Notes Migration section of the NotesDataMigrationGuide

When migrating data to or from Siebel Base tables, one of the main tasks is to identify the Interface table that would be used in the EIM process. There are cases in which a base table can be loaded from more than one EIM Interface tables. In such cases, the choice of interface table could optimize the EIM performance significantly. ROW_ID and IF_ROW_BATCH_NUM are mandatory columns for EIM processing. 

Driving Factors while choosing EIM table 

Here is a list of all factors that should be kept in mind while choosing the EIM Table. 

Base table- Identify all the Base tables that need to be populated. This is driven by requirement. 

EIM table- Identify all the EIM tables that contain mappings to the Base Table. The query that will help to determine all the EIM tables that populate a particular Base table is: 

SELECT DISTINCT   T2.NAME BASE,

T1.NAME EIM

FROM              S_TABLE T1,

S_TABLE T2,

S_EIM_TBL_MAP MAP

WHERE             T2.NAME=<<give Base Table Name here>>

AND               T1.ROW_ID=MAP.IF_TBL_ID

AND               T2.ROW_ID=MAP.DEST_TBL_ID

AND               T1.INACTIVE_FLG='N'

AND               T2.INACTIVE_FLG='N'

AND               MAP.INACTIVE_FLG='N'

ORDER BY          T1.NAME;

Target Table- In the IFB file, while specifying the base tables that a particular EIM table will load, it is mandatory to specify the target base table for the EIM table. If the target table is not mentioned, EIM will generate an error. The following query determines the target base table for an EIM table:

SELECT DISTINCT   T1.NAME SOURCE,

T2.NAME TARGET

FROM              S_TABLE T1,

S_TABLE T2

WHERE             T1.TARGET_TBL_ID = T2.ROW_ID

AND               T1.NAME =<<give EIM Table Name here>>

ORDER BY          T1.NAME;

EIM Table Selection to load maximum base tables- When more than one related Base tables need to be populated, instead of selecting different EIM tables for each Base table, the optimal choice would be an EIM table that would contain mapping to all the related Base tables that need to be populated. The following query determines the Base tables that can be loaded from a particular EIM table:

SELECT      T1.NAME EIM,

T2.NAME BASE

FROM        S_TABLE T1,

S_TABLE T2,

S_EIM_TBL_MAP MAP

WHERE       T1.ROW_ID=MAP.IF_TBL_ID

AND         T2.ROW_ID=MAP.DEST_TBL_ID

AND         T1.INACTIVE_FLG='N'

AND         T2.INACTIVE_FLG='N'

AND         MAP.INACTIVE_FLG='N'
AND         T1.NAME =<<give EIM Table Name here>>
ORDER BY    T1.NAME;

For example, consider a situation where both the tables S_PARTY_PER and         S_POSTN_CON need to be populated for storing relationships between an Employee and a position. Since employee related data are usually migrated using EIM_EMPLOYEE, this interface table would seem like an obvious choice. But EIM_EMPLOYEE does not have mapping with S_POSTN_CON. So in this case we could 

1. Load S_PARTY_PER using EIM_EMPLOYEE and S_POSTN_CON using EIM_CONTACT.
2. Alternatively, we could use only EIM_CONTACT, since this interface table loads both S_POSTN_CON as well as S_PARTY_PER.

The choice ii would be optimal since our purpose is getting solved using a single EIM table.

EIM Table Selection to load maximum columns- Among these Interface tables, select the Interface table that contains mappings to the maximum number of Base table columns. The ideal choice would be the interface table that would have mapping to all the base table columns that have been identified for population.

For example, the base table S_CONTACT can be loaded using both EIM_EMPLOYEE as well as EIM_CONTACT.  But if our requirement indicates that a specific column, say, PR_HELD_POSTN_ID of S_CONTACT needs to be populated then we would have to choose EIM_EMPLOYEE because EIM_CONTACT does not have mapping to PR_HELD_POSTN_ID column of S_CONTACT.

However, if our requirement were to populate PR_POSTN_ID of S_CONTACT, then we would have to choose EIM_CONTACT and not EIM_EMPLOYEE because EIM_EMPLOYEE does not have mapping to PR_POSTN_ID column of S_CONTACT.