Hi all,
I'm currently investigating the use of MDC Tables for large data warehouse
tables.
My scenario:
A fact table with 1000 Million Rows distributed on 12 Partitions (3 physical
hosts with 4 logical partitions each).
Overall size of table is 350 GB . Each night 1.5 Million new rows will be
added
and approx. the same amount of old records will be deleted (Roll in/Roll out
with SQL INSERT/DELETE).
The table is stored in SMS tablespace with 16K Pagesize and 64 Pages
Extentsize.
The tablespace has 6 containers on each partition. Each container is on a
separate IBM ESS array. Prefetchsize is 384 (6 containers * 64 pages).
Prefetch behaves very well
with these settings (DB2_PARALLEL_IO is set). DB2 is V8.1 ESE (DPF) FP5 and
runs on AIX 5.2.
We figured out that for our choosen MDC dimensions we will have to use an
extentsize of 2 pages otherwise we would waste too much space. This
extentsize gives us headaches:
- What is an optimal prefetchsize here?
With prefetchsize of 12 (6 containers * extentsize 2) each prefetcher will
only read 32 KB of data from one container.
With a prefetchsize of 384 (which is optimal from a disk layout point of
view) will DB2 start 192 prefetchers (that would be certainly overkill)?
Further:
Does anybody have experiences with MDC tables for large warehouse tables and
is willing to share them?
Especially performance experiences when inserting/deleting during the
roll-in/roll-out of daily data ?
Unfortunately I do not have an adequate environment to test all these
issues - so any comments are highly appreciated.
TIA
Joachim
PS: Feel free to send comments by email to joklassen at web dot de