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

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" <abuse@yahoo.com> wrote in message news:ck0sml$l9v$1@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
>
>

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]" <BobAtIBM@canada.com> wrote in message
news:2sifskF1lqa1iU1@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" <abuse@yahoo.com> wrote in message
> news:ck0sml$l9v$1@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
>>
>>
>
>

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" <abuse@yahoo.com> wrote in message news:ck12f4$mh2$1@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]" <BobAtIBM@canada.com> wrote in message
> news:2sifskF1lqa1iU1@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" <abuse@yahoo.com> wrote in message
> > news:ck0sml$l9v$1@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
> >>
> >>
> >
> >
>
>

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]" <BobAtIBM@canada.com> wrote in message
news:2sikocF1kj71eU1@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" <abuse@yahoo.com> wrote in message
> news:ck12f4$mh2$1@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]" <BobAtIBM@canada.com> wrote in message
>> news:2sifskF1lqa1iU1@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" <abuse@yahoo.com> wrote in message
>> > news:ck0sml$l9v$1@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
>> >>
>> >>
>> >
>> >
>>
>>
>
>