Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18

    Unanswered: Indexes Evaluation

    Hello everybody.
    I am checking the database design of a legacy system and one of the mayor issues is the indexes efficiency. Basicly, I want to identify the indexes used and not used, in order to improve the firsts and drop the seconds. Can I determine that from the system catalog tables ?
    Thanks in advance for your help.
    Antonio L.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    It would help if you write something about your system: opetating system, DB2

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    SQL:
    SELECT * FROM SYSIBM.SYSINDEXES WHERE TBNAME='table_name'


    On Windows you can use Index Wizard in DB2 v7.2 or Index Advisor in DB2 v8.1. For Unix/Linux systems you can use db2advis command. By the way this command works in Windows DB2 command window too. You can also control the index creation on Unix with Windows DB2 Index Wizard (Advisor). I have tested this and it works prfect.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Indexes Evaluation

    AFAIK, there is not anywhere DB2 stores such statistical info(I think oracle 9i does)

    On LUW, as grofty suggets, you can use the index advisor ... But, as far as I am concerned, it is a starting point .... You will have to give accurate information(which is not always possible) for the index advisor to provide best results ...

    Cheers

    Sathyaram

    Originally posted by aloz
    Hello everybody.
    I am checking the database design of a legacy system and one of the mayor issues is the indexes efficiency. Basicly, I want to identify the indexes used and not used, in order to improve the firsts and drop the seconds. Can I determine that from the system catalog tables ?
    Thanks in advance for your help.
    Antonio L.

  5. #5
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    Hi guys, thanks for your support.
    Following the suggestion from Grofaty, here some tips about the system:
    - DB2 database
    - OS/390 (Mainframe)

    Because the sql statement's execution plan is determined when the Plan is assembled (binded), I thought the operations sequence and indexes used (if any) were stored into some system catalogs tables, like SYSPLAN, SYSPACKSTMT or SYSPACKAGES. Therefore, from this tables, it would be possible to know the indexes in use.

    Agian, thanks for your help.

    Antonio L.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I assume, if you do an explain of the statements (or packages), you might be able to see in the Explain Tables (not sure which table)

    But, the statistics of the index usage, ie, how frequent, is not stored in db2 , is what I meant (as I remember oracle 9i has a view for this)

    Cheers

    Sathyaram

    Originally posted by aloz
    Hi guys, thanks for your support.
    Following the suggestion from Grofaty, here some tips about the system:
    - DB2 database
    - OS/390 (Mainframe)

    Because the sql statement's execution plan is determined when the Plan is assembled (binded), I thought the operations sequence and indexes used (if any) were stored into some system catalogs tables, like SYSPLAN, SYSPACKSTMT or SYSPACKAGES. Therefore, from this tables, it would be possible to know the indexes in use.

    Agian, thanks for your help.

    Antonio L.

  7. #7
    Join Date
    May 2003
    Posts
    7
    If your pkgs/plans are bound EXPLAIN YES then you can use the following to query the assocated PLAN_TABLE to find out which indexes are not being used.


    SELECT T1.NAME, T1.CREATOR, T1.TBNAME
    FROM SYSIBM.SYSINDEXES T1
    WHERE T1.DBNAME LIKE '%%%%%'
    AND NOT EXISTS
    (SELECT 1
    FROM %CREATOR%.PLAN_TABLE
    WHERE ACCESSNAME = T1.NAME)


    Note that you have to substitue for DBNAME and CREATOR for PLAN_TABLE.

    Regards,
    Scott

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In the DB2 Administration Guide for Version 7, there is Chapter 33. Using EXPLAIN to improve SQL performance. You can get this manual in PDF format at the IBM docs website. If you are not using version 7, you can use the proper version manual which has similar information (not sure if chapter number is the same), but the concepts will be the same.

    Basically you will need to create a Plan_Table (DDL shipped with the product) and then rebind all plans/packages you want review with the EXPLAIN=YES option. This is a one time update to the Plan_Table if you do a REBIND. If you use BIND REPLACE, the plan table will be updated each time the plan is rebound in the future (which may not be desired).

    Then you can query the Plan_Table and it will tell you everything you want to know about how DB2 is accessing the data and whether indexes are being used, etc (if you learn how to understand the columns in the Plan_Table). There is some learning curve here, but worth it if you can apply it to other similar situations. If not, maybe get some short-term help from a qualified person who can very quickly analyze the situation for you. This usually can be done by someone accessing your system remotely.

Posting Permissions

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