How are INDEX HINTS used in EIM?

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.

Perform testing with the IFB file parameter USE INDEX HINTS set to both settings (TRUE and FALSE). The default value for this parameter is TRUE. By setting this parameter to FALSE, EIM will not generate hints during processing. Performance gains can be realized if there are hints being generated that direct the database optimizer to use less than optimal indexes. EIM processing should be tested with both settings to determine which provides for better performance for each of the respective EIM jobs. Note that this parameter is only applicable for SQL Server and Oracle database platforms.

Debugging for IF_ROW_STAT value as NOT_ALLOWED

What is IF_ROW_STAT column?

IF_ROW_STAT column in an EIM table is among the mandatory system columns that need to be populated before running the EIM job. It's purpose is to reflect the EIM row status after EIM processing. You can populate this column with any alphanumeric value not exceeding 30 characters. Commonly we initially populate this column with something like "FOR_IMPORT", "FOR_DELETE", "FOR_MERGE" for import, delete and merge operation respectively. After EIM processing i.e. when the IFB runs this value changes for each record and is replaced by the row status, whether processed successfully or not. This is the column that you should look first to know the status of records after EIM job finishes.

What does NOT_ALLOWED in IF_ROW_STAT column mean?

In simple words Not Allowed means, EIM engine is trying to do some operation on base tables but one or more parameters in the IFB is not allowing it to do. For example, EIM fetches the user key combination from the EIM table and tries to find it in siebel base table and if no match is found it would try to insert the record in base table, but in IFB file you would have mentioned something like say INSERT ROWS = S_ORG_EXT, FALSE. So in this case EIM will throw error and IF_ROW_STAT will be populated as NOT_ALLOWED.

In short EIM, is trying to do something and IFB is not allowing it. Simple as that.

This error can come in following scenarios/operations:

a) In Import operation, when EIM tries to insert records but IFB mentions that insert in the table is not allowed (as explained above).

b) In Import operation, when EIM tries to update records but IFB mentions that insert and update in the table is not allowed.

c) In Merge operation, when EIM tries to merge and update records but IFB mentions that update in the table is not allowed.

Illustration:

Lets take a simple example of updating Accounts through EIM_ACCOUNT table. Now, suppose you have populated all the user key columns and the columns that you need to update and your IFB looks something like this:

[Siebel Interface Manager]

PROCESS = "ACCOUNT_LOAD"

[ACCOUNT_LOAD]
TYPE = SHELL
INCLUDE = "UPDATE_ACCOUNTS"
[UPDATE_ACCOUNTS]
TYPE  = IMPORT
BATCH = 100
TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_PARTY, S_ORG_EXT
 ONLY BASE COLUMNS = S_PARTY.PARTY_UID, S_PARTY.PARTY_TYPE_CD, S_ORG_EXT.NAME, S_ORG_EXT.LOC, S_ORG_EXT.BU_ID, S_ORG_EXT.ALT_EMAIL_ADDR

INSERT ROWS = S_PARTY, FALSE
UPDATE ROWS = S_PARTY, FALSE
INSERT ROWS = S_ORG_EXT, FALSE
UPDATE ROWS = S_ORG_EXT, TRUE

Now since you do not want accounts to be loaded but only existing accounts records needs to be updated, you have put INSERT ROWS = S_ORG_EXT, FALSE and UPDATE ROWS = S_ORG_EXT, TRUE

EIM identifies the records in siebel by comparing the user key columns from EIM table. The error scenario is that if you do not populate the user keys properly in the EIM table or populate it incorrectly then EIM fails to find the record to update in the base table. This results in EIM attempting to create new records in siebel, but since you have mentioned as INSERT ROWS = S_ORG_EXT, FALSE EIM will not be able to do that, throwing an error of NOT_ALLOWED and terminates the process.

Resolution:

To resolve this error, there are 2 scenarios:

a) If your requirement is only to update and no insertion should happen then ensure that you populate the user keys correctly. All the user key combinations should already exists in siebel.

b) If your requirement allows the update as well as insert if account not found in siebel then, change the parameter INSERT ROWS = S_ORG_EXT, FALSE to
INSERT ROWS = S_ORG_EXT, TRUE or just remove this parameter, as the default is to insert and update.

Same conditions and resolution scenarios apply for merge process as well.

Debugging for IF_ROW_STAT value as PARTIALLY_IMPORTED

What is IF_ROW_STAT column?

IF_ROW_STAT column in an EIM table is among the mandatory system columns that need to be populated before running the EIM job. It's purpose is to reflect the EIM row status after EIM processing. You can populate this column with any alphanumeric value not exceeding 30 characters. Commonly we initially populate this column with something like "FOR_IMPORT", "FOR_DELETE", "FOR_MERGE" for import, delete and merge operation respectively. After EIM processing i.e. when the IFB runs this value changes for each record and is replaced by the row status, whether processed successfully or not. This is the column that you should look first to know the status of records after EIM job finishes.

What does Partially imported mean in EIM?

In simple words partially imported means, the EIM processed records successfully in the target base table but failed to process data in one or more non-target base tables.

Lets take a simple example of loading Accounts through EIM_ACCOUNT table. Since account is a party type entity, the target base table for EIM_ACCOUNT is S_PARTY where as S_ORG_EXT, S_ORG_BU, S_ACCNT_POSTN etc. are non-target base tables. Now, suppose you have populated all the user key columns, required columns and other attribute columns for accounts and you IFB looks something like this:

[Siebel Interface Manager]

PROCESS = "ACCOUNT_LOAD"

[ACCOUNT_LOAD]
TYPE = SHELL
INCLUDE = "IMPORT_ACCOUNTS"
[IMPORT_ACCOUNTS]
TYPE  = IMPORT
BATCH = 1000
TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_PARTY, S_ORG_EXT, S_ACCNT_POSTN, S_ORG_BU

And you ran the IFB and IF_ROW_STAT column value turned out to be PARTIALLY_IMPORTED. Now what does this mean?

There is primarily one reason for it and that is one or more of the non-target base tables mentioned in the ONLY BASE TABLES parameter didn't get processed because of some missing/incorrect column information. One example of it could be the VIS_BU column of EIM_ACCOUNT which corresponds to the BU_ID foreign key of S_ORG_BU. Simply put forward, to associate the correct organization to the account, VIS_BU and (ACCNT_BU, LOC, NAME --> for ORG_ID of S_ORG_BU) column set should be populated with correct data. Similarly if you miss to populate the position related data in EIM_ACCOUNT (POSTN_BU, POSTN_DIVN, POSTN_LOC, POSTN_NAME) you will face the partially imported error. But if you had not mentioned the S_ORG_BU or S_ACCNT_POSTN table in ONLY BASE TABLES parameter, this error would not have had come.

Resolution:

To resolve this error, just re-populate the correct data in respective EIM columns of base table foreign key columns and run the EIM job again. In this case populate the VIS_BU column or the position columns correctly.