[DB2 v8] Implicit Clustering index .. Still not done..

Bala

[DB2 v8] Implicit Clustering index .. Still not done..
Hi List,

How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or the
combination or the created_ts ? What I am seeing is that DB2 is picking the
created_ts. But, there are two indexes that were created before V5 and hence
the catalog has '0001-01-01-00.00.00.000000' . In such a case, how will the
reorg know which one the implicit clustering index? Will it start looking at
OBIDs next to created- timestamp ?

Thanks,

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Shery hepp

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Bala)
Why would you want to use implicit clustering instead of specifying
cluster on an index?



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2DBAzOS
Sent: Tuesday, November 24, 2009 5:12 AM
To: [login to unmask email]
Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



Hi List,



How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
the combination or the created_ts ? What I am seeing is that DB2 is
picking the created_ts. But, there are two indexes that were created
before V5 and hence the catalog has '0001-01-01-00.00.00.000000' . In
such a case, how will the reorg know which one the implicit clustering
index? Will it start looking at OBIDs next to created- timestamp ?



Thanks,



________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Martin Hubel

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Shery hepp)
To paraphrase Shery says, Why would you care?

Stuff that goes back to V5 indicates a long standing issue. My understanding is that the first created index is used if a clustering index is not explicitly specified. Normally this is the primary index.

While there are known cases where the primary index is a viable choice for clustering, if is often not the best choice. Clustering helps DB2 avoid tablespace I/O when multiple rows are retrieved. In most PK situations, only one row is retrieved as searching on a range of PKs or bringing rows back in PK order may only be necessary for batch processing.

Clustering is best placed on an index that uses range predicates or has a low cardinality. It should be chosen during physical design looking at various alternatives.

hth--Martin


>> Why would you want to use implicit clustering instead of specifying
>> cluster on an index?



>> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2DBAzOS
>> Sent: Tuesday, November 24, 2009 5:12 AM
>> To: [login to unmask email]
>> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



>> Hi List,



>> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
>> the combination or the created_ts ? What I am seeing is that DB2 is
>> picking the created_ts. But, there are two indexes that were created
>> before V5 and hence the catalog has
>> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
>> which one the implicit clustering index? Will it start looking at OBIDs
>> next to created- timestamp ?



>> Thanks,





>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here.




====================
Martin Hubel
MHC Inc.
[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Bala

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Martin Hubel)
Why would I care ?

I have a huge list of tablespaces that do not have explicit clustering
index. Looks to me that only partitioning index (of v7 and back) have had
Cluster and no other index has.
I am trying to define (alter!) explicit clustering index (primary key index
in most cases).

If there is definite DB2 rule for implicit clustering index, then I thought
I would just live with the way it is and change indexes when
possible/required. If there is no such definiton or rule for implicit
clustering index, I afraid the reorgs would produce different result on
tablespaces (or their indexes) that were dropped and recreated. I don't want
to take surprises.

After all "Business" does not approve changing something often or without
reason. And, it is even more difficult to make the reasons understandable to
them. And, I am not somebody who has lived all these years every since my
shop was physically put on DB2 and DB2 has been changing/improving over
years/versions.

Thanks,

On Wed, Nov 25, 2009 at 3:20 AM, Martin Hubel <[login to unmask email]> wrote:

> To paraphrase Shery says, Why would you care?
>
> Stuff that goes back to V5 indicates a long standing issue. My
> understanding is that the first created index is used if a clustering index
> is not explicitly specified. Normally this is the primary index.
>
> While there are known cases where the primary index is a viable choice for
> clustering, if is often not the best choice. Clustering helps DB2 avoid
> tablespace I/O when multiple rows are retrieved. In most PK situations, only
> one row is retrieved as searching on a range of PKs or bringing rows back in
> PK order may only be necessary for batch processing.
>
> Clustering is best placed on an index that uses range predicates or has a
> low cardinality. It should be chosen during physical design looking at
> various alternatives.
>
> hth--Martin
>
>
> >> Why would you want to use implicit clustering instead of specifying
> >> cluster on an index?
>
>
>
> >> From: IDUG DB2-L
>
> [mailto:[login to unmask email] <[login to unmask email]> On Behalf Of
> DB2DBAzOS
> >> Sent: Tuesday, November 24, 2009 5:12 AM
> >> To: [login to unmask email]
>
> >> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..
>
>
>
> >> Hi List,
>
>
>
> >> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
> >> the combination or the created_ts ? What I am seeing is that DB2 is
> >> picking the created_ts. But, there are two indexes that were created
> >> before V5 and hence the catalog has
> >> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
> >> which one the implicit clustering index? Will it start looking at OBIDs
> >> next to created- timestamp ?
>
>
>
> >> Thanks,
>
>
>
>
>
> >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
> >> are not already an IDUG member, please register here.
>
>
>
>
> ====================
> Martin Hubel
> MHC Inc.
> [login to unmask email]
> +1 905-764-7498
> +1 416-670-7498 Mobile
> Skype: db2hubel
> Yahoo IM: db2hubel
>
> Charter Member - IBM Gold Consultant Program
> IBM Information Champion
>
> Fight the Right Fires - Let us show you how
> ====================
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Bala)
Let's ask another question



How are you ACCESSING the data?



A clustering index is only relevant if it defines or supports some
processing sequence



If you are only ever (or most of the time) accessing the data randomly then
who cares what sequence it's in - DB2 certainly doesn't



However, if you have many (or perhaps a few time-critical) processes
accessing the data in a given sequence, then that should be your clustering
key



Paraphrasing - clustering helps sequential processing get the data in the
sequence you want it in



Phil Grainger

Grainger Database Solutions Ltd



_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2DBAzOS
Sent: 25 November 2009 05:10
To: [login to unmask email]
Subject: Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



Why would I care ?



I have a huge list of tablespaces that do not have explicit clustering
index. Looks to me that only partitioning index (of v7 and back) have had
Cluster and no other index has.

I am trying to define (alter!) explicit clustering index (primary key index
in most cases).



If there is definite DB2 rule for implicit clustering index, then I thought
I would just live with the way it is and change indexes when
possible/required. If there is no such definiton or rule for implicit
clustering index, I afraid the reorgs would produce different result on
tablespaces (or their indexes) that were dropped and recreated. I don't want
to take surprises.



After all "Business" does not approve changing something often or without
reason. And, it is even more difficult to make the reasons understandable to
them. And, I am not somebody who has lived all these years every since my
shop was physically put on DB2 and DB2 has been changing/improving over
years/versions.



Thanks,

On Wed, Nov 25, 2009 at 3:20 AM, Martin Hubel <[login to unmask email]> wrote:

To paraphrase Shery says, Why would you care?

Stuff that goes back to V5 indicates a long standing issue. My understanding
is that the first created index is used if a clustering index is not
explicitly specified. Normally this is the primary index.

While there are known cases where the primary index is a viable choice for
clustering, if is often not the best choice. Clustering helps DB2 avoid
tablespace I/O when multiple rows are retrieved. In most PK situations, only
one row is retrieved as searching on a range of PKs or bringing rows back in
PK order may only be necessary for batch processing.

Clustering is best placed on an index that uses range predicates or has a
low cardinality. It should be chosen during physical design looking at
various alternatives.

hth--Martin


>> Why would you want to use implicit clustering instead of specifying
>> cluster on an index?



>> From: IDUG DB2-L

<mailto:[login to unmask email]> [mailto:[login to unmask email] On Behalf Of
DB2DBAzOS
>> Sent: Tuesday, November 24, 2009 5:12 AM
>> To: <mailto:[login to unmask email]> [login to unmask email]


>> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



>> Hi List,



>> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
>> the combination or the created_ts ? What I am seeing is that DB2 is
>> picking the created_ts. But, there are two indexes that were created
>> before V5 and hence the catalog has
>> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
>> which one the implicit clustering index? Will it start looking at OBIDs
>> next to created- timestamp ?



>> Thanks,






>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here.





====================
Martin Hubel
MHC Inc.
<mailto:[login to unmask email]> [login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================



_____

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The
Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.



_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Bala

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Phil Grainger)
Ok, I quit. I guess everybody is in the "asking mode".

On Wed, Nov 25, 2009 at 2:39 PM, Phil Grainger <
[login to unmask email]> wrote:

> Let’s ask another question
>
>
>
> How are you ACCESSING the data?
>
>
>
> A clustering index is only relevant if it defines or supports some
> processing sequence
>
>
>
> If you are only ever (or most of the time) accessing the data randomly then
> who cares what sequence it’s in – DB2 certainly doesn’t
>
>
>
> However, if you have many (or perhaps a few time-critical) processes
> accessing the data in a given sequence, then that should be your clustering
> key
>
>
>
> Paraphrasing – clustering helps sequential processing get the data in the
> sequence you want it in
>
>
>
> Phil Grainger
>
> Grainger Database Solutions Ltd
>
>
> ------------------------------
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *DB2DBAzOS
> *Sent:* 25 November 2009 05:10
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not
> done..
>
>
>
> Why would I care ?
>
>
>
> I have a huge list of tablespaces that do not have explicit clustering
> index. Looks to me that only partitioning index (of v7 and back) have had
> Cluster and no other index has.
>
> I am trying to define (alter!) explicit clustering index (primary key index
> in most cases).
>
>
>
> If there is definite DB2 rule for implicit clustering index, then I thought
> I would just live with the way it is and change indexes when
> possible/required. If there is no such definiton or rule for implicit
> clustering index, I afraid the reorgs would produce different result on
> tablespaces (or their indexes) that were dropped and recreated. I don't want
> to take surprises.
>
>
>
> After all "Business" does not approve changing something often or without
> reason. And, it is even more difficult to make the reasons understandable to
> them. And, I am not somebody who has lived all these years every since my
> shop was physically put on DB2 and DB2 has been changing/improving over
> years/versions.
>
>
>
> Thanks,
>
> On Wed, Nov 25, 2009 at 3:20 AM, Martin Hubel <[login to unmask email]> wrote:
>
> To paraphrase Shery says, Why would you care?
>
> Stuff that goes back to V5 indicates a long standing issue. My
> understanding is that the first created index is used if a clustering index
> is not explicitly specified. Normally this is the primary index.
>
> While there are known cases where the primary index is a viable choice for
> clustering, if is often not the best choice. Clustering helps DB2 avoid
> tablespace I/O when multiple rows are retrieved. In most PK situations, only
> one row is retrieved as searching on a range of PKs or bringing rows back in
> PK order may only be necessary for batch processing.
>
> Clustering is best placed on an index that uses range predicates or has a
> low cardinality. It should be chosen during physical design looking at
> various alternatives.
>
> hth--Martin
>
>
> >> Why would you want to use implicit clustering instead of specifying
> >> cluster on an index?
>
>
>
> >> From: IDUG DB2-L
>
> [mailto:[login to unmask email] <[login to unmask email]> On Behalf Of DB2DBAzOS
> >> Sent: Tuesday, November 24, 2009 5:12 AM
> >> To: [login to unmask email]
>
>
> >> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..
>
>
>
> >> Hi List,
>
>
>
> >> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
> >> the combination or the created_ts ? What I am seeing is that DB2 is
> >> picking the created_ts. But, there are two indexes that were created
> >> before V5 and hence the catalog has
> >> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
> >> which one the implicit clustering index? Will it start looking at OBIDs
> >> next to created- timestamp ?
>
>
>
> >> Thanks,
>
>
>
>
> >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you
> >> are not already an IDUG member, please register here.
>
>
>
> ====================
> Martin Hubel
> MHC Inc.
> [login to unmask email]
> +1 905-764-7498
> +1 416-670-7498 Mobile
> Skype: db2hubel
> Yahoo IM: db2hubel
>
> Charter Member - IBM Gold Consultant Program
> IBM Information Champion
>
> Fight the Right Fires - Let us show you how
> ====================
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Michael Turner

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Bala)
OK, I will try to answer your original question! I seem to remember (and at my age that doesn't guarantee accuracy) that the selection of the implicit clustering index is not based on anything stored in the Catalog. It is dependent on how the object definitions are chained together within the DBD in the Directory. This will initially point DB2 to the first index defined but, after drops and creates of indexes, the exact order of chaining is not certain.

You will need to look at the Diagnosis Guide and Reference manual to investigate further.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: DB2DBAzOS
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, November 25, 2009 9:23 AM
Subject: Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..


Ok, I quit. I guess everybody is in the "asking mode".

On Wed, Nov 25, 2009 at 2:39 PM, Phil Grainger <[login to unmask email]> wrote:

Let’s ask another question



How are you ACCESSING the data?



A clustering index is only relevant if it defines or supports some processing sequence



If you are only ever (or most of the time) accessing the data randomly then who cares what sequence it’s in – DB2 certainly doesn’t



However, if you have many (or perhaps a few time-critical) processes accessing the data in a given sequence, then that should be your clustering key



Paraphrasing – clustering helps sequential processing get the data in the sequence you want it in



Phil Grainger

Grainger Database Solutions Ltd




----------------------------------------------------------------------------

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2DBAzOS
Sent: 25 November 2009 05:10


To: [login to unmask email]

Subject: Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..




Why would I care ?



I have a huge list of tablespaces that do not have explicit clustering index. Looks to me that only partitioning index (of v7 and back) have had Cluster and no other index has.

I am trying to define (alter!) explicit clustering index (primary key index in most cases).



If there is definite DB2 rule for implicit clustering index, then I thought I would just live with the way it is and change indexes when possible/required. If there is no such definiton or rule for implicit clustering index, I afraid the reorgs would produce different result on tablespaces (or their indexes) that were dropped and recreated. I don't want to take surprises.



After all "Business" does not approve changing something often or without reason. And, it is even more difficult to make the reasons understandable to them. And, I am not somebody who has lived all these years every since my shop was physically put on DB2 and DB2 has been changing/improving over years/versions.



Thanks,

On Wed, Nov 25, 2009 at 3:20 AM, Martin Hubel <[login to unmask email]> wrote:

To paraphrase Shery says, Why would you care?

Stuff that goes back to V5 indicates a long standing issue. My understanding is that the first created index is used if a clustering index is not explicitly specified. Normally this is the primary index.

While there are known cases where the primary index is a viable choice for clustering, if is often not the best choice. Clustering helps DB2 avoid tablespace I/O when multiple rows are retrieved. In most PK situations, only one row is retrieved as searching on a range of PKs or bringing rows back in PK order may only be necessary for batch processing.

Clustering is best placed on an index that uses range predicates or has a low cardinality. It should be chosen during physical design looking at various alternatives.

hth--Martin


>> Why would you want to use implicit clustering instead of specifying
>> cluster on an index?



>> From: IDUG DB2-L

[mailto:[login to unmask email] On Behalf Of DB2DBAzOS
>> Sent: Tuesday, November 24, 2009 5:12 AM
>> To: [login to unmask email]


>> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



>> Hi List,



>> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
>> the combination or the created_ts ? What I am seeing is that DB2 is
>> picking the created_ts. But, there are two indexes that were created
>> before V5 and hence the catalog has
>> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
>> which one the implicit clustering index? Will it start looking at OBIDs
>> next to created- timestamp ?



>> Thanks,






>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here.





====================
Martin Hubel
MHC Inc.
[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================




----------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.




----------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



----------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.




------------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Michael Turner)
Please don't be offended by the questions



We're just trying to make sure you get the RIGHT answer



And the right answer isn't (necessarily) to have a clustering index on every
table



You may well be fine the way things are



(and be grateful no-one has yet said "it depends" :-) )



Phil Grainger

Grainger Database Solutions Ltd



_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2DBAzOS
Sent: 25 November 2009 09:23
To: [login to unmask email]
Subject: Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



Ok, I quit. I guess everybody is in the "asking mode".



On Wed, Nov 25, 2009 at 2:39 PM, Phil Grainger
<[login to unmask email]> wrote:

Let's ask another question



How are you ACCESSING the data?



A clustering index is only relevant if it defines or supports some
processing sequence



If you are only ever (or most of the time) accessing the data randomly then
who cares what sequence it's in - DB2 certainly doesn't



However, if you have many (or perhaps a few time-critical) processes
accessing the data in a given sequence, then that should be your clustering
key



Paraphrasing - clustering helps sequential processing get the data in the
sequence you want it in



Phil Grainger

Grainger Database Solutions Ltd



_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2DBAzOS
Sent: 25 November 2009 05:10


To: [login to unmask email]

Subject: Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



Why would I care ?



I have a huge list of tablespaces that do not have explicit clustering
index. Looks to me that only partitioning index (of v7 and back) have had
Cluster and no other index has.

I am trying to define (alter!) explicit clustering index (primary key index
in most cases).



If there is definite DB2 rule for implicit clustering index, then I thought
I would just live with the way it is and change indexes when
possible/required. If there is no such definiton or rule for implicit
clustering index, I afraid the reorgs would produce different result on
tablespaces (or their indexes) that were dropped and recreated. I don't want
to take surprises.



After all "Business" does not approve changing something often or without
reason. And, it is even more difficult to make the reasons understandable to
them. And, I am not somebody who has lived all these years every since my
shop was physically put on DB2 and DB2 has been changing/improving over
years/versions.



Thanks,

On Wed, Nov 25, 2009 at 3:20 AM, Martin Hubel <[login to unmask email]> wrote:

To paraphrase Shery says, Why would you care?

Stuff that goes back to V5 indicates a long standing issue. My understanding
is that the first created index is used if a clustering index is not
explicitly specified. Normally this is the primary index.

While there are known cases where the primary index is a viable choice for
clustering, if is often not the best choice. Clustering helps DB2 avoid
tablespace I/O when multiple rows are retrieved. In most PK situations, only
one row is retrieved as searching on a range of PKs or bringing rows back in
PK order may only be necessary for batch processing.

Clustering is best placed on an index that uses range predicates or has a
low cardinality. It should be chosen during physical design looking at
various alternatives.

hth--Martin


>> Why would you want to use implicit clustering instead of specifying
>> cluster on an index?



>> From: IDUG DB2-L

<mailto:[login to unmask email]> [mailto:[login to unmask email] On Behalf Of
DB2DBAzOS
>> Sent: Tuesday, November 24, 2009 5:12 AM
>> To: <mailto:[login to unmask email]> [login to unmask email]


>> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..



>> Hi List,



>> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
>> the combination or the created_ts ? What I am seeing is that DB2 is
>> picking the created_ts. But, there are two indexes that were created
>> before V5 and hence the catalog has
>> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
>> which one the implicit clustering index? Will it start looking at OBIDs
>> next to created- timestamp ?



>> Thanks,





>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here.




====================
Martin Hubel
MHC Inc.
<mailto:[login to unmask email]> [login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================



_____

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The
Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.



_____

< http://www.idug.org/ > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.



_____

< http://www.idug.org/ > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.



_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Bala

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Phil Grainger)
Thanks, Mike. I observed that there is no simple logic on Catalogs that DBAs
can understand in following the implicit clustering. Because, over the last
two reorgs, one of the table changed totally because one of the index was
dropped and recreated (thanks to V8 reorg feature that picks implicit
clustering while reloading).



On Wed, Nov 25, 2009 at 3:48 PM, Mike Turner <[login to unmask email]> wrote:

> OK, I will try to answer your original question! I seem to remember (and
> at my age that doesn't guarantee accuracy) that the selection of the
> implicit clustering index is not based on anything stored in the Catalog. It
> is dependent on how the object definitions are chained together within the
> DBD in the Directory. This will initially point DB2 to the first index
> defined but, after drops and creates of indexes, the exact order of chaining
> is not certain.
>
> You will need to look at the Diagnosis Guide and Reference manual to
> investigate further.
>
> Regards
> Mike Turner
> Email: [login to unmask email]
> Tel: +44 (0)1565-873702
> Web: www.michael-turner.ltd.uk
> Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered
> Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159
> 44.
>
> ----- Original Message -----
> *From:* DB2DBAzOS <[login to unmask email]>
> *Newsgroups:* bit.listserv.db2-l
> *To:* [login to unmask email]
> *Sent:* Wednesday, November 25, 2009 9:23 AM
> *Subject:* Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not
> done..
>
> Ok, I quit. I guess everybody is in the "asking mode".
>
> On Wed, Nov 25, 2009 at 2:39 PM, Phil Grainger <
> [login to unmask email]> wrote:
>
>> Let’s ask another question
>>
>>
>>
>> How are you ACCESSING the data?
>>
>>
>>
>> A clustering index is only relevant if it defines or supports some
>> processing sequence
>>
>>
>>
>> If you are only ever (or most of the time) accessing the data randomly
>> then who cares what sequence it’s in – DB2 certainly doesn’t
>>
>>
>>
>> However, if you have many (or perhaps a few time-critical) processes
>> accessing the data in a given sequence, then that should be your clustering
>> key
>>
>>
>>
>> Paraphrasing – clustering helps sequential processing get the data in the
>> sequence you want it in
>>
>>
>>
>> Phil Grainger
>>
>> Grainger Database Solutions Ltd
>>
>>
>> ------------------------------
>>
>> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *DB2DBAzOS
>> *Sent:* 25 November 2009 05:10
>>
>> *To:* [login to unmask email]
>> *Subject:* Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not
>> done..
>>
>>
>>
>> Why would I care ?
>>
>>
>>
>> I have a huge list of tablespaces that do not have explicit clustering
>> index. Looks to me that only partitioning index (of v7 and back) have had
>> Cluster and no other index has.
>>
>> I am trying to define (alter!) explicit clustering index (primary key
>> index in most cases).
>>
>>
>>
>> If there is definite DB2 rule for implicit clustering index, then I
>> thought I would just live with the way it is and change indexes when
>> possible/required. If there is no such definiton or rule for implicit
>> clustering index, I afraid the reorgs would produce different result on
>> tablespaces (or their indexes) that were dropped and recreated. I don't want
>> to take surprises.
>>
>>
>>
>> After all "Business" does not approve changing something often or without
>> reason. And, it is even more difficult to make the reasons understandable to
>> them. And, I am not somebody who has lived all these years every since my
>> shop was physically put on DB2 and DB2 has been changing/improving over
>> years/versions.
>>
>>
>>
>> Thanks,
>>
>> On Wed, Nov 25, 2009 at 3:20 AM, Martin Hubel <[login to unmask email]> wrote:
>>
>> To paraphrase Shery says, Why would you care?
>>
>> Stuff that goes back to V5 indicates a long standing issue. My
>> understanding is that the first created index is used if a clustering index
>> is not explicitly specified. Normally this is the primary index.
>>
>> While there are known cases where the primary index is a viable choice for
>> clustering, if is often not the best choice. Clustering helps DB2 avoid
>> tablespace I/O when multiple rows are retrieved. In most PK situations, only
>> one row is retrieved as searching on a range of PKs or bringing rows back in
>> PK order may only be necessary for batch processing.
>>
>> Clustering is best placed on an index that uses range predicates or has a
>> low cardinality. It should be chosen during physical design looking at
>> various alternatives.
>>
>> hth--Martin
>>
>>
>> >> Why would you want to use implicit clustering instead of specifying
>> >> cluster on an index?
>>
>>
>>
>> >> From: IDUG DB2-L
>>
>> [mailto:[login to unmask email] <[login to unmask email]> On Behalf Of DB2DBAzOS
>> >> Sent: Tuesday, November 24, 2009 5:12 AM
>> >> To: [login to unmask email]
>>
>>
>> >> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..
>>
>>
>>
>>
>> >> Hi List,
>>
>>
>>
>> >> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID
>> or
>> >> the combination or the created_ts ? What I am seeing is that DB2 is
>> >> picking the created_ts. But, there are two indexes that were created
>> >> before V5 and hence the catalog has
>> >> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
>> >> which one the implicit clustering index? Will it start looking at OBIDs
>> >> next to created- timestamp ?
>>
>>
>>
>> >> Thanks,
>>
>>
>>
>>
>> >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
>> you
>> >> are not already an IDUG member, please register here.
>>
>>
>>
>> ====================
>> Martin Hubel
>> MHC Inc.
>> [login to unmask email]
>> +1 905-764-7498
>> +1 416-670-7498 Mobile
>> Skype: db2hubel
>> Yahoo IM: db2hubel
>>
>> Charter Member - IBM Gold Consultant Program
>> IBM Information Champion
>>
>> Fight the Right Fires - Let us show you how
>> ====================
>>
>>
>> ------------------------------
>>
>> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>
>>
>> ------------------------------
>>
>> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>
>> ------------------------------
>>
>> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Bala

Re: [DB2 v8] Implicit Clustering index .. Still not done..
(in response to Bala)
Hi Phil,

I wasn't offended because, I do understand it would be monotonous to provide
just answers all the time :-)

And about 'it depends' answers, well, it depends ...




On Wed, Nov 25, 2009 at 4:05 PM, Phil Grainger <
[login to unmask email]> wrote:

> Please don’t be offended by the questions
>
>
>
> We’re just trying to make sure you get the RIGHT answer
>
>
>
> And the right answer isn’t (necessarily) to have a clustering index on
> every table
>
>
>
> You may well be fine the way things are
>
>
>
> (and be grateful no-one has yet said “it depends” J )
>
>
>
> Phil Grainger
>
> Grainger Database Solutions Ltd
>
>
> ------------------------------
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *DB2DBAzOS
> *Sent:* 25 November 2009 09:23
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not
> done..
>
>
>
> Ok, I quit. I guess everybody is in the "asking mode".
>
>
>
> On Wed, Nov 25, 2009 at 2:39 PM, Phil Grainger <
> [login to unmask email]> wrote:
>
> Let’s ask another question
>
>
>
> How are you ACCESSING the data?
>
>
>
> A clustering index is only relevant if it defines or supports some
> processing sequence
>
>
>
> If you are only ever (or most of the time) accessing the data randomly then
> who cares what sequence it’s in – DB2 certainly doesn’t
>
>
>
> However, if you have many (or perhaps a few time-critical) processes
> accessing the data in a given sequence, then that should be your clustering
> key
>
>
>
> Paraphrasing – clustering helps sequential processing get the data in the
> sequence you want it in
>
>
>
> Phil Grainger
>
> Grainger Database Solutions Ltd
>
>
> ------------------------------
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *DB2DBAzOS
> *Sent:* 25 November 2009 05:10
>
>
> *To:* [login to unmask email]
>
> *Subject:* Re: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not
> done..
>
>
>
> Why would I care ?
>
>
>
> I have a huge list of tablespaces that do not have explicit clustering
> index. Looks to me that only partitioning index (of v7 and back) have had
> Cluster and no other index has.
>
> I am trying to define (alter!) explicit clustering index (primary key index
> in most cases).
>
>
>
> If there is definite DB2 rule for implicit clustering index, then I thought
> I would just live with the way it is and change indexes when
> possible/required. If there is no such definiton or rule for implicit
> clustering index, I afraid the reorgs would produce different result on
> tablespaces (or their indexes) that were dropped and recreated. I don't want
> to take surprises.
>
>
>
> After all "Business" does not approve changing something often or without
> reason. And, it is even more difficult to make the reasons understandable to
> them. And, I am not somebody who has lived all these years every since my
> shop was physically put on DB2 and DB2 has been changing/improving over
> years/versions.
>
>
>
> Thanks,
>
> On Wed, Nov 25, 2009 at 3:20 AM, Martin Hubel <[login to unmask email]> wrote:
>
> To paraphrase Shery says, Why would you care?
>
> Stuff that goes back to V5 indicates a long standing issue. My
> understanding is that the first created index is used if a clustering index
> is not explicitly specified. Normally this is the primary index.
>
> While there are known cases where the primary index is a viable choice for
> clustering, if is often not the best choice. Clustering helps DB2 avoid
> tablespace I/O when multiple rows are retrieved. In most PK situations, only
> one row is retrieved as searching on a range of PKs or bringing rows back in
> PK order may only be necessary for batch processing.
>
> Clustering is best placed on an index that uses range predicates or has a
> low cardinality. It should be chosen during physical design looking at
> various alternatives.
>
> hth--Martin
>
>
> >> Why would you want to use implicit clustering instead of specifying
> >> cluster on an index?
>
>
>
> >> From: IDUG DB2-L
>
> [mailto:[login to unmask email] <[login to unmask email]> On Behalf Of DB2DBAzOS
> >> Sent: Tuesday, November 24, 2009 5:12 AM
> >> To: [login to unmask email]
>
>
> >> Subject: [DB2-L] [DB2 v8] Implicit Clustering index .. Still not done..
>
>
>
> >> Hi List,
>
>
>
> >> How does DB2 decide on implicit clustering ? Is it the OBID or ISOBID or
> >> the combination or the created_ts ? What I am seeing is that DB2 is
> >> picking the created_ts. But, there are two indexes that were created
> >> before V5 and hence the catalog has
> >> '0001-01-01-00.00.00.000000' . In such a case, how will the reorg know
> >> which one the implicit clustering index? Will it start looking at OBIDs
> >> next to created- timestamp ?
>
>
>
> >> Thanks,
>
>
>
> >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you
> >> are not already an IDUG member, please register here.
>
>
> ====================
> Martin Hubel
> MHC Inc.
> [login to unmask email]
> +1 905-764-7498
> +1 416-670-7498 Mobile
> Skype: db2hubel
> Yahoo IM: db2hubel
>
> Charter Member - IBM Gold Consultant Program
> IBM Information Champion
>
> Fight the Right Fires - Let us show you how
> ====================
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L