DB2 v9.5.1 ESE AIX v6.1

I’d like to use SYSIBMADM.BP_HITRATIO admin. view to tune the bufferpools, but could not find anywhere if data in it is cumulative or instant, or for some time period.
Does it require to turn on the bufferpool snapshot switch? I know I need to do it for db2pd –bufferpools
How often information is getting updated in the view?
What would be your suggestion - if I am running a performance test which take 6 hrs, how often does it make sense to select from this view to get info?

Thanks in advance

Hit ratios are cumulative by definition. Information is collected from the moment the database starts, provided that the DFT_MON_BUFPOOL parameter is set.

You may remember that a view (any view) does not store information. The monitoring views call the corresponding table functions behind the scenes, which in turn retrieve snapshot monitor element values at that time.

- OR from the moment when dft_mon_bufpool set to ON if db is already active? Pls correct me if I am wrong

- which means that data is collected at the time of SELECT from admin. view? Again pls correct me if I am wrong

restarted the database and selected data with bp monitor off: Buffer pool (DFT_MON_BUFPOOL) = OFF
and it is still showing data:

BP_NAME TOTAL_LOGICAL_READS TOTAL_PHYSICAL_READS TOTAL_HIT_RATIO_PERCENT DATA_HIT_RATIO_PERCENT INDEX_HIT_RATIO_PERCENT XDA_HIT_RATIO_PERCENT
-------------- -------------------- -------------------- ----------------------- ---------------------- ----------------------- ---------------------
BP_16K_1 150 348 -132.00 -357.14 2.12 -
BP_SYSCATSPC 545 274 49.72 73.21 39.25 -
BP_TBS_PROD 0 0 - - -

why am I still getting numbers? and why are they negative? and what are we supposed to do based on this "intel"?

Thanks in advance

I believe so.

Yes, that is what I said.

and why even with all monitors OFF, db2pd -db <db_name> -bufferpools' showing data as well:

Bufferpool Statistics for all bufferpools (when BUFFERPOOL monitor switch is ON):

BPID DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio
1 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
2 336 90 73.21% 0 0 00.00% 752 458 39.10% 0 0 00.00%
3 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4 58 257 -343.10% 0 0 00.00% 188 184 02.13% 0 0 00.00%
4096 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4097 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4098 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4099 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%

Let me share some of my experiences with the snapshot administrative views.
We can use the snapshot administrative views to capture the current state of the database. In particular about the SYSIBMADM.BP_HITRATIO
view we get the information about the bufferpool data hit ratio,bufferpool index hit ratio and bufferpool hit ratio.
Regarding your next question about db2pd -bufferpools yes that is another best option to see the bufferpool hit ratio. And this SYSIBMADM view is based on snapshot timestamp and it does require monitor swtiches on. Let me make it clear I am not talking about the dbm cfg parameters DFT_MON_........ You can also use this view for your session by turning on the monitor switches for your session only.
db2 get monitor switches and then turn them on . Once you disconnect from the database it goes back to its original state.
In this way you can avoid overhead and in your case if you are running a performance test for 6 hours you can automate with a script. I dont know shell scripting so I am just giving you a small example.


USAGE : BP_HITRATIO.ksh <Database name>
db2 connect to $1;
db2 update monitor switches using BUFFERPOOL ON SORT ON TABLE ON UOW ON LOCK ON TIMESTAMP ON STATEMENT ON;
db2 "SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME,
TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT,
INDEX_HIT_RATIO_PERCENT, XDA_HIT_RATIO_PERCENT, DBPARTITIONNUM
FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM" ;
db2 terminate;


And the output looks something like this
DB_NAME BP_NAME TOTAL_HIT_RATIO_PERCENT DATA_HIT_RATIO_PERCENT INDEX_HIT_RATIO_PERCENT XDA_HIT_RATIO_PERCENT DBPARTITI
ONNUM
-------- -------------- ----------------------- ---------------------- ----------------------- --------------------- ---------
-----
ODD003T IBMDEFAULTBP 63.52 73.07 50.73 -
0
ODD003T BP6 0.00 0.00 - -
0
ODD003T BP7 0.00 0.00 - -
0
ODD003T BP8 0.00 0.00 - -
0
ODD003T BP9 0.00 0.00 - -
0
ODD003T IBMSYSTEMBP4K - - - -
0
ODD003T IBMSYSTEMBP8K - - - -
0
ODD003T IBMSYSTEMBP16K - - - -
0
ODD003T IBMSYSTEMBP32K - - - -
0

9 record(s) selected.

Then if you want you can further export this to a excel file and make you script better.

These views are really helpful to anlayze the bufferpool hit ratio easily rather than going through the whole bufferpool snapshot.

Hope this would have given an insight ... LET ME KNOW IF YOU GET MORE INFORMATION ABOUT THIS I AM JUST A BEGINNER TOO I AM READY TO LEARN !!!!!!!!!!!!!

Thanks

Surgeon

so I run this query to get bp_hitratio:
db2 "select substr(BP_NAME,1,14) bp_name ,TOTAL_LOGICAL_READS TOT_LOG_READS ,TOTAL_PHYSICAL_READS tot_phys_reads ,TOTAL_HIT_RATIO_PERCENT tothitratio ,DATA_HIT_RATIO_PERCENT datahitratio ,INDEX_HIT_RATIO_PERCENT idxhitratio ,substr(to_char(SNAPSHOT_TIMESTAMP,’YYYY-MM-DD-HH24:MMS’),1,19) snapshot_ts from SYSIBMADM.BP_HITRATIO"

and it works well from command line (as long as I am connected to the database). However when I run it in the script (every 60 secs) data does not get updated:
BP_NAME TOT_LOG_READS TOT_PHYS_READS TOTHITRATIO DATAHITRATIO IDXHITRATIO SNAPSHOT_TIMESTAMP
-------------- -------------------- -------------------- ----------- ------------ ----------- -------------------
IBMDEFAULTBP 426 0 100.00 100.00 - 2010-01-08-15:01:45
BP_SYSCATSPC 0 0 - - - 2010-01-08-15:01:45
BP_TBS_PROD 0 0 - - - 2010-01-08-15:01:45
BP_TBS16K 0 0 - - - 2010-01-08-15:01:45
IBMSYSTEMBP4K 0 0 - - - 2010-01-08-15:01:45
IBMSYSTEMBP8K 0 0 - - - 2010-01-08-15:01:45
IBMSYSTEMBP16K 0 0 - - - 2010-01-08-15:01:45
IBMSYSTEMBP32K 0 0 - - - 2010-01-08-15:01:45

Timestamp remains the same. What and where is the problem?

Can you post the script you are trying to run.... so that I can help you from my side....

Thanks

Surgeon

how do i post a large script here?

Can you attach the script.

When replying click Go Advance and then Manage Attachments

DBFinder

found an error:
i used
substr(to_char(SNAPSHOT_TIMESTAMP,'YYYY-MM-DD-HH24:MMS') instead
substr(to_char(SNAPSHOT_TIMESTAMP,'YYYY-MM-DD-HH24:MI:SS')
so it was showing month instead of minuted.