Are: DPF, MDC and TP (table level partitioning intro in version 9),
are they mutually exclusive ? That is if you create either a DPF or
MDC or TP tables but these partitioning features can not be
combined ? Can you for instance create a TP (table partition) table
on top of DPF, for example ?

We are consider building a large datawarehouse on AIX db2 9.5 running
DPF, about 2 TB to start and monthly roll-in/roll-out of about 150 MB.
We are choosing table design and I am a little confused about the
table partitioning choices in udb.

Thank you in advance. Richard.

"Richard" <rsl101 (AT) gmail (DOT) com> wrote

Yes, you can do all 3 at the same time. In fact (no pun intended) I would
expect almost all large data warehouse tables to use both DPF and Table
Partitioning at the same time. MDC is sometimes useful, but not always as
much as the other two, especially if you have a lot of different ways the
data will be retrieved, and/or your design is not rock solid (likely to
change).

There is one catch if you use Table Partitioning and want to have
partitioned indexes (only available in Version 9.7). In order to have
partitioned indexes, the PK (and any unique index) of the table must include
the Table Partitioning Column. And obviously the PK must also include the
DPF hash key column (which would be a column with high cardinality, unlike
the Table Partitioning key which is probably a date). So you end up with
some slightly weird (but entirely workable) PK's that contain a traditional
PK column plus the date column, even though the PK is unique without the
date column.

Partitioned indexes are only available in 9.7, so I would urge you to start
out with that release, or plan ahead by using the appropriate PK's that
will allow partitioned indexes when you upgrade in the future.

Richard wrote:
http://www.redbooks.ibm.com/abstract...7467.html?Open
http://www.ibm.com/developerworks/da...atabasedesign/

Larry Edelstein

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Richard wrote:
http://www.redbooks.ibm.com/abstract...7467.html?Open
http://www.ibm.com/developerworks/da...atabasedesign/

Larry Edelstein

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Thanks so much for info.

The fact you can have all 3 architectures combined in a single table
is amazing. They all work at different layers.

I wonder what is the most common combination for your everyday facts
table that goes thru normal cycle of monthly roll-in/roll-out ?

If the advantages are so individually powerful, is there any downside
to just design every warehouse facts table with ALL 3 features ?


TIA. Richard

"Richard" <rsl101 (AT) gmail (DOT) com> wrote

I answered both your questions in my post above.