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.