Bookshelf Home | Contents | Index | PDF |
Implementing Siebel eBusiness Applications on DB2 UDB for z/OS and OS/390 > Understanding Siebel Partitioning > About Methods Used for Partitioning > Scenario for PartitioningIf you were using table S_ADDR_ORG, the column OU_ID might appear to be a good candidate to define a clustering or partitioning index. However, OU_ID contains data in the Siebel row ID format. Row IDs are generated in ascending order, but a trigger extracts the last two bytes of the OU_ID column and stores this value in a new physical PARTITION_COLUMN. In a storage control file, the partitioned table space is defined with two types of storage control file objects: Tablespace and Tspart (table space partitions). The storage control file objects related to partitioning are:
A table definition always includes the same options, whether the corresponding table space is partitioned or not. However, the table space and index include different options if partitioned or nonpartitioned. The following examples reflect a partitioning scenario for the S_ADDR_ORG table that resides in H0401000 partitioned table space. The Siebel partitioning template used for this scenario is storage_p.ctl. NOTE: The object numbers that identify the following examples might be different for your implementation. The example for object 6074 illustrates the Table object definition for S_ADDR_ORG: [Object 6074] The definition for Tablespace object [Object 3197] You can easily identify a partitioned table space by reviewing the Partitions option in the Tablespace object of the storage control file. If the Partitions option is greater than zero, then the table space is partitioned and requires additional objects, such as Tableparts (Tspart) and partitioning index. The number of Tspart objects is equal to the number of partitions. In the example for object 3197, illustrated above, [Object 3198] The storage control file definitions for the partitioned table space in the previous example result in the following output DDL statements: CREATE,TABLESPACE H0401000 IN SIDB0401 USING STOGROUP SYSDEFLT A partitioning index definition in the storage control file requires two types of objects: IndexBase and IndexPart (index partitions). The new partitioning index S_ADDR_ORG_P99, containing PARTITION_COLUMN, is defined in the storage control file for S_ADDR_ORG. [Object 8386] The example for object 8386 contains a physical partitioning column and a corresponding DB2 before insert trigger. These are always defined by the SpecialCol keyword in an IndexBase section. The syntax for the trigger implements the column PARTITION_COLUMN to S_ADDR_ORG as CHAR(2) NOT NULL with default of a space. It also implements a partitioning index and a trigger to populate the partitioning column. In the example for object 8386, the IndexBase section defines ten (10) partitions. Therefore, it requires ten IndexPart objects. These are illustrated in the example for objects 8387 through 8396: [Object 8387] The storage control file definitions in the previous example result in the following output DDL statements: CREATE INDEX SIEBTO.S_ADDR_ORG_P99 ON SIEBTO.S_ADDR_ORG The trigger name, PTH0401, is based on a number assigned to the S_ADDR_ORG table within the Siebel repository. The S_ADDR_ORG table is defined in table space H0401000. The trigger name and the table space name are both based on the number assigned to the S_ADDR_ORG table and stored in the table Group Code in the Siebel repository. Notice that the source column OU_ID is added as the second column of the index. This makes sure that related records (records with same OU_ID) are inserted and stored in physical clustered order. If the original clustering index contains more than one column, then these too could be added to the partitioning index to sustain clustering order. In order to sustain clustering order, it is recommended that you schedule routine Reorgs of the table space and index space. Regularly scheduled Reorgs accommodate insert activity and reclaim PCTFREE and FREEPAGE definitions. CAUTION: For most processes, the Siebel Row ID is generated as a BASE 36 value that contains the alphabetical characters A through Z and the numeric characters zero through nine (0 - 9). The EIM process uses a suffix that contains numeric characters zero through nine, so consider this when you define the key ranges. When you are defining key ranges, remember that EBCDIC and ASCII sorting orders are different. Numeric characters precede alphabetical characters in ASCII, but it is the opposite in EBCDIC. About the Performance Overhead of Using a TriggerThe performance overhead of using a trigger is measured as a percentage of the inserts. Performance overhead depends on several factors, including row length and the number of indexes in the table. Triggers that use the S_PARTY partitioned table space, 10 partitions, 14 columns, and 443 bytes with 8 indexes, have demonstrated a performance overhead of less than one percent per insert. |
Implementing Siebel eBusiness Applications on DB2 UDB for z/OS and OS/390 |