Prev: Fenced User Id Confusion
Next: What's throttling?
From: Damir on 4 Mar 2010 06:44
Hi all,
what are the possible ways to convert a non partitioned index to a
partitioned index in DB2 V9.7.1?

According to the InfoCenter, the only way is to drop and recreate one index
at a time
(http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.partition.doc/doc/t0054730.html)

I was wondering if it were also possible to make the conversion with only
one command, such as REORG TABLE... or something like that?

Regards,
Damir



From: Serge Rielau on 4 Mar 2010 07:45
On 3/4/2010 6:44 AM, Damir wrote:
> Hi all,
> what are the possible ways to convert a non partitioned index to a
> partitioned index in DB2 V9.7.1?
>
> According to the InfoCenter, the only way is to drop and recreate one index
> at a time
> (http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.partition.doc/doc/t0054730.html)
>
> I was wondering if it were also possible to make the conversion with only
> one command, such as REORG TABLE... or something like that?
Damir,

It should be easy to write a stored procedure that, given a table table
converts all global indexes to local indexes...

Cheers
Serge


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

From: Damir on 4 Mar 2010 08:25
Of course it is and that is what we are going to do.
I was just wondering... :-)
Thank you for the quick response!

Regards,
Damir

---
"Serge Rielau" <srielau(a)ca.ibm.com> wrote in message
news:7v9oatFedgU1(a)mid.individual.net...
>
> It should be easy to write a stored procedure that, given a table table
> converts all global indexes to local indexes...
>
> Cheers
> Serge
>



From: Naresh Chainani on 5 Mar 2010 10:53
On Mar 4, 5:25 am, "Damir" <damirwil...(a)yahoo.com> wrote:
> Of course it is and that is what we are going to do.
> I was just wondering... :-)
> Thank you for the quick response!
>
> Regards,
>     Damir
>
> ---"Serge Rielau" <srie...(a)ca.ibm.com> wrote in message
>
> news:7v9oatFedgU1(a)mid.individual.net...
>
>
>
> > It should be easy to write a stored procedure that, given a table table
> > converts all global indexes to local indexes...
>
> > Cheers
> > Serge

One thing to add is that DB2 allows creation of partitioned and
nonpartitioned indexes on the same columns. One of the reasons for
allowing this was to ease this conversion where users first create the
desired partitioned indexes and then drop all nonpartitioned indexes.
That way, queries could continue to access the data using indexes when
the partitioned indexes are being created. As to whether the
nonpartitioned indexes should be dropped one by one or all at end,
space considerations may influence this decision.

Naresh
 | 
Pages: 1
Prev: Fenced User Id Confusion
Next: What's throttling?