reorg problems

[login to unmask email]

reorg problems
I have been trying to improve performance on a system that has been showing
excessive times in DB2. On inspection we found one table that seemed to be
the culprit. When I checked the statistics on it I found that it was about
60% clustered and had no clustering index. The primary index is unique and
could be clustering. I first reorged without changing the index and the
index went from 60% to 49% clustered. I then altered the index to make it
clustering & reorged again - but the results were the same ie: still 49%
clustered. This is the only index on this table - does anyone have an idea
as to why this is happening. The table is part of a Visual Image (now
Content Manager) system. Part of the system (mainly the images tables) is
on an NT server. The part that I am working with is on os390 (2.8) db2 v6
and is mainly indexes to the data on NT.
Bud Greenman
Programmer Analyst



Todd Burch

Re: reorg problems
(in response to BudGreenman@ONGOV.NET)
Bud, you did run runstats after the reorg, didn't you?

Todd.

----- Original Message -----
From: "Bud Greenman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 16, 2002 9:39 AM
Subject: reorg problems


> I have been trying to improve performance on a system that has been
showing
> excessive times in DB2. On inspection we found one table that seemed to
be
> the culprit. When I checked the statistics on it I found that it was
about
> 60% clustered and had no clustering index. The primary index is unique and
> could be clustering. I first reorged without changing the index and the
> index went from 60% to 49% clustered. I then altered the index to make it
> clustering & reorged again - but the results were the same ie: still 49%
> clustered. This is the only index on this table - does anyone have an idea
> as to why this is happening. The table is part of a Visual Image (now
> Content Manager) system. Part of the system (mainly the images tables) is
> on an NT server. The part that I am working with is on os390 (2.8) db2 v6
> and is mainly indexes to the data on NT.
> Bud Greenman
> Programmer Analyst
>
>
>


>



Mae Bruce

Re: reorg problems
(in response to Todd Burch)
And make sure the index was reorged in addition to the table.

-----Original Message-----
From: Todd Burch [mailto:[login to unmask email]
Sent: Monday, December 16, 2002 10:06 AM
To: [login to unmask email]
Subject: Re: reorg problems


Bud, you did run runstats after the reorg, didn't you?

Todd.

----- Original Message -----
From: "Bud Greenman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 16, 2002 9:39 AM
Subject: reorg problems


> I have been trying to improve performance on a system that has been
showing
> excessive times in DB2. On inspection we found one table that seemed
> to
be
> the culprit. When I checked the statistics on it I found that it was
about
> 60% clustered and had no clustering index. The primary index is unique
> and could be clustering. I first reorged without changing the index
> and the index went from 60% to 49% clustered. I then altered the
> index to make it clustering & reorged again - but the results were the
> same ie: still 49% clustered. This is the only index on this table -
> does anyone have an idea as to why this is happening. The table is
> part of a Visual Image (now Content Manager) system. Part of the
> system (mainly the images tables) is on an NT server. The part that I
> am working with is on os390 (2.8) db2 v6 and is mainly indexes to the
> data on NT. Bud Greenman Programmer Analyst
>
>
>
> visit


>








michael bell

Re: reorg problems
(in response to Mae Bruce)
First thing to check is whether you reorged the index or the tablespace. An
index reorg won't change the sequence of data in the tablespace.
If it is only 49% clustered, I would recommend SORTDATA for the tablespace
reorg.

Mike Bell
HLS Technologies

----- Original Message -----
From: "Bud Greenman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 16, 2002 9:39 AM
Subject: reorg problems


> I have been trying to improve performance on a system that has been
showing
> excessive times in DB2. On inspection we found one table that seemed to
be
> the culprit. When I checked the statistics on it I found that it was
about
> 60% clustered and had no clustering index. The primary index is unique and
> could be clustering. I first reorged without changing the index and the
> index went from 60% to 49% clustered. I then altered the index to make it
> clustering & reorged again - but the results were the same ie: still 49%
> clustered. This is the only index on this table - does anyone have an idea
> as to why this is happening. The table is part of a Visual Image (now
> Content Manager) system. Part of the system (mainly the images tables) is
> on an NT server. The part that I am working with is on os390 (2.8) db2 v6
> and is mainly indexes to the data on NT.
> Bud Greenman
> Programmer Analyst
>
>
>





Dominic MORTIMER

Re: reorg problems
(in response to michael bell)
Making the only index clustering will not make any difference as if there is
no index specifically defined as clustering DB2 will make the first index
created clustering. In your case if there is only one index then this will
by default be the clustering index.

It is also my understanding that reorging a tablespace will reorg all
indexes.

It does however, seem strange that the cluster ratio went down after a
reorg, have you checked that the index and tablespace are in a low number of
extents. A high number of extents could explain disorganised data, even
after a reorg.

Cheers

Dom

-----Original Message-----
From: Bruce, Mae [mailto:[login to unmask email]
Sent: 16 December 2002 16:27
To: [login to unmask email]
Subject: Re: reorg problems


And make sure the index was reorged in addition to the table.

-----Original Message-----
From: Todd Burch [mailto:[login to unmask email]
Sent: Monday, December 16, 2002 10:06 AM
To: [login to unmask email]
Subject: Re: reorg problems


Bud, you did run runstats after the reorg, didn't you?

Todd.

----- Original Message -----
From: "Bud Greenman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 16, 2002 9:39 AM
Subject: reorg problems


> I have been trying to improve performance on a system that has been
showing
> excessive times in DB2. On inspection we found one table that seemed
> to
be
> the culprit. When I checked the statistics on it I found that it was
about
> 60% clustered and had no clustering index. The primary index is unique
> and could be clustering. I first reorged without changing the index
> and the index went from 60% to 49% clustered. I then altered the
> index to make it clustering & reorged again - but the results were the
> same ie: still 49% clustered. This is the only index on this table -
> does anyone have an idea as to why this is happening. The table is
> part of a Visual Image (now Content Manager) system. Part of the
> system (mainly the images tables) is on an NT server. The part that I
> am working with is on os390 (2.8) db2 v6 and is mainly indexes to the
> data on NT. Bud Greenman Programmer Analyst
>
>
>
> visit


>












_________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding, printing,
or copying of this email is strictly prohibited.

If you have received this email in error please notify the
SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.
_________________________________________________________



Phil Grainger

Re: reorg problems
(in response to Dominic MORTIMER)
Did no-one else notice he is on NT???

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: michael bell [mailto:[login to unmask email]
Sent: 16 December 2002 16:29
To: [login to unmask email]
Subject: Re: [DB2-L] reorg problems


First thing to check is whether you reorged the index or the tablespace. An
index reorg won't change the sequence of data in the tablespace.
If it is only 49% clustered, I would recommend SORTDATA for the tablespace
reorg.

Mike Bell
HLS Technologies

----- Original Message -----
From: "Bud Greenman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 16, 2002 9:39 AM
Subject: reorg problems


> I have been trying to improve performance on a system that has been
showing
> excessive times in DB2. On inspection we found one table that seemed to
be
> the culprit. When I checked the statistics on it I found that it was
about
> 60% clustered and had no clustering index. The primary index is unique and
> could be clustering. I first reorged without changing the index and the
> index went from 60% to 49% clustered. I then altered the index to make it
> clustering & reorged again - but the results were the same ie: still 49%
> clustered. This is the only index on this table - does anyone have an idea
> as to why this is happening. The table is part of a Visual Image (now
> Content Manager) system. Part of the system (mainly the images tables) is
> on an NT server. The part that I am working with is on os390 (2.8) db2 v6
> and is mainly indexes to the data on NT.
> Bud Greenman
> Programmer Analyst
>
>
>







[login to unmask email]

Re: reorg problems
(in response to Phil Grainger)
Yes I ran the runstats with the reorg job then reran them since I didn't
believe the results

Bud Greenman
Programmer Analyst



Todd Burch
<[login to unmask email] To: [login to unmask email]
USA.COM> cc:
Sent by: DB2 Data Subject: Re: reorg problems
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


12/16/2002 11:05
AM
Please respond to
DB2 Data Base
Discussion List






Bud, you did run runstats after the reorg, didn't you?

Todd.

----- Original Message -----
From: "Bud Greenman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 16, 2002 9:39 AM
Subject: reorg problems


> I have been trying to improve performance on a system that has been
showing
> excessive times in DB2. On inspection we found one table that seemed to
be
> the culprit. When I checked the statistics on it I found that it was
about
> 60% clustered and had no clustering index. The primary index is unique
and
> could be clustering. I first reorged without changing the index and the
> index went from 60% to 49% clustered. I then altered the index to make
it
> clustering & reorged again - but the results were the same ie: still 49%
> clustered. This is the only index on this table - does anyone have an
idea
> as to why this is happening. The table is part of a Visual Image (now
> Content Manager) system. Part of the system (mainly the images tables)
is
> on an NT server. The part that I am working with is on os390 (2.8) db2
v6
> and is mainly indexes to the data on NT.
> Bud Greenman
> Programmer Analyst
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can

>



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



[login to unmask email]

Re: reorg problems
(in response to BudGreenman@ONGOV.NET)
Thanks. The problem was indeed that the index was into extents. After
altering the index allocation & reorging it is now 100% clustered.
Bud Greenman
Programmer Analyst



"MORTIMER, Dominic"
<[login to unmask email] To: [login to unmask email]
MA.SLB.COM> cc:
Sent by: DB2 Data Base Subject: Re: reorg problems
Discussion List
<[login to unmask email]>


12/16/2002 11:56 AM
Please respond to DB2 Data
Base Discussion List






Making the only index clustering will not make any difference as if there
is
no index specifically defined as clustering DB2 will make the first index
created clustering. In your case if there is only one index then this will
by default be the clustering index.

It is also my understanding that reorging a tablespace will reorg all
indexes.

It does however, seem strange that the cluster ratio went down after a
reorg, have you checked that the index and tablespace are in a low number
of
extents. A high number of extents could explain disorganised data, even
after a reorg.

Cheers

Dom

-----Original Message-----
From: Bruce, Mae [mailto:[login to unmask email]
Sent: 16 December 2002 16:27
To: [login to unmask email]
Subject: Re: reorg problems


And make sure the index was reorged in addition to the table.

-----Original Message-----
From: Todd Burch [mailto:[login to unmask email]
Sent: Monday, December 16, 2002 10:06 AM
To: [login to unmask email]
Subject: Re: reorg problems


Bud, you did run runstats after the reorg, didn't you?

Todd.

----- Original Message -----
From: "Bud Greenman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 16, 2002 9:39 AM
Subject: reorg problems


> I have been trying to improve performance on a system that has been
showing
> excessive times in DB2. On inspection we found one table that seemed
> to
be
> the culprit. When I checked the statistics on it I found that it was
about
> 60% clustered and had no clustering index. The primary index is unique
> and could be clustering. I first reorged without changing the index
> and the index went from 60% to 49% clustered. I then altered the
> index to make it clustering & reorged again - but the results were the
> same ie: still 49% clustered. This is the only index on this table -
> does anyone have an idea as to why this is happening. The table is
> part of a Visual Image (now Content Manager) system. Part of the
> system (mainly the images tables) is on an NT server. The part that I
> am working with is on os390 (2.8) db2 v6 and is mainly indexes to the
> data on NT. Bud Greenman Programmer Analyst
>
>
>
> visit
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can

>



the





the




_________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding, printing,
or copying of this email is strictly prohibited.

If you have received this email in error please notify the
SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.
_________________________________________________________



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can