Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    54

    Unanswered: Database Indexes Usages Tracking

    Hi,
    I have few question related to tracking of Indexes usages on DB2.

    (1) How to get information about unused indexes on database?
    (2) How to get information about missing indexes\ Required indexes on database?

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in 9.7 luw fp(?)
    there are some new columns in the catalog : last_used
    they can give an indication if an index has never been used
    missing indexes : monitor long running queries - doing tablescan....collect workload - explain
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2011
    Posts
    54
    Can you please give reference of related catalogs.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all info is over here
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center
    look at sysibm syscat....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Aug 2011
    Posts
    46
    db2pd -d <dbname> -tcbstats all , will give index usage statistics since database activation, you may want to consider, apart from lastused column from syscat.indexes .

    for required indexes try db2advis

Tags for this Thread

Posting Permissions

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