DB2 Version 8 Question

Barbara Nigh

DB2 Version 8 Question
In DB2 Version 8, is it possible to change the clustering index on a
partitioned table without dropping the tablespace first?







-----------------------------------------
*******************************************************************
****
This E-mail is confidential. It may also be legally privileged. If
you
are not the addressee you may not copy, forward, disclose or use
any
part of it. If you have received this message in error, please
delete
it and all copies from your system and notify the sender
immediately
by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
*******************************************************************
****

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Doyle Mark

Re: DB2 Version 8 Question
(in response to Barbara Nigh)
I expect so (though I never tried it). From the V8 What's New document
(http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnwnj10/2.5.1.4?DT
=20040210142928)

2.5.1.4 Change the partitioning and clustering of the data in your
tables

Version 8 gives you more flexibility for partitioning and clustering the
data in your tables:

You can create a partition without an index. As a result, you can
improve performance by eliminating an unnecessary index and the
accompanying overhead.

You can drop a partitioning index or create a table without a
partitioning index.

You can define a clustering order that is different from the
partitioning order. For example, if your data is partitioned by month,
you can cluster by customer number within each partition.

You can alter the clustering index.


Mark
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Barbara Jo Nigh
Sent: Monday, January 16, 2006 1:32 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Version 8 Question


In DB2 Version 8, is it possible to change the clustering index on a
partitioned table without dropping the tablespace first?







-----------------------------------------
*******************************************************************
****
This E-mail is confidential. It may also be legally privileged. If you
are not the addressee you may not copy, forward, disclose or use any
part of it. If you have received this message in error, please delete
it and all copies from your system and notify the sender immediately by
return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
*******************************************************************
****

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris Blaicher

Re: DB2 Version 8 Question
(in response to Doyle Mark)
Yes, but with a few cautionary remarks.

If you change any column but the last column from a fixed length to a
variable length, I very, very strongly suggest that you do a full reorg
on the tablespace, even though DB2 only sets AREO status on the index.
I feel it should be put in REORP.

Why you ask? Well, let us say the first column was CHAR(2) and the
second column was DATE. The LIMITKEY on PART 1 is '01','02/28/1998'.
If you change the first column to VARCHAR(8) and then insert a row with
VALUES('01','03/15/1999') you would expect the row inserted to go into
at least the second partition, but it goes into PART 1.

Why? It is because the first column of the LIMITKEY is padded with
X'FF' while the inserted row is padded with X'00'. This means you can
have a unique index and insert a row with VALUES('01','03/15/1999')
before the ALTER and have it go into PART 2, and then do another insert
of the same key after the ALTER and have it go into PART 1. The next
time you run a REORG you will get errors.

Christopher Y. Blaicher
BMC Software, Inc.
Austin Development Labs
(512) 340-6154
BMC Software, Inc. makes no representations or promises regarding the
reliability, completeness, or accuracy of the information provided in
this discussion; all readers agree not to rely on this information or
take any action against BMC Software in response to this information.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Barbara Jo Nigh
Sent: Monday, January 16, 2006 1:32 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Version 8 Question

In DB2 Version 8, is it possible to change the clustering index on a
partitioned table without dropping the tablespace first?







-----------------------------------------
*******************************************************************
****
This E-mail is confidential. It may also be legally privileged. If
you
are not the addressee you may not copy, forward, disclose or use
any
part of it. If you have received this message in error, please
delete
it and all copies from your system and notify the sender
immediately
by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
*******************************************************************
****

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kintisch

Re: DB2 Version 8 Question
(in response to Chris Blaicher)
Barbara,
In V8 to be able to change clustering indexes first be sure you have
created the table with PARTITION BY so as to use Table-controlled
partitioning. Then [as noted in the following section of Redbook SG24-7134]:

2.7.7 Changing a clustering index
Since DB2 Version 8, you have the capability to change the clustering index
with an ALTER INDEX statement. Since only one index can be explicitly
defined as a clustering index, you must follow these steps to change the
clustering:

1. ALTER INDEX ixname1 NOT CLUSTER on the current clustering index.

Clustering continues to be done according to this index (ixname1) until a
new clustering index is explicitly defined.

2. ALTER INDEX ixname2 CLUSTER on the index you wish to be the new
clustering index.

New rows will be clustered according to the new clustering index. Old rows
will remain in their current location.

3. REORG the table space to rearrange the rows in the new clustering index
order.

All existing rows will be rearranged in the new clustering sequence. Any
new rows will be inserted with the new clustering sequence.
If no explicit clustering index is specified for a table, the DB2 Version 8
REORG utility recognizes the first index created on each table as the
implicit clustering index when ordering data rows. If the explicit
clustering for a table is removed (changed to NOT CLUSTER), that index is
still used as the implicit clustering index until a new explicit clustering
index is chosen.


I hope that helps. Larry Kintisch http://www.DBIndexDesign.com

At 11:32 AM 1/16/2006 -0800, you wrote:
>In DB2 Version 8, is it possible to change the clustering index on a
>partitioned table without dropping the tablespace first?
>
>
>
>
>
>
>
>-----------------------------------------
>*******************************************************************
>****
> This E-mail is confidential. It may also be legally privileged. If
>you
> are not the addressee you may not copy, forward, disclose or use
>any
> part of it. If you have received this message in error, please
>delete
> it and all copies from your system and notify the sender
>immediately
> by return E-mail.
>
> Internet communications cannot be guaranteed to be timely, secure,
> error or virus-free. The sender does not accept liability for any
> errors or omissions.
>*******************************************************************
>****
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: DB2 Version 8 Question
(in response to Larry Kintisch)
Barbara

I just read all the replies on your initially question and wondering how
complex the answers are.

The short answer is: Yes you can without dropping the index and without
dropping the tablespace. After you altered the index, the table is
automatically table controlled. You have nothing else to do.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: DB2 Version 8 Question
(in response to Walter Janißen)
On Mon, 16 Jan 2006 14:13:06 -0600, Blaicher, Chris
<[login to unmask email]> wrote:

Chris

Wow. If it behaves like you said, that's a bug in DB2 in my opinion. Either
the tablespace must be flagged REORP or DB2 must handle it correctly.

Does the same hold for table controlled partitioned tablespaces and for non
padded partitioning indexes (if the table is index-controlled)?

>Yes, but with a few cautionary remarks.
>
>If you change any column but the last column from a fixed length to a
>variable length, I very, very strongly suggest that you do a full reorg
>on the tablespace, even though DB2 only sets AREO status on the index.
>I feel it should be put in REORP.
>
>Why you ask? Well, let us say the first column was CHAR(2) and the
>second column was DATE. The LIMITKEY on PART 1 is '01','02/28/1998'.
>If you change the first column to VARCHAR(8) and then insert a row with
>VALUES('01','03/15/1999') you would expect the row inserted to go into
>at least the second partition, but it goes into PART 1.
>
>Why? It is because the first column of the LIMITKEY is padded with
>X'FF' while the inserted row is padded with X'00'. This means you can
>have a unique index and insert a row with VALUES('01','03/15/1999')
>before the ALTER and have it go into PART 2, and then do another insert
>of the same key after the ALTER and have it go into PART 1. The next
>time you run a REORG you will get errors.
>
>Christopher Y. Blaicher
>BMC Software, Inc.
>Austin Development Labs
>(512) 340-6154
>BMC Software, Inc. makes no representations or promises regarding the
>reliability, completeness, or accuracy of the information provided in
>this discussion; all readers agree not to rely on this information or
>take any action against BMC Software in response to this information.
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
>Behalf Of Barbara Jo Nigh
>Sent: Monday, January 16, 2006 1:32 PM
>To: [login to unmask email]
>Subject: [DB2-L] DB2 Version 8 Question
>
>In DB2 Version 8, is it possible to change the clustering index on a
>partitioned table without dropping the tablespace first?
>
>
>
>
>
>
>
>-----------------------------------------
>*******************************************************************
>****
> This E-mail is confidential. It may also be legally privileged. If
>you
> are not the addressee you may not copy, forward, disclose or use
>any
> part of it. If you have received this message in error, please
>delete
> it and all copies from your system and notify the sender
>immediately
> by return E-mail.
>
> Internet communications cannot be guaranteed to be timely, secure,
> error or virus-free. The sender does not accept liability for any
> errors or omissions.
>*******************************************************************
>****
>
>------------------------------------------------------------------------
>---------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences
>at http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris Blaicher

Re: DB2 Version 8 Question
(in response to Walter Janißen)
After sending my comments, I realized I should have made it clear I had only done the test on an index-controlled padded object. If you followed the column type changes I used, PADDED/NOT PADDED would not matter. The big problem is going from fixed length to variable length.

Christopher Y. Blaicher
BMC Software, Inc.
Austin Development Labs
(512) 340-6154
BMC Software, Inc. makes no representations or promises regarding the reliability, completeness, or accuracy of the information provided in this discussion; all readers agree not to rely on this information or take any action against BMC Software in response to this information.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Tuesday, January 17, 2006 6:10 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Version 8 Question

On Mon, 16 Jan 2006 14:13:06 -0600, Blaicher, Chris
<[login to unmask email]> wrote:

Chris

Wow. If it behaves like you said, that's a bug in DB2 in my opinion. Either
the tablespace must be flagged REORP or DB2 must handle it correctly.

Does the same hold for table controlled partitioned tablespaces and for non
padded partitioning indexes (if the table is index-controlled)?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm