Indexes not used

Vanitha Subramaniam

Indexes not used
Hi all,

I am running Explain on all the queries accessing the table and found that some of the indexes are not used. We are on V8. In V9 we can assure this by SYSIBM.SYSINDEXSPACESTATS. LASTUSED column.

Is there a way that we can assure that the indexes are not used for accessing , then we can recommend for deleting the index.

Thanks,
Vanitha



________________________________
DISCLAIMER:
This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.


______________________________________________________________________

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



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

SUBSCRIBE DB2-L Muthuraj

Re: Indexes not used
(in response to Vanitha Subramaniam)
Actually the table you have mentioned is used for RTS. Even in V8, you can
enable the RTS and you can use the table.

But I have some questions about using this column. Definitely this column is
useful. But to what extend?

If an index is not used for last 6 months, does it mean that it wont be used
forever?
Not at all. If you extract the information from mentioned table after 6
months, it will say you whether the index is used in last 6 months only.

To decide the obsolette indexes, you have to have more knowledge about your
system. From SYSPACKDEP, you can decide which all programs are using the indexes
and but dynamic SQLS , you may not get this information. In that case, i
guess you can use LASTUSED column to get that info.

Regards,
Muthu


______________________________________________________________________

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



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

Avram Friedman

Re: Indexes not used
(in response to SUBSCRIBE DB2-L Muthuraj)
The short answer is no.
Your analysis just provides information on most (not all) static access to the
indexs. In addition provides no information on dynamic access.

There are things you can do too improve your comfort level about droping
these indexs.

1. is the high level key in the index the same as any other high level key for
indexes on the same table. Unless you jump through hoops with RUNSTATS
and most people dont many optioizer funtions only liik at the high level key.
2. scan your application source libs including those on distributed platforms for
the column names of the first 2 or 3 key columns. Any hits associated with
any of the following clauses ... where, order, group
3. checks views for use of the index as well.


Could you discuss what you expect to save from the elimination of the indexes.
Is it mostly an astectics issue. Or are you in a serious DASD, CPU, I/O,
MEMORY constrained environment and all other more reasonable approaches
have failed.

Have you heard the saying "If it ain't broke, don't fix it"

Av Friedman


On Wed, 31 Dec 2008 12:42:11 +0530, Vanitha_Subramaniam
<[login to unmask email]> wrote:

>Hi all,
>
>I am running Explain on all the queries accessing the table and found that
some of the indexes are not used. We are on V8. In V9 we can assure this by
SYSIBM.SYSINDEXSPACESTATS. LASTUSED column.
>
>Is there a way that we can assure that the indexes are not used for
accessing , then we can recommend for deleting the index.
>
>Thanks,
>Vanitha
>
>
>
>________________________________
>DISCLAIMER:
>This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying
or distribution or forwarding of any or all of the contents in this message is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard is
appreciated.
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>_________________________________________________________________
_____
>
>
>
>IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html
>


______________________________________________________________________

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



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

David Simpson

Re: Indexes not used
(in response to Avram Friedman)
I do not believe this information is limited to static SQL. My testing shows the date updated for dynamic access. The doc on the column says:

The date when the index is used for SELECT, FETCH,
searched UPDATE, searched DELETE, or used to enforce
referential integrity constraints

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Avram Friedman
Sent: Wednesday, December 31, 2008 11:17 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Indexes not used

The short answer is no.
Your analysis just provides information on most (not all) static access to the
indexs. In addition provides no information on dynamic access.

There are things you can do too improve your comfort level about droping
these indexs.

1. is the high level key in the index the same as any other high level key for
indexes on the same table. Unless you jump through hoops with RUNSTATS
and most people dont many optioizer funtions only liik at the high level key.
2. scan your application source libs including those on distributed platforms for
the column names of the first 2 or 3 key columns. Any hits associated with
any of the following clauses ... where, order, group
3. checks views for use of the index as well.


Could you discuss what you expect to save from the elimination of the indexes.
Is it mostly an astectics issue. Or are you in a serious DASD, CPU, I/O,
MEMORY constrained environment and all other more reasonable approaches
have failed.

Have you heard the saying "If it ain't broke, don't fix it"

Av Friedman


On Wed, 31 Dec 2008 12:42:11 +0530, Vanitha_Subramaniam
<[login to unmask email]> wrote:

>Hi all,
>
>I am running Explain on all the queries accessing the table and found that
some of the indexes are not used. We are on V8. In V9 we can assure this by
SYSIBM.SYSINDEXSPACESTATS. LASTUSED column.
>
>Is there a way that we can assure that the indexes are not used for
accessing , then we can recommend for deleting the index.
>
>Thanks,
>Vanitha
>
>
>
>________________________________
>DISCLAIMER:
>This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying
or distribution or forwarding of any or all of the contents in this message is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard is
appreciated.
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>_________________________________________________________________
_____
>
>
>
>IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html
>


______________________________________________________________________

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



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


______________________________________________________________________

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



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

Avram Friedman

Re: Indexes not used
(in response to David Simpson)
As I read the original posting
The lister 'determined' an index was not used by looking at explains for index
references.
When finding none we wanted to know if the RTS Lastused column could be
used to verify that the index was not used.

My controburtion was the explain (or sysplandep or syspackdep) can not
prove an index is not used because it only mesures SOME static SQL and no
dynamic SQL.

for not quite an identical reason the LASTUSED time in RTS also can not be
used. The index will be used for ALL base table inserts ALL base table deletes
and many updates. So if the base table changes all the indexes are used (an
over simpflication I know)

By the way this is one of the reasons why explain or the dependency tables
can not be trusted for this use even for static SQL. All DELETES and ALL
inserts to a base table make changes to the related indexes but these indexes
only appear as dependencies if they are used directly by the Insert or Delete,
indirect requirements are not considered explainable events. ... In a DELETE
WHERE the indexes that appear in the explain are those involved in satisifying
the WHERE condition but ALL the indexes are modified. Now add RI into the
mix and it gets worse.

Av Friedman
On Wed, 31 Dec 2008 13:37:43 -0500, David Simpson
<[login to unmask email]> wrote:

>I do not believe this information is limited to static SQL. My testing shows
the date updated for dynamic access. The doc on the column says:
>
>The date when the index is used for SELECT, FETCH,
>searched UPDATE, searched DELETE, or used to enforce
>referential integrity constraints
>
>David Simpson
>Senior Technical Advisor
>Themis Training
>[login to unmask email]
>http://www.themisinc.com
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Avram Friedman
>Sent: Wednesday, December 31, 2008 11:17 AM
>To: [login to unmask email]
>Subject: Re: [DB2-L] Indexes not used
>
>The short answer is no.
>Your analysis just provides information on most (not all) static access to the
>indexs. In addition provides no information on dynamic access.
>
>There are things you can do too improve your comfort level about droping
>these indexs.
>
>1. is the high level key in the index the same as any other high level key for
>indexes on the same table. Unless you jump through hoops with RUNSTATS
>and most people dont many optioizer funtions only liik at the high level key.
>2. scan your application source libs including those on distributed platforms
for
>the column names of the first 2 or 3 key columns. Any hits associated with
>any of the following clauses ... where, order, group
>3. checks views for use of the index as well.
>
>
>Could you discuss what you expect to save from the elimination of the
indexes.
>Is it mostly an astectics issue. Or are you in a serious DASD, CPU, I/O,
>MEMORY constrained environment and all other more reasonable approaches
>have failed.
>
>Have you heard the saying "If it ain't broke, don't fix it"
>
>Av Friedman
>
>
>On Wed, 31 Dec 2008 12:42:11 +0530, Vanitha_Subramaniam
><[login to unmask email]> wrote:
>
>>Hi all,
>>
>>I am running Explain on all the queries accessing the table and found that
>some of the indexes are not used. We are on V8. In V9 we can assure this by
>SYSIBM.SYSINDEXSPACESTATS. LASTUSED column.
>>
>>Is there a way that we can assure that the indexes are not used for
>accessing , then we can recommend for deleting the index.
>>
>>Thanks,
>>Vanitha
>>
>>
>>
>>________________________________
>>DISCLAIMER:
>>This email (including any attachments) is intended for the sole use of the
>intended recipient/s and may contain material that is CONFIDENTIAL AND
>PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying
>or distribution or forwarding of any or all of the contents in this message is
>STRICTLY PROHIBITED. If you are not the intended recipient, please contact
>the sender by email and delete all copies; your cooperation in this regard is
>appreciated.
>>
>>
>>________________________________________________________________
_
>_____
>>
>>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>>________________________________________________________________
_
>_____
>>
>>
>>
>>IDUG.org was recently updated requiring members to use a new password.
>You should have gotten an e-mail with the temporary password assigned to
>your account. Please log in and update your member profile. If you are not
>already an IDUG.org member, please register at
>http://www.idug.org/component/juser/register.html
>>
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>_________________________________________________________________
_____
>
>
>
>IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>_________________________________________________________________
_____
>
>
>
>IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

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



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

David Simpson

Re: Indexes not used
(in response to Avram Friedman)
Sorry, I misread your post. Certainly the plan table is of limited value in making this determination, especially if you have packages that haven't been bound in a long time or without EXPLAIN(YES) or ANY dynamic SQL. The RTS column should be helpful in filling some (but not all) of these gaps.

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Avram Friedman
Sent: Wednesday, December 31, 2008 2:07 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Indexes not used

As I read the original posting
The lister 'determined' an index was not used by looking at explains for index
references.
When finding none we wanted to know if the RTS Lastused column could be
used to verify that the index was not used.

My controburtion was the explain (or sysplandep or syspackdep) can not
prove an index is not used because it only mesures SOME static SQL and no
dynamic SQL.

for not quite an identical reason the LASTUSED time in RTS also can not be
used. The index will be used for ALL base table inserts ALL base table deletes
and many updates. So if the base table changes all the indexes are used (an
over simpflication I know)

By the way this is one of the reasons why explain or the dependency tables
can not be trusted for this use even for static SQL. All DELETES and ALL
inserts to a base table make changes to the related indexes but these indexes
only appear as dependencies if they are used directly by the Insert or Delete,
indirect requirements are not considered explainable events. ... In a DELETE
WHERE the indexes that appear in the explain are those involved in satisifying
the WHERE condition but ALL the indexes are modified. Now add RI into the
mix and it gets worse.

Av Friedman
On Wed, 31 Dec 2008 13:37:43 -0500, David Simpson
<[login to unmask email]> wrote:

>I do not believe this information is limited to static SQL. My testing shows
the date updated for dynamic access. The doc on the column says:
>
>The date when the index is used for SELECT, FETCH,
>searched UPDATE, searched DELETE, or used to enforce
>referential integrity constraints
>
>David Simpson
>Senior Technical Advisor
>Themis Training
>[login to unmask email]
>http://www.themisinc.com
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Avram Friedman
>Sent: Wednesday, December 31, 2008 11:17 AM
>To: [login to unmask email]
>Subject: Re: [DB2-L] Indexes not used
>
>The short answer is no.
>Your analysis just provides information on most (not all) static access to the
>indexs. In addition provides no information on dynamic access.
>
>There are things you can do too improve your comfort level about droping
>these indexs.
>
>1. is the high level key in the index the same as any other high level key for
>indexes on the same table. Unless you jump through hoops with RUNSTATS
>and most people dont many optioizer funtions only liik at the high level key.
>2. scan your application source libs including those on distributed platforms
for
>the column names of the first 2 or 3 key columns. Any hits associated with
>any of the following clauses ... where, order, group
>3. checks views for use of the index as well.
>
>
>Could you discuss what you expect to save from the elimination of the
indexes.
>Is it mostly an astectics issue. Or are you in a serious DASD, CPU, I/O,
>MEMORY constrained environment and all other more reasonable approaches
>have failed.
>
>Have you heard the saying "If it ain't broke, don't fix it"
>
>Av Friedman
>
>
>On Wed, 31 Dec 2008 12:42:11 +0530, Vanitha_Subramaniam
><[login to unmask email]> wrote:
>
>>Hi all,
>>
>>I am running Explain on all the queries accessing the table and found that
>some of the indexes are not used. We are on V8. In V9 we can assure this by
>SYSIBM.SYSINDEXSPACESTATS. LASTUSED column.
>>
>>Is there a way that we can assure that the indexes are not used for
>accessing , then we can recommend for deleting the index.
>>
>>Thanks,
>>Vanitha
>>
>>
>>
>>________________________________
>>DISCLAIMER:
>>This email (including any attachments) is intended for the sole use of the
>intended recipient/s and may contain material that is CONFIDENTIAL AND
>PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying
>or distribution or forwarding of any or all of the contents in this message is
>STRICTLY PROHIBITED. If you are not the intended recipient, please contact
>the sender by email and delete all copies; your cooperation in this regard is
>appreciated.
>>
>>
>>________________________________________________________________
_
>_____
>>
>>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>>________________________________________________________________
_
>_____
>>
>>
>>
>>IDUG.org was recently updated requiring members to use a new password.
>You should have gotten an e-mail with the temporary password assigned to
>your account. Please log in and update your member profile. If you are not
>already an IDUG.org member, please register at
>http://www.idug.org/component/juser/register.html
>>
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>_________________________________________________________________
_____
>
>
>
>IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html
>
>
>_________________________________________________________________
_____
>
>* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
>_________________________________________________________________
_____
>
>
>
>IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

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



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


______________________________________________________________________

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



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