What are the effects DB2 partitioning on performance and maintenance?

[Basil Cordeiro]

We have a DB2 V9 installed. We have a table with 14 GB data which is 10 yrs worth of data. The potential for growth is at the rate of 1-2 GB per year.

We are upgrading the application and a DBA has suggested to use partition table space instead of Simple table space. They are also suggesting 16 partitions of 64 GB each.

Here are our questions on this :
1. Does partitioning enhance access performance.
2. If the partition table is allocated to use 64 GB, are the allocations dynamic to only grow when additional data is stored or 64 GB is allocated by default per partition.
3. Does having more partitions have a drawback with respect to performance or maintenance.

[vijay2082]

Hi,

You can do a table level partitioning and partition across the tablespaces. Although I don't think that 14 GB table requires a partition approach unless the application logic wants one. If you are going to select dtaa based on year then it's worth to put 2-3 years data in a single tablespace and rest in others. It needs a proper planning and should be welcomed by the application approach.

Just partitioning for the sake of partition won't be much of help.

Answers to your question

1) Yes it does if we used a proper approach and we know what are we going to achieve.
2) Space allocation can be at the tablespace level, but we can dynamically put it across several tablespaces. you can add a new partition at any time.

3) Have partitioning approach such that a particular query retrieves maximum data from a single partition rather than spanning across several partitions.
Each tablespace should be on a different disk drive (Physical disk drives). Proper indexing and maintenace will result in a good performance if partitioning is worth needed.

Still I don't think the table in your case requires to be partitioned ( althhough we need to look closer at your application logic though)

Cheers, Vijay