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.

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.

you can also get some info with db2pd -tcbstats / -tcbstats index

Keep in mind that db2pd only keep info starting from the last restart.

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.

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

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.

Table monitoring is about 1-2% overhead.

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.