Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Index stats data lookup

    Hi Experts

    I need to find some unused index in our system. For the detail analysis i need to get the
    > Performance Savings after deleting the index
    > Storage Savings after deleting the index

    Below is the query i use. Plz note that i can only use syscat or similar admin routines. Help me to
    find the index size [which will lead me to find the Storage size] or the above mention items directly

    Thanx

    select
    INDSCHEMA,INDNAME,TABNAME,COLNAMES,UNIQUERULE,COLC OUNT,INDEXTYPE,IID,NLEAF,CREATE_TIME,LASTUSED
    from SYSCAT.INDEXES
    where INDSCHEMA = 'TCSUPER'
    and UNIQUERULE != 'P'
    and UNIQUERULE != 'U'
    and SYSTEM_REQUIRED = 0
    and LASTUSED < '2012-12-31'
    and LASTUSED != '1/1/0001'

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Have a look at the ADMIN_GET_INDEX_INFO function.
    Note, that you are not able to get the size of some particular index on non-partitioned table from this function. You can get the size of all indexes for such a table only.
    See the description of the INDEX_OBJECT_*_SIZE fields for further details.
    Regards,
    Mark.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I'm not sure that a single query is enough to decide that an index should be dropped. I am also using db2advis and db2pd, like in this article: http://www.ibm.com/developerworks/da...b2unusedindex/

    On the other hand, storage saving is not a good reason to drop an index.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    hi,
    what is the os platform and db2 version. if you are using db2 luw v10.1 and later,
    the usage list function will show you whether the object was referecned by some statements.
    for example:
    create usage list usl_idx1 for index idx1.
    SET USAGE LIST idx1 state active;

    after some period ( maybe one day ),
    you can use the monitor function
    SELECT * FROM TABLE(MON_GET_INDEX_USAGE_LIST(null, null, -2))
    to find whether this index was used by some statements.

Posting Permissions

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