Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: DB2 Unused indexes

    I want to know about the Indexes which unused from last 6 months.

    Please share/suggest me the process and how to fetch unused indexes, So that we can remove out them easily for enhancing performance.

    And What does it means if LASTUSED Column in SYSCAT.INDEXES is showing specific date value.


    thanks
    Rahul
    Last edited by Rahulg; 06-10-13 at 08:12. Reason: more information

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    LASTUSED is the last time an index was used. That should be sufficient for your needs.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2013
    Posts
    3
    @Marcus:- So shall I remove those indexes which are showing last year's date value in it?
    As the version is 9.7 ESE.

    I think Real time statistic (RTS) updates the LASTUSED information in the DB2 catalog.

    Please suggest....
    Is there any other way to find unused indexes or shall i go for it.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    LASTUSED is updated semi-realtime via background task that you can see if you run: db2 list applications show detail.

    I would list out all the indexes not used in the last year, and look at the columns to make sure you understand why it is not used, and then delete them if you think you understand why they are not being used.

    Be aware that some small tables may have unique indexes on them that have never been used, but are needed just in case a new row is added to ensure that the PK uniqueness is enforced.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2013
    Posts
    3
    Agreed Sir,

    Yes, are you saying some small tables to system tables.

    correct I will not work on any system generated tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •