Prev: db2look and trusted context
Next: 6 Find Your Shoe Size - Step by Step Guide to Measuring Your Feet
From: Serge Rielau on 9 Apr 2010 19:56
OK that makes all sense.
Now the obvious part you may be missing is that there is no such thing
as a free lunch.
If you want indexes on the archive they must be build at some point.
Now since this is happening on your archive and on a read-only (old)
partition, speed shouldn't be an issue right?

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Damir on 10 Apr 2010 08:43
Indeed, the speed is not the issue (anymore), now that we have the
partitioned indexes, because we can now build the indexes in advance and
have the new archive partition ready (in advance) for attachment into the
archive table.
The issue that (possibly) remains is the LOG/TEMP space consumption.
But I have a reason to believe that we will see some improvement there too:
last time the SET INTEGRITY command had to rebuild the complete indexes (for
the whole archived tables) and so it consumed a lot of LOG/TEMP space, while
now the index creation/buildup will work on the new arhcive partition only,
meaning there will be considerably less work to be done.

Regards,
Damir


---
"Serge Rielau" <srielau(a)ca.ibm.com> wrote in message
news:829t4mFpqeU1(a)mid.individual.net...
> OK that makes all sense.
> Now the obvious part you may be missing is that there is no such thing as
> a free lunch.
> If you want indexes on the archive they must be build at some point.
> Now since this is happening on your archive and on a read-only (old)
> partition, speed shouldn't be an issue right?
>
> Cheers
> Serge
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab
>



From: Serge Rielau on 10 Apr 2010 20:35
Correct.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Naresh Chainani on 12 Apr 2010 00:39
On Apr 10, 5:43 am, "Damir" <damirwil...(a)yahoo.com> wrote:
> Indeed, the speed is not the issue (anymore), now that we have the
> partitioned indexes, because we can now build the indexes in advance and
> have the new archive partition ready (in advance) for attachment into the
> archive table.
> The issue that (possibly) remains is the LOG/TEMP space consumption.
> But I have a reason to believe that we will see some improvement there too:
> last time the SET INTEGRITY command had to rebuild the complete indexes (for
> the whole archived tables) and so it consumed a lot of LOG/TEMP space, while
> now the index creation/buildup will work on the new arhcive partition only,
> meaning there will be considerably less work to be done.
>
> Regards,
>     Damir
>
> ---"Serge Rielau" <srie...(a)ca.ibm.com> wrote in message
>
> news:829t4mFpqeU1(a)mid.individual.net...
>
> > OK that makes all sense.
> > Now the obvious part you may be missing is that there is no such thing as
> > a free lunch.
> > If you want indexes on the archive they must be build at some point.
> > Now since this is happening on your archive and on a read-only (old)
> > partition, speed shouldn't be an issue right?
>
> > Cheers
> > Serge
>
> > --
> > Serge Rielau
> > SQL Architect DB2 for LUW
> > IBM Toronto Lab

Given that indexes will be already present on the new table in the
archive db prior to attach, the ATTACH + SET INTEGRITY (SI) operations
will be much faster with partitioned indexes in 9.7. SI will still
need to perform range check on the newly attached data but overall the
roll-in time should be considerably less.

Also, in your steps above, creating the indexes after the load (step
3a) would be faster/better than doing so in step 2.

Naresh
First  |  Prev  | 
Pages: 1 2
Prev: db2look and trusted context
Next: 6 Find Your Shoe Size - Step by Step Guide to Measuring Your Feet