469,356 Members | 2,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Questions regarding large MDC tables

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
Nov 12 '05 #1
4 3250
Joachim Klassen wrote:
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

Try the new advisor in V8.2. You can simply download DB2 V8.2 onto your
laptop, mimic the stats and then apply the proposal to DB2 V8.1.5.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
Serge,
thanks for the quick reply.

I already followed your suggestion but with no luck. Design advisor makes no
recommendations for MDC.
Does that mean that MDC is not recommended at all for my scenario or do I
have to refine my workload input (which is at the moment very basic with 3
Queries and 1 Insert statement).

Thanks again
Joachim

"Serge Rielau" <sr*****@ca.ibm.com> schrieb im Newsbeitrag
news:35*************@individual.net...
Joachim Klassen wrote:
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

Try the new advisor in V8.2. You can simply download DB2 V8.2 onto your
laptop, mimic the stats and then apply the proposal to DB2 V8.1.5.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #3
Joachim,

Sure it does. The V8.2 advisor handles Indexes, Partitioning, MQT, and
MDC. You sure you tried V8.2?
MDC will not improve your insert performance. It can help with delete
(rollout) and query.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4
"Serge Rielau" <sr*****@ca.ibm.com> schrieb im Newsbeitrag
news:35*************@individual.net...
Joachim,

Sure it does. The V8.2 advisor handles Indexes, Partitioning, MQT, and
MDC. You sure you tried V8.2?
MDC will not improve your insert performance. It can help with delete
(rollout) and query.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge
Yes, I tried V8.2 (V8.1 FP7). I will retry with FP8 and a refined workload.
But maybe I use it wrong. Here is what I've done so far:
- captured objects ddl (tables and indexes) and statistics from the original
tables via db2look
- recreated the objects on my laptop in a non-partitioned instance (maybe
thats the problem)
- inserted some thousand records and run RUNSTATS to get entries in
SYSCOLDIST etc.. to be able to update them later
- updated the stats with the original values
- started Design advisor GUI and defined a workload consisting of 3 typical
queries and 1 insert
- started recommendations

I thought that MDC would not help with insert performance (more likely the
opposite ?) thats why we consider LOAD FROM CURSOR.
Our main goal is improvement for roll-out. Queries already perform well.
But the more informations I find about MDC in a partitioned environment the
more I doubt that they will help us in this special scenario.

Thanks for your comments
Joachim
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jack Coxen | last post: by
1 post views Thread by Invalidlastname | last post: by
1 post views Thread by mairhtin o'feannag | last post: by
6 posts views Thread by Phil Sandler | last post: by
22 posts views Thread by Jesse Burns | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.