Implementing Siebel Business Applications on DB2 UDB for z/OS > Understanding Siebel Partitioning > Example of Partitioning: the S_ADDR_ORG Table >

Example of an Index Definition

If you use the table S_ADDR_ORG, the column OU_ID appears to be a good candidate for defining 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. The new partitioning index S_ADDR_ORG_P99, containing PARTITION_COLUMN, is defined in the storage control file for S_ADDR_ORG.

A partitioning index definition in the storage control file requires two types of objects: IndexBase and IndexPart (index partitions). An example definition for each is shown.

IndexBase Definition

[Object 8386]
Type = IndexBase
Name = S_ADDR_ORG_P99
Table = S_ADDR_ORG
Partitions = 10
SpecialCol = PARTITION_COLUMN WCHAR(2) NOT NULL WITH 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 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.

IndexPart Definition

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]
Type = IndexPart
Name = S_ADDR_ORG_P99
Table = S_ADDR_ORG
PartNum = 1
LimitKey = '3'
...
[Object 8396]
Type = IndexPart
Name = S_ADDR_ORG_P99
Table = S_ADDR_ORG
PartNum = 10
LimitKey = X'FF'

Example of the DDL for the Partitioning Index

The storage control file definitions for the partitioning index 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 ('3') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 PCTFREE 30
FREEPAGE 4 ,
PART 2 VALUES ('7') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 144 PCTFREE 30
FREEPAGE 4 ,
PART 3 VALUES ('B') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 PCTFREE 30
FREEPAGE 4 ,
PART 4 VALUES ('F') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 PCTFREE 30
FREEPAGE 4 ,
PART 5 VALUES ('I') 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 ('R') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 PCTFREE 30
FREEPAGE 4 ,
PART 8 VALUES ('V') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 PCTFREE 30
FREEPAGE 4 ,
PART 9 VALUES ('Z') USING STOGROUP SYSDEFLT PRIQTY 48 SECQTY 1440 PCTFREE 30
FREEPAGE 4 ,
PART 10 VALUES (X'FF') 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.

As shown in the previous example, the source column OU_ID is added as the second column of the index. Using this column order ensures that related records (records with the same OU_ID) are inserted and stored in physical clustered order. If the original clustering index contains more than one column, then these too can be added to the partitioning index to sustain clustering order. Schedule routine REORGs of the table space and index space regularly to sustain clustering order. Running REORGs regularly accommodates insert activity and reclaims PCTFREE and FREEPAGE definitions.

When you are defining key ranges, remember that EBCDIC and ASCII sorting orders are different. Numeric characters precede alphabetical characters in ASCII, but the opposite applies in EBCDIC.

NOTE:  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 take this into account in defining the key ranges.