Index reorg selection criteria.

Ravi Karnati

Index reorg selection criteria.
Hi Group,
Our current strategy for selecting Index reorg candidates is based on the LEAFFAR from the RTS tables (SYSIBM.INDEXSPACESTATS) and we are debating on whether we should include/check LEAFDIST from the SYSINDEXPART table.
Any input is greatly appreciated.
This is our current selection strategy from RTS tables.
SELECT DISTINCT
A.DBNAME, A.INDEXSPACE
, SUBSTR(B.CREATOR,1,8) AS XCREATOR
, SUBSTR(B.NAME ,1,8) AS XNAME
, (DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))
AS PCT_RELOCATED
FROM
SYSIBM.INDEXSPACESTATS AS A
, SYSIBM.SYSINDEXES AS B
WHERE A.DBID = B.DBID
AND A.ISOBID = B.ISOBID
AND A.DBNAME = B.DBNAME
AND A.INDEXSPACE = B.INDEXSPACE
AND A.DBNAME LIKE 'XXXXX'
AND A.NACTIVE > 0
AND A.TOTALENTRIES > 9999
GROUP BY A.DBNAME, A.INDEXSPACE, B.CREATOR, B.NAME
HAVING
((DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))
> 4.9 )
ORDER BY
A.DBNAME, A.INDEXSPACE ;

And this is from IBM's website.

Use the following query to identify user-created indexes and DB2 catalog indexes that you should consider reorganizing with the REORG INDEX utility:
EXEC SQL
SELECT IXNAME, IXCREATOR
FROM SYSIBM.SYSINDEXPART
WHERE LEAFDIST > 200
ENDEXEC
End of Product-sensitive Programming Interface
Be aware that using a LEAFDIST value of more than 200 as an indicator of a disorganized index is merely a rough guideline for general cases. This guidance is not absolute. In some cases, 200 is an acceptable value for LEAFDIST. For example, with FREEPAGE 0 and index page splitting, the LEAFDIST value can climb sharply. In this case, a LEAFDIST value that exceeds 200 can be acceptable.


Thanks,
Ravi .


This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to 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 Listserv

Roy Boxwell

Re: Index reorg selection criteria.
(in response to Ravi Karnati)
no - that implies that you RUNSTAT the world! Crazy with RTS! Just make
sure you use the other RTS columns for Percentage Changed, Percentage
Appended, Percentage PSEUDO deletes, MASS Deletes and LEVELS Changed all
of these especially Pseudo, Levels and MASS Delete are pointers to an
immediate IX Reorg requirement

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



"Karnati, Ravindra X" <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
09.02.2011 04:33
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Re: [DB2-L] Index reorg selection criteria.






Hi Group,
Our current strategy for selecting Index reorg candidates is based on the
LEAFFAR from the RTS tables (SYSIBM.INDEXSPACESTATS) and we are debating
on whether we should include/check LEAFDIST from the SYSINDEXPART table.
Any input is greatly appreciated.
This is our current selection strategy from RTS tables.
SELECT DISTINCT
A.DBNAME, A.INDEXSPACE
, SUBSTR(B.CREATOR,1,8) AS XCREATOR
, SUBSTR(B.NAME ,1,8) AS XNAME
, (DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))
AS PCT_RELOCATED
FROM
SYSIBM.INDEXSPACESTATS AS A
, SYSIBM.SYSINDEXES AS B
WHERE A.DBID = B.DBID
AND A.ISOBID = B.ISOBID
AND A.DBNAME = B.DBNAME
AND A.INDEXSPACE = B.INDEXSPACE
AND A.DBNAME LIKE 'XXXXX'
AND A.NACTIVE > 0
AND A.TOTALENTRIES > 9999
GROUP BY A.DBNAME, A.INDEXSPACE, B.CREATOR, B.NAME
HAVING
((DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))
> 4.9 )
ORDER BY
A.DBNAME, A.INDEXSPACE ;

And this is from IBM’s website.

Use the following query to identify user-created indexes and DB2 catalog
indexes that you should consider reorganizing with the REORG INDEX
utility:
EXEC SQL
SELECT IXNAME, IXCREATOR
FROM SYSIBM.SYSINDEXPART
WHERE LEAFDIST > 200
ENDEXEC
End of Product-sensitive Programming Interface
Be aware that using a LEAFDIST value of more than 200 as an indicator of a
disorganized index is merely a rough guideline for general cases. This
guidance is not absolute. In some cases, 200 is an acceptable value for
LEAFDIST. For example, with FREEPAGE 0 and index page splitting, the
LEAFDIST value can climb sharply. In this case, a LEAFDIST value that
exceeds 200 can be acceptable.


Thanks,
Ravi .
This communication is for informational purposes only. It is not intended
as an offer or solicitation for the purchase or sale of any financial
instrument or as an official confirmation of any transaction. All market
prices, data and other information are not warranted as to completeness or
accuracy and are subject to change without notice. Any comments or
statements made herein do not necessarily reflect those of JPMorgan Chase
& Co., its subsidiaries and affiliates. This transmission may contain
information that is privileged, confidential, legally privileged, and/or
exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or use of the information contained herein (including any
reliance thereon) is STRICTLY PROHIBITED. Although this transmission and
any attachments are believed to be free of any virus or other defect that
might affect any computer system into which it is received and opened, it
is the responsibility of the recipient to ensure that it is virus free and
no responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
and affiliates, as applicable, for any loss or damage arising in any way
from its use. If you received this transmission in error, please
immediately contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you. Please refer to
http://www.jpmorgan.com/pages/disclosures for disclosures relating to
European legal entities.



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 * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Roger Hecq

Re: Index reorg selection criteria.
(in response to Roy Boxwell)
My experience has been that LeafDist can have a great impact on
performance for applications using that index for access. That should
be obvious to most, but ... You should be able to establish a
threshhold value fairly easily, by monitoring the increase in LeafDist
and the response time for the dependant SQL. You will probably note
that the value for LeafDist increases exponentially and your threshhold
will be at the point where value increases sharply.

In your case, LeafFar, may be the only factor that you need to monitor,
but you will need to determine that from your performance data.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Karnati,
Ravindra X
Sent: Tuesday, February 08, 2011 10:33 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Index reorg selection criteria.



Hi Group,

Our current strategy for selecting Index reorg candidates is based on
the LEAFFAR from the RTS tables (SYSIBM.INDEXSPACESTATS) and we are
debating on whether we should include/check LEAFDIST from the
SYSINDEXPART table.

Any input is greatly appreciated.

This is our current selection strategy from RTS tables.

SELECT DISTINCT

A.DBNAME, A.INDEXSPACE

, SUBSTR(B.CREATOR,1,8) AS XCREATOR

, SUBSTR(B.NAME ,1,8) AS XNAME

, (DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))

AS PCT_RELOCATED

FROM

SYSIBM.INDEXSPACESTATS AS A

, SYSIBM.SYSINDEXES AS B


WHERE A.DBID = B.DBID

AND A.ISOBID = B.ISOBID

AND A.DBNAME = B.DBNAME

AND A.INDEXSPACE = B.INDEXSPACE

AND A.DBNAME LIKE 'XXXXX'

AND A.NACTIVE > 0

AND A.TOTALENTRIES > 9999


GROUP BY A.DBNAME, A.INDEXSPACE, B.CREATOR, B.NAME

HAVING

((DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))

> 4.9 )

ORDER BY

A.DBNAME, A.INDEXSPACE ;



And this is from IBM's website.



Use the following query to identify user-created indexes and DB2 catalog
indexes that you should consider reorganizing with the REORG INDEX
utility:

EXEC SQL

SELECT IXNAME, IXCREATOR

FROM SYSIBM.SYSINDEXPART

WHERE LEAFDIST > 200

ENDEXEC

End of Product-sensitive Programming Interface

Be aware that using a LEAFDIST value of more than 200 as an indicator of
a disorganized index is merely a rough guideline for general cases. This
guidance is not absolute. In some cases, 200 is an acceptable value for
LEAFDIST. For example, with FREEPAGE 0 and index page splitting, the
LEAFDIST value can climb sharply. In this case, a LEAFDIST value that
exceeds 200 can be acceptable.





Thanks,

Ravi .

This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of any
financial instrument or as an official confirmation of any transaction.
All market prices, data and other information are not warranted as to
completeness or accuracy and are subject to change without notice. Any
comments or statements made herein do not necessarily reflect those of
JPMorgan Chase & Co., its subsidiaries and affiliates. This transmission
may contain information that is privileged, confidential, legally
privileged, and/or exempt from disclosure under applicable law. If you
are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution, or use of the information contained
herein (including any reliance thereon) is STRICTLY PROHIBITED. Although
this transmission and any attachments are believed to be free of any
virus or other defect that might affect any computer system into which
it is received and opened, it is the responsibility of the recipient to
ensure that it is virus free and no responsibility is accepted by
JPMorgan Chase & Co., its subsidiaries and affiliates, as applicable,
for any loss or damage arising in any way from its use. If you received
this transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard copy
format. Thank you. Please refer to
http://www.jpmorgan.com/pages/disclosures for disclosures relating to
European legal entities.


________________________________

Independent, not-for-profit, User Run - the IDUG difference!
< 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 * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Larry Kintisch

Re: Index reorg selection criteria.
(in response to Roger Hecq)
Hi Ravi and others,

I've presented at a few IDUG RUGS over the past 8 years, and the
section of my talk about index reorganization comes from Chapter 11
of Tapio Lahdenmaki's book "Relational Database Index Design and the
Optimizers" [WILEY, 2004] and his course, that I teach in the USA,
"Cost-Saving Database Index Design".

I'd suggest that you use his classification of short, medium and
large index key-lengths and understand that for single matching key
access [like "primary_key_col = ?"] splits don't matter for performance.

However if your index access path includes an index scan of a
"slice" of the index [like "payments by customers this month"], or a
non-matching index scan, then your expectation of sequential index
key "touches" will become occasionally, and then frequently,
synchronous reads [estimated at 10MS each]. One percent splits might
make a 100MS response time double and CPU cost will shoot up!

Tapio presents guidelines he and the IBM performance team
developed about 12 years ago. These include statistical methods of
allocating freespace and reorg frequency. Even if some parts of the
guidelines may be sometimes obsoleted by today's very large buffer
pools and huge DASD subsystem caches [that were not in use 12 years
ago], the guidelines remain a very useful starting point, from
whatever stats you choose as your reorg trigger.

By the way the book is part of my offering of the 2-day class in
the USA, [contact me off line for information] and if you want a copy
of the book, mail a $70.00 check to me [postage and sales tax included]:
Pay to: Lawrence Kintisch, 208 Hilltop Dr, Nyack, NY 10960-1119

Hope this helps.

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 22:33 2011-02-08, you wrote:
>Hi Group,
>Our current strategy for selecting Index reorg candidates is based
>on the LEAFFAR from the RTS tables (SYSIBM.INDEXSPACESTATS) and we
>are debating on whether we should include/check LEAFDIST from the
>SYSINDEXPART table.
>Any input is greatly appreciated.
>This is our current selection strategy from RTS tables.
>SELECT DISTINCT
> A.DBNAME, A.INDEXSPACE
> , SUBSTR(B.CREATOR,1,8) AS XCREATOR
> , SUBSTR(B.NAME ,1,8) AS XNAME
> , (DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))
> AS PCT_RELOCATED
>FROM
> SYSIBM.INDEXSPACESTATS AS A
> , SYSIBM.SYSINDEXES AS
> B
>WHERE A.DBID = B.DBID
> AND A.ISOBID = B.ISOBID
> AND A.DBNAME = B.DBNAME
> AND A.INDEXSPACE = B.INDEXSPACE
> AND A.DBNAME LIKE 'XXXXX'
> AND A.NACTIVE > 0
> AND A.TOTALENTRIES >
> 9999
>
> GROUP BY A.DBNAME, A.INDEXSPACE, B.CREATOR, B.NAME
> HAVING
> ((DOUBLE(SUM(A.REORGLEAFFAR))*100) / DOUBLE(SUM(A.NACTIVE))
> > 4.9 )
> ORDER BY
> A.DBNAME, A.INDEXSPACE ;
>
>And this is from IBM's website.
>
>Use the following query to identify user-created indexes and DB2
>catalog indexes that you should consider reorganizing with the REORG
>INDEX utility:
>EXEC SQL
>SELECT IXNAME, IXCREATOR
>FROM SYSIBM.SYSINDEXPART
> WHERE LEAFDIST > 200
>ENDEXEC
>End of Product-sensitive Programming Interface
>Be aware that using a LEAFDIST value of more than 200 as an
>indicator of a disorganized index is merely a rough guideline for
>general cases. This guidance is not absolute. In some cases, 200 is
>an acceptable value for LEAFDIST. For example, with FREEPAGE 0 and
>index page splitting, the LEAFDIST value can climb sharply. In this
>case, a LEAFDIST value that exceeds 200 can be acceptable.
>
>
>Thanks,
>Ravi .
>
>No virus found in this message.
>Checked by AVG - < http://www.avg.com > www.avg.com
>Version: 10.0.1204 / Virus Database: 1435/3432 - Release Date: 02/09/11
>
>This communication is for informational purposes only. It is not
>intended as an offer or solicitation for the purchase or sale of any
>financial instrument or as an official confirmation of any
>transaction. All market prices, data and other information are not
>warranted as to completeness or accuracy and are subject to change
>without notice. Any comments or statements made herein do not
>necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
>and affiliates. This transmission may contain information that is
>privileged, confidential, legally privileged, and/or exempt from
>disclosure under applicable law. If you are not the intended
>recipient, you are hereby notified that any disclosure, copying,
>distribution, or use of the information contained herein (including
>any reliance thereon) is STRICTLY PROHIBITED. Although this
>transmission and any attachments are believed to be free of any
>virus or other defect that might affect any computer system into
>which it is received and opened, it is the responsibility of the
>recipient to ensure that it is virus free and no responsibility is
>accepted by JPMorgan Chase & Co., its subsidiaries and affiliates,
>as applicable, for any loss or damage arising in any way from its
>use. If you received this transmission in error, please immediately
>contact the sender and destroy the material in its entirety, whether
>in electronic or hard copy format. Thank you. Please refer to
>http://www.jpmorgan.com/pages/disclosures for disclosures relating
>to European legal entities.
>
>
> < http://www.idug.org >
>Independent, not-for-profit, User Run - the IDUG difference!
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If
>you are not already an IDUG member,
> < http://www.idug.org/register > please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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