Implementing Siebel Business Applications on DB2 for z/OS > About Siebel Table Partitioning > Considerations in Partitioning Tables >

About Table-Controlled Partitioning and Using Indexes

It is recommended that you use table-controlled partitioning with Siebel database tables.

In the current Siebel release, partitioned tables use table-controlled partitioning by default. If you use table-controlled partitioning, partitioning is defined at the table level so you do not require a partitioning index. Maintaining indexes that are used for partitioning purposes only is no longer necessary, which reduces storage requirements and allows you to select a secondary index as the clustering index.

About Choosing a Clustering Index

In previous versions of DB2, the partitioning index was automatically the clustering index. In DB2 for z/OS Version 8 and later releases, data can be clustered by any index. Data in a partitioned table space no longer needs to be clustered by the partitioning index, although in many installations, this might be the most efficient option. For example, it is recommended that child tables are partitioned so that all records related by the parent table ROW_ID are contiguous within the same partition and a similar approach is recommended for intersection tables. By choosing the most appropriate clustering index for each of the Siebel-partitioned tables, you can optimize query performance.

In choosing a clustering index, consider both day-to-day online access to the Siebel tables and processes such as EIM, Siebel Remote, Assignment Manager, and Workflow. The recommended clustering index for each of the Siebel-partitioned base tables is shown in Table 8.

Only one clustering index can be defined for a table. When you have altered the index to make it clustering, run the REORG utility on the table space and then run RUNSTATS.

NOTE:  It is recommended that you do not partition secondary indexes for performance reasons.

About Using Partitioning Indexes

Although the prepartitioned Siebel tables use table-controlled partitioning, you can also define partitioning indexes for Siebel tables that are not partitioned using PARTITION_COLUMN. For information on the role of PARTITION_COLUMN in partitioning tables, see Partitioning for Even Data Distribution.

You might want to define a partitioning index for a Siebel table-controlled partitioned table to improve query performance, if, for example, the table and its associated index are very large. You can partition the index provided it has the same columns and limit keys as the partitioning key values defined for the table.

Partitioning indexes are distinguished from partitioned indexes. A partitioning index must:

• Contain all of the columns from the partitioning key, at a minimum

  NOTE:  A partitioning index can also contain additional columns, for example, columns required to support optimal clustering or performance enhancement.

• The columns must be in the same order
• The columns must have the same sort order

A partitioned index does not have the same partitioning key values as the key values on the table. Siebel Business Applications do not support partitioned indexes.

Partitioning indexes are defined in the storage control file templates for the prepartitioned Siebel base tables that do not use PARTITION_COLUMN. These tables are:

• S_CONTACT
• S_OPTY
• S_ORG_EXT
• EIM_ACCOUNT
• EIM_PROD_INT
• EIM_OPTY
• EIM_CONTACT
• EIM_SRV_REQ
• EIM_ACTIVITY
• EIM_DEFECT
• EIM_ASSET
• EIM_ACCNT_CUT
• EIM_FN_CONTACT1
• EIM_FN_ASSET
• EIM_FN_ASSET1
• EIM_FN_ASSET2
• EIM_FN_INSITM1
• EIM_FN_INSCLM1
• EIM_FN_ORGGRP
• EIM_ASSET_AT
• EIM_VHCL_SRV

For more information about the Siebel partitioned tables, see Prepartitioned Siebel Tables.