Implementing Siebel eBusiness Applications on DB2 UDB for OS/390 and z/OS > Understanding Siebel Partitioning > Partitioning Methods >

Scenario for Partitioning

If 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. Examples for this scenario are provided in the following pages.

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_group.ctl.

NOTE:  The object numbers that identify the following examples might be different for your implementation.

The example for object 954 illustrates the Table object definition for S_ADDR_ORG:

[Object 954]
Type = Table
Name = S_ADDR_ORG
Database = SIEBDB01
Tablespace = H0401000
Clobs = No

The definition for Tablespace object H0401000 is provided in the example for object 43:

[Object 43]
Type = Tablespace
Name = H0401000
Database = SIEBDB01
Define = No
Partitions = 10
Bufferpool = BP1
LockSize = Page

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 43, illustrated above, Partitions=10; therefore, ten Tspart objects must be defined (as shown in the example for objects 242 through 251).

[Object 242]
Type = Tspart
Name = H0401000
PartNum = 1
...
[Object 251]
Type = Tspart
Name = H0401000
PartNum = 10

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 SIEBDB01 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 DEFINE NO NUMPARTS 10
(PART 1 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 2 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 3 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 4 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 5 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 6 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 7 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 8 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 9 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ,
PART 10 USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 FREEPAGE 4 PCTFREE 30 COMPRESS NO ) BUFFERPOOL BP1 LOCKSIZE PAGE LOCKMAX 0
COMPRESS NO
/

A partitioning index definition in the storage control file needs 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 3198]
Type = IndexBase
Name = S_ADDR_ORG_P99
Table = S_ADDR_ORG
Partitions = 10
SpecialCol = PARTITION_COLUMN WCHAR(2) NOTNULL DEFAULT ' '
Function = WHEN (N.OU_ID IS NOT NULL) BEGIN ATOMIC SET N.PARTITION_COLUMN = RIGHT(N.OU_ID, 2); END
Column 1 = PARTITION_COLUMN ASC
Column 2 = OU_ID ASC

The example for object 3198 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 3198, the IndexBase section defines ten (10) partitions. Therefore, it requires ten IndexPart objects. These are illustrated in the example for objects 3234 through 3243:

[Object 3234]
Type = IndexPart
Name = S_ADDR_ORG_P99
Table = S_ADDR_ORG
PartNum = 1
LimitKey = 'B'
...
[Object 3243]
Type = IndexPart
Name = S_ADDR_ORG_P99
Table = S_ADDR_ORG
PartNum = 10
LimitKey = 'Z'

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
(PARTITION_COLUMN, OU_ID)
USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 PCTFREE 30 DEFINE NO
CLUSTER (
PART 1 VALUES ('B') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 2 VALUES ('C') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 3 VALUES ('E') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 4 VALUES ('G') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 5 VALUES ('K') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 6 VALUES ('N') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 7 VALUES ('P') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 8 VALUES ('R') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 9 VALUES ('T') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4 ,
PART 10 VALUES ('Z') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440
PCTFREE 30 FREEPAGE 4
) BUFFERPOOL BP2
/
CREATE TRIGGER SIEBTO.PTH0401 NO CASCADE BEFORE INSERT ON
SIEBTO.S_ADDR_ORG REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
WHEN (N.OU_ID IS NOT NULL) BEGIN ATOMIC SET N.PARTITION_COLUMN =
RIGHT(N.OU_ID, 2); END
/

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.

Trigger Performance Overhead

The 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 parts, 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 OS/390 and z/OS 
 Published: 18 April 2003