Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: are these indexes being used?

    DB2 9.5 LUW

    Is there a catalog table or something that I can query to find out which indexes are being utilized?

    We have spent the last year or so building up our Data Mart and in looking at all of the indexes that we have deployed - I'm thinking we are not utilizing all of them.

    Is there any way to report on this?

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    It was just talked about it last week I think.

    db2pd is the best tool to hunt for unused indexes:
    Code:

    db2pd -db sample -tcbstats index

    Look for the "TCB Index Stats:" header and the "Scans" column.
    This will tell you how many times DB2 used that index. If the counter is ZERO, DB2 did not need that index at all so.......
    Be ware:
    1 - when a database stops and starts again all the counters are reset to ZERO
    2 - beware of FK contraints. The index might be required as FK.

    You do need SYSADM access
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    I know this is going to sound stupid.. but what is the correct syntax to run this from COMMAND EDITOR?

    I try and run it like this:

    db2pd -db PRODDM -tcbstats index

    and I get this:

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "db2pd -db" was found following
    "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
    SQLSTATE=42601

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    Thank you!

  6. #6
    Join Date
    Dec 2007
    Posts
    288
    Can anyone tell me how to find the tableid? I need it to run DB2PD like this:


    db2pd -db PRODDM -tcbstats index tbspaceid = 3 tableid = xxx

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by itsonlyme44
    Can anyone tell me how to find the tableid?
    It is in SYSCAT.TABLES, obviously.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Dec 2007
    Posts
    288
    Thank you!!! I missed that... I work more with SQL Server and was looking for an object table...

    anyway.. can anyone help me interpret the output from this:

    Code:
    >db2pd -db PRODDM -tcbstats index  > c:\PRODDM_Stats.txt
    Code:
    TCB Index Stats:
    Address    TableName          IID   EmpPgDel   RootSplits BndrySplts PseuEmptPg Scans      KeyUpdates InclUpdats NonBndSpts PgAllocs   Merges     PseuDels   DelClean   IntNodSpl  0          0          0          0          0          0          0          0          0          0          0          0          0          
    0x59456DA8 T9DAT              2     0          0          0          0          16         0          0          0          0          0          0          0          0          
    0x59456DA8 T9DAT              1     0          0          0          0          9          0          0          0          0          0          0          0          0          
    0x59458028 T9PH               1     0          0          0          0          0          0          0          0          0          0          0          0          0          
    0x59455728 T9PSC              2     0          0          0          0          26         0          0          0          0          0          0          0          0          
    0x59455728 T9PSC              1     0          0          0          0          1          0          0          0          0          0          0          0          0          
    0x5945EC28 T9PSR              2     0          0          0          0          2          0          0          0          0          0          0          0          0          
    0x5945EC28 T9PSR              1     0          0          0          0          0          0          0          0          0          0          0          0          0
    For instance I have mulitiple indexes on the tables listed.. so how do I know which indexes are being used?

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    this information is only current from the last time instance was cycled.

    As I understand "scans" column tells you number of times index was used. You might not want to base your decision based on one output. I would collect the info for at least a couple of month and decide then.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Cougar8000
    this information is only current from the last time instance was cycled.
    I'd say it has accumulated since the time of the database activation, which may or may not coincide with the start of the instance.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Dec 2007
    Posts
    288
    I see now.. the IID is the index ID. lots of crosschecking involved to figure out which index is which (we have a couple hundred tables in our datamart with up to 5 indexes on each table) but the information on index useage is well worth the trouble.. thank you for your help

Posting Permissions

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