Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Where are IDX scan couters in DB2 ??

    Hello Friends,

    To analyze which indexes are being used, I need to check how many index scans were run on a given index. That means how many times an index had been used in past 24 hours.

    Can someone help me find data for an index and maybe if possible about a table too.

    Regards

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS?

    Andy

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    DB2v9.5.5 on Win2K3

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Alright,

    So db2pd can be run periodically.

    Thanks for help

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    As much as your heart desires but I would only run it before deactivating db/cycling the instance. which ever comes first. That is assuming you do it on a regular intervals for house cleaning. If not, you are going to see data there from the last time one of those events took place.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Well,
    I am writing a script for daily checks. At the begining of each day. I guess I should keep a record for about 7 days or so; to review daily ; for a look on the trend.

    Regards

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You are going to be wasting your and others time. This is not something you check on a daily or even weekly basis. I would venture to say that this is something that you want to check on a quarterly basis unless you are creating new indexes and implementing new projects on a daily basis.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Agree,

    I just had a discussion; we are not going to set up a daily job for this. May be once a while , or when my manager wants to have a look on details.

    Regards for good suggestion

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I have decided to take another look at it and possibly finally write something up that will take a manual work out of the picture, if it all possible.

    I would be curious to compare the notes and see how you are going about it and maybe put a collective together to get this done.

    What I have found out thus far is for some unknown reason IBM did not extended fields for tbname, schema on the output and if you are on a 9 what ever your tables most likely been extended past 18 char point.

    This makes it a bit tricky to get a correct info out. In the index section it only gives you a chopped tbname and an IID of the index. So, to identify the correct index you have to cross match hex value from an index section to a hex value on the table section to get a correct tableID. After that match it with a correct tbname and only then go get a correct indname. They sure do not make it easy on you.

    I think I will pipe the output to a file. From there I would slice and dice that file into a two sections: table and index files. From there load that data into tables and then write SQL to get what I really need.

    What do you think?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    I usually do the same trick, output to textfile and process it for presentation. I will appreciate when you will do that.

    My surprise is that why IBM has not provided a view like may other useful views. Is there any place where we can suggest IBM for future versions.

    I can do a lot easy scripting in windows native .BAT scripts and also VBScript system scripts.

    Regards

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    hmm, I guess I can go back to sleep ya'll good night

    Thanks Andy
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Cougar8000 View Post
    good night
    sleep tight

Posting Permissions

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