By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,310 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

Parallel incremental index build during load operation

P: n/a
Hi

I am facing the following problem. I load a fact table with around 25
millons lines, and 7 indexes. I load it with 3 million line subsets. I am on
a quadriprocessor Solaris machine. The loading phase seems quite efficient
and seems to be well parallelized. But the build phase is long and seems to
use only one processor. Very low IO wait occurs during this phase. Is there
something can I do to fasten this process? I guess that if the 4 procs where
working, it would help.

Thanks for your attention.

Alexandre
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You may find it advantageous, if you can test this, to not have the load process build the
indexes, but rather after the load is done, turn on intra-partition parallelism and then
build the indexes with this active as it will use more CPUs during the index scan phase
than the load procedure does. I have seen locations sometimes get overall execution time
gains by doing this.

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]

"Xela" <ab***@yahoo.com> wrote in message news:ck**********@reader1.imaginet.fr...
Hi

I am facing the following problem. I load a fact table with around 25
millons lines, and 7 indexes. I load it with 3 million line subsets. I am on
a quadriprocessor Solaris machine. The loading phase seems quite efficient
and seems to be well parallelized. But the build phase is long and seems to
use only one processor. Very low IO wait occurs during this phase. Is there
something can I do to fasten this process? I guess that if the 4 procs where
working, it would help.

Thanks for your attention.

Alexandre

Nov 12 '05 #2

P: n/a
Thanks for your reply. We have another requirement : the loaded table must
remain avalaible for requests. As a consequence, deffering index building
does not seem to fit our nneds since the table will not be avalaible for
queries during such a rebuild phase. Am I corrct? Just another question :
Isn't some other tuning valaible, like adding more sortheap space or others,
that could help? Thanks for your attention.

Alexandre

"Bob [IBM]" <Bo******@canada.com> wrote in message
news:2s*************@uni-berlin.de...
You may find it advantageous, if you can test this, to not have the load
process build the
indexes, but rather after the load is done, turn on intra-partition
parallelism and then
build the indexes with this active as it will use more CPUs during the
index scan phase
than the load procedure does. I have seen locations sometimes get overall
execution time
gains by doing this.

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software
Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official
IBM position -
ask my cat!]

"Xela" <ab***@yahoo.com> wrote in message
news:ck**********@reader1.imaginet.fr...
Hi

I am facing the following problem. I load a fact table with around 25
millons lines, and 7 indexes. I load it with 3 million line subsets. I am
on
a quadriprocessor Solaris machine. The loading phase seems quite
efficient
and seems to be well parallelized. But the build phase is long and seems
to
use only one processor. Very low IO wait occurs during this phase. Is
there
something can I do to fasten this process? I guess that if the 4 procs
where
working, it would help.

Thanks for your attention.

Alexandre


Nov 12 '05 #3

P: n/a
Well it depends on which version of DB2 UDB you are using ... if you are using V8.1 then
you have the ability to do online index creation ... since you mention that you wanted the
table to be accessible during the load (I assume this was a load insert and not a load
replace so you could access previous data in the table) you must be on V8.1 ... so you
should have the ability for online index creation. You need to have TYPE-II indexes for
this to happen.

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]

"Xela" <ab***@yahoo.com> wrote in message news:ck**********@reader1.imaginet.fr...
Thanks for your reply. We have another requirement : the loaded table must
remain avalaible for requests. As a consequence, deffering index building
does not seem to fit our nneds since the table will not be avalaible for
queries during such a rebuild phase. Am I corrct? Thanks for your attention.

Alexandre

"Bob [IBM]" <Bo******@canada.com> wrote in message
news:2s*************@uni-berlin.de...
You may find it advantageous, if you can test this, to not have the load
process build the
indexes, but rather after the load is done, turn on intra-partition
parallelism and then
build the indexes with this active as it will use more CPUs during the
index scan phase
than the load procedure does. I have seen locations sometimes get overall
execution time
gains by doing this.

--

Bob

"Xela" <ab***@yahoo.com> wrote in message
news:ck**********@reader1.imaginet.fr...
Hi

I am facing the following problem. I load a fact table with around 25
millons lines, and 7 indexes. I load it with 3 million line subsets. I am
on
a quadriprocessor Solaris machine. The loading phase seems quite
efficient
and seems to be well parallelized. But the build phase is long and seems
to
use only one processor. Very low IO wait occurs during this phase. Is
there
something can I do to fasten this process? I guess that if the 4 procs
where
working, it would help.

Thanks for your attention.

Alexandre



Nov 12 '05 #4

P: n/a

I use actually DB2 8.1.2 so my indexes are type II. I use also load insert.
But the indexes reconstruction still quite slow dans does not use all the
resources of our computer. That is what disappoints me. Many thanks anyway.

Alexandre

"Bob [IBM]" <Bo******@canada.com> wrote in message
news:2s*************@uni-berlin.de...
Well it depends on which version of DB2 UDB you are using ... if you are
using V8.1 then
you have the ability to do online index creation ... since you mention
that you wanted the
table to be accessible during the load (I assume this was a load insert
and not a load
replace so you could access previous data in the table) you must be on
V8.1 ... so you
should have the ability for online index creation. You need to have
TYPE-II indexes for
this to happen.

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software
Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official
IBM position -
ask my cat!]

"Xela" <ab***@yahoo.com> wrote in message
news:ck**********@reader1.imaginet.fr...
Thanks for your reply. We have another requirement : the loaded table
must
remain avalaible for requests. As a consequence, deffering index building
does not seem to fit our nneds since the table will not be avalaible for
queries during such a rebuild phase. Am I corrct? Thanks for your
attention.

Alexandre

"Bob [IBM]" <Bo******@canada.com> wrote in message
news:2s*************@uni-berlin.de...
> You may find it advantageous, if you can test this, to not have the
> load
> process build the
> indexes, but rather after the load is done, turn on intra-partition
> parallelism and then
> build the indexes with this active as it will use more CPUs during the
> index scan phase
> than the load procedure does. I have seen locations sometimes get
> overall
> execution time
> gains by doing this.
>
> --
>
> Bob
>
> "Xela" <ab***@yahoo.com> wrote in message
> news:ck**********@reader1.imaginet.fr...
>> Hi
>>
>> I am facing the following problem. I load a fact table with around 25
>> millons lines, and 7 indexes. I load it with 3 million line subsets. I
>> am
>> on
>> a quadriprocessor Solaris machine. The loading phase seems quite
>> efficient
>> and seems to be well parallelized. But the build phase is long and
>> seems
>> to
>> use only one processor. Very low IO wait occurs during this phase. Is
>> there
>> something can I do to fasten this process? I guess that if the 4 procs
>> where
>> working, it would help.
>>
>> Thanks for your attention.
>>
>> Alexandre
>>
>>
>
>



Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.