Regarding altering a non unique index in DB2V8

Sabarish Kannan

Regarding altering a non unique index in DB2V8
Hi

I want to know whether it is possible to alter a non unique index in DB2V8
to a unique index without dropping and recreating .i checked ALTER INDEX and
i couldnt find any option for it.

Please let me know.

Sabarish


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Jack Campbell

Re: Regarding altering a non unique index in DB2V8
(in response to Sabarish Kannan)
Sabarish

you need to drop/create the index to add UNIQUE - bear in mind db2 will need
to verify every entry in the index is unique (and this is normally done by the
BUILD processor)

regards

Jack


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Sevetson

Re: Regarding altering a non unique index in DB2V8
(in response to Jack Campbell)
You'd think that'd be something they could fix: Just let the ALTER to
UNIQUE result in a RBDP flag being set.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jack Campbell
Sent: Friday, January 09, 2009 1:42 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Regarding altering a non unique index in DB2V8

Sabarish

you need to drop/create the index to add UNIQUE - bear in mind db2 will
need
to verify every entry in the index is unique (and this is normally done
by the
BUILD processor)

regards

Jack


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Peter Vanroose

Re: Regarding altering a non unique index in DB2V8
(in response to Philip Sevetson)
No, the UNIQUE characteristique of an index will never be ALTERable, since
the internal structure of both types of indexes is different.
Especially, a UNIQUE index has just a single RID per value (in the leaf
nodes), while a non-unique one has lists of RIDs per value. The latter
requires a more complex setup internally.

-- Peter Vanroose.

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Phil Grainger

Re: Regarding altering a non unique index in DB2V8
(in response to Peter Vanroose)
Well,

To be honest, I'd never say "never" - who knows what is around the corner

I still remember scoffing at the idea of an "on-line reorg" - how on earth could THAT be possible :)

Phil G
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Peter Vanroose
Sent: Sun 11/01/2009 21:40
To: [login to unmask email]
Subject: Re: [DB2-L] Regarding altering a non unique index in DB2V8



No, the UNIQUE characteristique of an index will never be ALTERable, since
the internal structure of both types of indexes is different.
Especially, a UNIQUE index has just a single RID per value (in the leaf
nodes), while a non-unique one has lists of RIDs per value. The latter
requires a more complex setup internally.

-- Peter Vanroose.

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html





______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Adam Baldwin

Re: Regarding altering a non unique index in DB2V8
(in response to Phil Grainger)
Leaving aside the differences in the physical attributes of unique and non
unique indexes, one also has to think about the logical side. Given that as of
V8, DB2 allows you to insert into, and delete from, tables that have non-
unique indexes that are in an RBDP state and that it also allows you to delete
from tables that have unique indexes that are in an RBDP state, what would
be the desired results when an index has been "altered" from one to the other
and is in a RBDP state?


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Sevetson

Re: Regarding altering a non unique index in DB2V8
(in response to Adam Baldwin)
Adam,

The proposed ALTER INDEX TO UNIQUE/TO NOT UNIQUE is just a way of
speeding up a change that someone is doing anyway. Current practice is
to drop the index and recreate it with UNIQUE and DEFER YES clauses.
You'll get a REBUILD PENDING status on it, but meanwhile you'll
invalidate all plans/packages that use the index for any reason.

Why not instead ALTER the index, get the REBUILD PENDING status, and (if
altering TO NOT UNIQUE) invalidate only the plans/packages which use the
index for INSERT or UPDATE? The plans/packages which use the index for
SELECT are doing so for speed of access, and we want to preserve those
access paths if possible.

In either of the above two cases, a REBUILD INDEX is required and will
fail if any duplicate keys are detected (assuming you have done the
ALTER INDEX x.y TO UNIQUE), so the ALTER is not more dangerous/risky
than the current means to accomplish this purpose.

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Adam Baldwin
Sent: Monday, January 12, 2009 1:41 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Regarding altering a non unique index in DB2V8

Leaving aside the differences in the physical attributes of unique and
non
unique indexes, one also has to think about the logical side. Given that
as of
V8, DB2 allows you to insert into, and delete from, tables that have
non-
unique indexes that are in an RBDP state and that it also allows you to
delete
from tables that have unique indexes that are in an RBDP state, what
would
be the desired results when an index has been "altered" from one to the
other
and is in a RBDP state?


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Troy Coleman

Re: Regarding altering a non unique index in DB2V8
(in response to Philip Sevetson)
The only way to handle this today is to drop and recreate.
However, IBM has been adding more and more features to minimize outages through what is known as "On-line Schema Evolution" or more recently (DDOD) "Data Definition On Demand".
I wrote a blog entry on this back in 2007 http://ibmsystemsmag.blogs.com/db2utor/2007/09/evolution-of-on.html
So the bad news is you don't have it today but the good news is you will probably have it in a future release.

Troy Coleman, Support Engineer IBM Certified Database Administrator - DB2 9 for z/OS and LUW SoftBase Systems, Inc. 847-776-0618 828-670-9900 ext. 334 [login to unmask email] Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/ The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.

sabarish kannan wrote:
Hi
 
  I want to know whether it is possible to alter a non unique index in DB2V8 to a unique index without dropping and recreating .i checked ALTER INDEX and i couldnt find any option for it.
 
Please let me know.
 
Sabarish
 


IDUG 2009 - Europe * 5-9 October * Rome, Italy

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.



IDUG 2009 - Europe * 5-9 October * Rome, Italy

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.

Adam Baldwin

Re: Regarding altering a non unique index in DB2V8
(in response to Troy Coleman)
Phil, Troy - points taken. Re the availability, from personal experience I
wouldn't see too much difference between DROP/CREATE and ALTER when it
comes to an index. In index changes that we do which require a DROP/CREATE
the longest step is always the rebuild - not the DROP/CREATE or the rebinds.

Also, we have index naming conventions that include whether the index is
unique or not, but as RENAME INDEX is coming.....

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Isaac Yassin

Re: Regarding altering a non unique index in DB2V8
(in response to Adam Baldwin)
Hi,

In V9 you may get some help in "rebuild index shrlevel
change" - however if it needs to be unique then you have
a "small" problem.

Isaac

---- Original message ----
>Date:   Tue, 13 Jan 2009 07:15:10 +0000
>From:   Adam Baldwin <[login to unmask email]>
>Subject:   Re: [DB2-L] Regarding altering a non unique
index in DB2V8
>To:   [login to unmask email]
>
>Phil, Troy - points taken. Re the availability, from
personal experience I
>wouldn't see too much difference between DROP/CREATE and
ALTER when it
>comes to an index. In index changes that we do which
require a DROP/CREATE
>the longest step is always the rebuild - not the
DROP/CREATE or the rebinds.
>
>Also, we have index naming conventions that include whether
the index is
>unique or not, but as RENAME INDEX is coming.....
>
>Cheers, Adam
>
>____________________________________________________________
__________
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
http://IDUG.ORG/Events *
>____________________________________________________________
__________
>
>
>
>
>IDUG.org was recently updated requiring members to use a
new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in
and update your member profile. If you are not already an
IDUG.org member, please register at
http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html