Tablespaces and Indexspaces

Performance Recommendations › Ways to Improve Performance › Tablespaces and Indexspaces

Tablespaces and Indexspaces

Segmenting tablespaces allows DB2 to map additional space for inserts. The mapping provides a more efficient method of finding available space for the inserts. The default installation sets the segment size to 64K for the following tablespaces:

DASC
DOBJ
DPRP
DSUBEX

Partitioning encyclopedia tablespaces enables parallel execution of DB2 utilities, in that any utility can be run on any single partition. This increases the overall throughput, especially in large encyclopedia sites.

However, the partition increments are very data dependent and site specific, and the values of these increments may require periodic adjustment as the distribution of data changes within the encyclopedia tables.

There are five tablespaces in the current host encyclopedia that benefit from partitioning. They are DASC, DOBJ, DPRP, DSUBEX and possibly DTXT depending on the textual information in models. Below is the Host Encyclopedia Partitioning Guidelines.

These tablespaces can be partitioned as follows:

Tablespace	Comments
DASC	Make the current unique clustering index (DASCI1) a unique, non-clustering index. Define a new, non-unique clustering and partitioning index on columns ASSOC_FROM_OBJ_ID, ASSOC_TYPE_CODE.
DOBJ	Partition using the current unique clustering index (DOBJI1).
DPRP	Partition using the current unique clustering index (DPRPI1).
DSUBEX	Make the current unique clustering index (DSUBEXI1) a unique, non-clustering index. Define a new, unique clustering and partitioning index on columns SE_SUBSET_ID, SE_OBJ_ID.
DTXT	Partition using the current unique clustering index (DTXTI1).

The encyclopedia installation makes use of the following DB2 features to reduce contentions:

Eliminate index-locking altogether by defining indexes as type 2 indexes.
Reduce data page locking by binding encyclopedia plans with CURRENTDATA (NO) parameter.

It is possible to reduce the contentions further by using row level locking (RLL) on DB2 objects that are subject to contentions. The DMAX table is a good candidate for row level locking.

The DB2 optimizer may not choose the correct path when the first key cardinality of DOBJI2 is small compared to the first key cardinality of DOBJI1. This situation occurs when an encyclopedia contains a small number of models with a large number of objects. To help influence DB2 to choose the optimal path, the encyclopedia installation process inserts 1300 rows into the DOBJ table. The rows contain negative numbers for the model IDs and object IDs.