Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2009
    Posts
    5

    Question Unanswered: query to find the most frequently accessed objects (tables,indexes etc)

    Hi,

    I needed a help in writing a query which gives me the count or the number of times an object is accessed,be it a select, update, insert or a delete.
    So that means every time I run a select or any other operation on any table, I should be able to get the count for that table to be incremented.

    I found a way of doing this in MS SQL using Dynamic Management Views (DMV) but I need to know how to I achieve this in DB2.

    Thanks in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you turn on the Table monitor switch (update dbm cfg using DFT_MON_TABLE ON) for the instance you can then get the information you want with a snapshot for tables on db-name. See the Command Reference manual for more informaiton.

    If you have the latest versions of DB2, there are some snapshot views that are also available with the same information.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    you can also get some info with db2pd -tcbstats / -tcbstats index

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Keep in mind that db2pd only keep info starting from the last restart.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Oct 2009
    Posts
    5

    Question

    Hi,

    Thanks for the responses.

    Is there a way to directly put the output of the db2pd commands to a table rather in a text file.

    Currently I am using this command - db2pd -db sample -tcbstats > c:\test.txt

    I want this output in a table. Are there are some commands to do this directly or any other alternate way.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    if you would like to insert snapshot data into table, I suggest to use snapshot monitor, like "insert into ... select snapshot_table..."

    Detailed instructions:
    1. create table:
    CREATE TABLE admin.my_snapshot_table AS (select * from table (SNAPSHOT_TABLE('sample', -1)) as T") DEFINITION ONLY
    Note: replace 'sample' with you database name.

    2. insert snapshot data in table:
    insert into admin.my_snapshot_table select * from table (SNAPSHOT_TABLE('sample', -1)) as T
    Note: replace 'sample' with you database name.

    3. select snapshot data:
    select * from admin.my_snaphsot_table

    4. You need to write batch file where step 2 command is used and then schedule the batch at regular interval.

    If you need more info read: SQL access to DB2 monitoring data

    Regards
    Last edited by grofaty; 10-13-09 at 03:26.

  7. #7
    Join Date
    Oct 2009
    Posts
    5

    Question

    Hi,

    Thanks for the responses.

    Is there a way to achieve this db2pd - as I needed it to be light, because I read it somewhere that enabling monitoring on the table (monitoring in general) is a costly affair.

    Moreover, I went through the snapshot table and I learnt that it gives me the count of the rows affected rather than the number of times the tables are accessed. E.g. If I say, Select * from TempTable (arbitrary table), the count increment is not ONE (which I expect it to be as I want the number of times I have accessed the table) but the number of records (say 20 if the number of records in that table is 20).

    So is there a way to get the total number of times the table is accessed i.e. each select, insert, delete or update should increment the counter only by one and not by the number of rows affected.

    Thanks in advance.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Table monitoring is about 1-2% overhead.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Oct 2009
    Posts
    5
    Hi,

    Thanks for the response.

    I guess enabling monitoring should not be a problem as it is 1-2 % overhead.

    But can anyone answer my other question:

    pasted from previous post -->

    Moreover, I went through the snapshot table and I learnt that it gives me the count of the rows affected rather than the number of times the tables are accessed. E.g. If I say, Select * from TempTable (arbitrary table), the count increment is not ONE (which I expect it to be as I want the number of times I have accessed the table) but the number of records (say 20 if the number of records in that table is 20).

    So is there a way to get the total number of times the table is accessed i.e. each select, insert, delete or update should increment the counter only by one and not by the number of rows affected.


    Thanks in advance.

Posting Permissions

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