Implementing Siebel Business Applications on DB2 UDB for z/OS > Understanding Siebel Partitioning > Partitioning Strategies for Special Types of Tables >

EIM Tables and Partitioning

To speed up Enterprise Integration Manager (EIM) load and reduce I/O (input and output) contention, spread partitions for EIM tables across the entire I/O subsystem. You can use the Siebel Database Storage Configurator to define partitions on EIM tables. For further information, see Modifying the Storage Control File.

NOTE:  The sample storage control files also contain partitioning of certain EIM tables. Before you use EIM, verify that you are using the correct key ranges, because key ranges depend on the batch numbers used.

It is recommended that you partition EIM tables based on their U1 indexes, that is, IF_ROW_BATCH_NUM and ROW_ID. This method of partitioning allows an EIM batch input to be assigned to one partition, thereby allowing multiple EIM batches to be run in parallel.

To compute the optimal number of partitions for an EIM table, divide the number of rows in the EIM table by the number of parallel processes you intend to run. The result is the approximate number of partitions you should create for the table.

The recommended partitioning index is usually the U1 index (IF_ROW_BATCH_NUM and ROW_ID) but in some cases, you might need to consider a different index for partitioning. In the following example, EIM_CONTACT is partitioned based on IF_ROW_BATCH_NUM and CON_LAST_NAME. The target base table S_CONTACT is also partitioned based on LAST_NAME. This results in less random I/O by DB2 while accessing the S_CONTACT table.

CREATE INDEX SIEBTO.EIM_CONTACT_P99
ON SIEBTO.EIM_CONTACT
( IF_ROW_BATCH_NUM ASC ,
CON_LAST_NAME ASC )

This special method applies only to target base tables that are partitioned based on the natural key—not by PARTITION_COLUMN, which is derived from ROW_ID. The following examples illustrate partitioning EIM tables for S_ORG_EXT and S_OPTY.

CREATE INDEX SIEBTO.EIM_ACCOUNT_P99
ON SIEBTO.EIM_ACCOUNT
( IF_ROW_BATCH_NUM ASC ,
NAME ASC ,
ACCNT_BI ASC ,
LOC ASC )

CREATE INDEX SIEBTO.EIM_OPTY_P99
ON SIEBTO.EIM_OPTY
( IF_ROW_BATCH_NUM ASC ,
OPTY_NAME ASC )

EIM Table Partitioning and Data Distribution

The mechanism by which EIM generates the ROW_ID can result in an uneven distribution of data if you use EIM with one of the default partitioning schemes to import data into a base table.

When EIM imports data into a base table, EIM automatically generates the base table ROW_ID by concatenating the prefix with a unique number; for example, 1-SB3-123. EIM relies on database functions to make sure that each number is unique under the same prefix.

Uneven data distribution occurs because one of the default partitioning approaches uses the last two characters of ROW_ID as the partition key for partitioning of base tables; these characters can be letters, numbers, or a combination of letters and numbers, but the last two characters in a ROW_ID generated by EIM are numbers.

If your storage control file is to use partitioning keys that reflect the nature of your data, consider your data distribution with regard to your EIM process before you begin your database installation. Then, modify your partitioning keys accordingly. Alternatively, you can repartition the table after an uneven distribution has occurred.