tracking io

rajaraja_cholan · #1 (**permalink**) 07-19-11, 10:13

I want to keep track of io statistics from DB2 - what I mean is that I don't want to track io from OS point of view (don't want to use iostat, lparstat, topas, etc.). Does DB2 have any snapshot/table function/admin view for io metrics?

the reason I don't want OS level tracking is that it shows values for the system as a whole, while I am interested only from DB2 point of view.

Please provide your inputs, appreciate it.

singhipst · #2 (**permalink**) 07-20-11, 01:17

I used below query to find IO. Might be helpful for you

Code:

select 
to_char(current timestamp,'HH:MI:SS') as time,
sum(DIRECT_READ_REQS) as DIRECT_READ_REQS, 
sum(DIRECT_WRITE_REQS) as DIRECT_WRITE_REQS,
sum(pool_data_l_reads + pool_temp_data_l_reads + pool_index_l_reads + pool_temp_index_l_reads + pool_xda_l_reads + pool_temp_xda_l_reads) as logical_reads,
sum(pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads) as physical_reads,
sum((pool_data_writes + pool_index_writes + pool_xda_writes)) as TOTAL_WRITES
from table(SYSPROC.MON_GET_BUFFERPOOL('',-1));

shubin_du · #3 (**permalink**) 07-20-11, 06:46

it's not clear if you need the i/o stats only at the db level or at the session level or at the sql level. you can get the i/o information from many views, for example

select * from sysibmadm.snapdb
select * from sysibmadm.snapappl
select * from sysibmadm.snapdyn_sql
select * from sysibmadm.bp_read_io
select * from sysibmadm.snapstmt
select * from sysibmadm.snaptbsp
select * from sysibmadm.snapbp

the full description can be found in documentation "Administrative Routines and Views"

Regards
Sergey

rajaraja_cholan · #4 (**permalink**) 07-21-11, 09:29

Thanks for the reply singhipst & shubin_du, I will look into monitor proc and snapshot views

Internet Services

Web Development

Digital Marketing

Consumer Tech