Before deciding whether or not to use a DB2 partitioned table space, it’s wise to weigh the pros and cons. The following applies to partitioning in general and all types of partitioning, including index-controlled, table-controlled, and universal partition-by-range.
Advantages of a Partitioned Table Space
Each partition can be placed on a different disk volume to increase access efficiency. Of course, this is more difficult, if not impossible, to accomplish with RAID storage devices, so this doesn’t generally apply to shops using modern disk storage arrays.
Partitioned table spaces can be used to store large amounts of data. The maximum size of segmented table spaces is 64GB.
START and STOP commands can be issued at the partition level. By stopping only specific partitions, the remaining partitions are available to be accessed, thereby promoting higher availability.
Free space (PCTFREE and FREEPAGE) can be specified at the partition level, enabling the DBA to isolate data “hot spots” to a specific partition and tune accordingly.
Query I/O, CPU, and Sysplex parallelism enable multiple engines to access different partitions in parallel, usually resulting in reduced elapsed time. DB2 can access non-partitioned table spaces in parallel, too, but partitioning can optimize parallelism by removing disk contention.
Table space scans on partitioned table spaces can skip partitions that are excluded based on the query predicates. Skipping entire partitions can improve overall query performance for table space scans.
By mixing clustering and partitioning, you can design to decrease data contention. For example, if the table space will be partitioned by DEPTNO, each department (or range of compatible departments) could be placed in a separate partition. Each range of departments is in a discrete physical data set, thereby reducing inter-departmental contention due to multiple departments co-existing on the same data page.
You can further reduce contention by creating Data Partitioned Secondary Indexes (DPSIs). Prior to V8, some contention remained for data in non-partitioned indexes. Defining a Non-Partitioned Secondary Index (NPSI) on a table in a partitioned table space causes you to lose some of the benefits of partition-level independence for utility operations because access to an NPSI isn’t broken apart by the partitioning scheme.