Q and A

Asked and Answered

Divide and Conquer

Is there a rhyme, reason, formula, or theory that can be used to determine the optimal number of tablespace partitions and their optimal size?

Robert Catterall responds:

There is no formula — at least as far as I know. Here are some considerations:

• If you have a big table with high availability requirements and you have one or more nonpartitioning indexes on that table, you might opt for more numerous, smaller partitions to minimize the duration of the BUILD2 phase of IBM's online REORG utility.
• If you compress data in partitioned tablespaces and you are tight on DBM1 virtual storage, you might want to opt for fewer, larger partitions, because the compression dictionary (64KB, I believe) for each open partition will be loaded into DBM1 extended private storage.
• The more partitions, the more parallelism you can get, which is important for certain batch processes (multiple concurrent batch jobs, each accessing a different partition or range of partitions) and for query parallelism.
• If a table stores time-series data, the number of partitions may be suggested by the number of time periods in the table (for example, each of 52 partitions could represent one week of one year or each of 180 partitions could represent one day in a table that holds approximately 6 months of data).

See a complete archive of reader/author Q&As .

Back to Divide and Conquer by Robert Catterall