Implementing Siebel Business Applications on DB2 UDB for z/OS > Understanding Siebel Partitioning > About Methods Used for Partitioning >

Partitioning for Even Data Distribution

This partitioning method involves partitioning a table using an additional partitioning column, PARTITION_COLUMN, designed specifically for Siebel partitioning with even data distribution. This column is populated with data using a BEFORE INSERT trigger option to generate the partitioning value for each row.

Most Siebel tables are tied together by the ROW_ID column from a parent table. Columns with an _ID suffix are used to define a parent-child relationship, for example, OU_ID. These columns might seem to be good partitioning candidates because they support the DB2 access path, but, in fact, these columns are poor candidates for partitioning because ROW_IDs are generated in sequential order. (An exception is if the values in a column with an _ID suffix identify groups of related records; in this case, the column might be a suitable partitioning key.)

To resolve the limitation caused by the sequential order of ROW_ID, Siebel 8.0 provides a column, PARTITION_COLUMN, which is used only for partitioning purposes. It is a physical column defined in a storage control file, but it is not a part of the Siebel repository. It is important that you continue to use the name PARTITION_COLUMN if the Siebel application is to recognize this column. You must also define this column as NOT NULL WITH DEFAULT.

A DB2 BEFORE INSERT trigger is used to populate the PARTITION_COLUMN values; it is defined in the Siebel storage control file. This trigger generates a random number between 00 and 10 and uses it to populate PARTITION_COLUMN. The partition limit keys are also numbers between 01 and 10. Rows are assigned to each of the 10 partitions based on the value of PARTITION_COLUMN generated for the row. For example, if the number generated for PARTITION_COLUMN is 6, the row is assigned to the partition with a limit key value of 6. By using a trigger to populate the columns, you can partition tables that do not have good candidate columns for a partitioning key due to their data content. Using a trigger, you can still generate values that distribute the data well.

The EIM tables and some of the prepartitioned base tables do not use PARTITION_COLUMN. For information on partitioning EIM tables, see EIM Tables and Partitioning.

When partitioning a nonpartitioned Siebel table, or when customizing a prepartitioned table, you need to assess whether PARTITION_COLUMN is the most appropriate partitioning key for the specific table. To improve query performance, ensure the partitioning key you use keeps related records together within each partition.

About Maintaining Even Data Distribution When Migrating to Unicode

In Siebel 7.x releases, the trigger that is used to populate PARTITION_COLUMN is based on a column from the parent table; random numbers are not generated to populate PARTITION_COLUMN. In addition, Siebel 7.x releases use an index-controlled partitioning scheme instead of the table-controlled partitioning scheme that is the default in Siebel 8.0.

When you upgrade from a pre-Siebel 8.0 release to Siebel 8.0, your existing partitioned tables continue to use index-controlled partitioning, and the PARTITION_COLUMN trigger is not changed. If you then migrate your Siebel 8.0 database to a Unicode encoding scheme, tablespace sizes increase and the sort order used changes. Because of this, for Siebel prepartitioned tables that are partitioned based upon PARTITION_COLUMN using the 7.x release trigger, you might have to modify the partitioning values you use to ensure balanced partitions after you migrate. If the limit key values are no longer appropriate, use the REORG REBALANCE utility to rebalance them.