• No replies
1 Post

Pinned topic Resize partitioned tables and initial sizing.

‏2011-06-06T23:52:07Z |
Hi All,

A question about table partition resize in DB2 9.7. I've read up on creating partitions and at the moment I'm not sure what partition size to set.

We are setting up a Operational Data Store, which contains the replicated changes from legacy systems. As new records enter the ODS, the old records are end dated. We will be using the end date of the record to partition, with the idea being that older end dates will be rolled out. However the client has very little data on transaction history, so we are unsure at what rate data will change in the tables.

The overall size of the ODS will be relatively small, so we have a few options:

1. Leave the tables unpartitioned while we gather transaction history.
2. Partition on small monthly partitions, if these are smaller than required, detach and read back into quarterly or yearly partitions.
3. Partition on quarterly or yearly, detach and read into smaller partitions if required. (This seems more involved)

Is there a simple way to resize partitions? Form what I've read it requires the user to detach the partitions, create new partitions and read the data back in. Is there anything easier?

One last question. Thanks for getting this far, Is there a best practices with consistent partition sizing across a database? Reference tables will have very little data changing, while transaction tables will be much higher. Seems a one size fits all wouldn't work, but maintenance on various sized partitions would be more difficult to administer.

Thanks and apologies for the long post.