Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    427
    Provided Answers: 1

    Unanswered: How to know if an index is optim?

    Hi colleagues,
    There is some way to know if un index is optim, i am using db2 V9.7 and db2 V10.5.
    Thank you for advanced.
    DBA DB2 for LUW

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    Try to define optim.
    And then I guess you will find the answer by your own.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    You can ask db2advis utility to help you with this.
    Regards,
    Mark.

  4. #4
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    427
    Provided Answers: 1
    Hi,
    for me optim is an index very useful in a table for a query.
    There is values that indicate this index is correct or apropiate?
    On the table syscat.indexes there are fields that can help if an index is very optim?

    Thank you for advanced.

  5. #5
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    an index is "optim" when its needed by queries to quickly access data within your table. May not be "optim" if it only helps a single query.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    An index helps some queries, but might also slow down INSERTs and UPDATEs.

    You may want to use:
    - db2advis (something like -d database_name -a your_database_user/password -g -tables "TABNAME IN ('your_table_name')"
    - SYSCAT.INDEXES LASTUSED column
    - db2pd (something like db2pd -db your_database_name -tcbstats all tbspaceid=your_tablespace_ID tableid=your_table_ID -file result_file.txt)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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