473,322 Members | 1,614 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Bufferpool Hit Ratio

Hi All,
I am using DB2 UDB v 8.2 , OS: RHEL . Now, How to get the Bufferpool
Hit Ratio?

Aug 22 '06 #1
5 2461
select distinct BP_NAME,
NPAGES,
PAGESIZE,
decimal((bigint(npages)*bigint(pagesize)/(1024*1024))),

decimal(1-((decimal(pool_data_p_reads)+decimal(pool_index_p_ reads))/

(1+decimal(pool_data_l_reads)+decimal(pool_index_l _reads))),20,5)*100
as "BP Hit Ratio"
from table( snapshot_bp( '<DBNAME>', -1 ))
as snapshot_database inner join syscat.bufferpools bp
on bp.BPNAME = snapshot_database.BP_NAME

Make sure you update "<DBNAME>" with your database's name.


satish mullapudi wrote:
Hi All,
I am using DB2 UDB v 8.2 , OS: RHEL . Now, How to get the Bufferpool
Hit Ratio?
Aug 22 '06 #2
On Tue, 22 Aug 2006 05:09:06 -0700, tsmithnj wrote:
select distinct BP_NAME,
NPAGES,
PAGESIZE,
decimal((bigint(npages)*bigint(pagesize)/(1024*1024))),

decimal(1-((decimal(pool_data_p_reads)+decimal(pool_index_p_ reads))/

(1+decimal(pool_data_l_reads)+decimal(pool_index_l _reads))),20,5)*100
as "BP Hit Ratio"
from table( snapshot_bp( '<DBNAME>', -1 ))
as snapshot_database inner join syscat.bufferpools bp
on bp.BPNAME = snapshot_database.BP_NAME

Make sure you update "<DBNAME>" with your database's name.
[from a db2 newbie] Thank you for the post!
Question. What is column '4'? In my case they are all 0.
nat
Aug 22 '06 #3
It is the hit ratio. You probably need to turn on your monitor
switches.
natG wrote:
On Tue, 22 Aug 2006 05:09:06 -0700, tsmithnj wrote:
select distinct BP_NAME,
NPAGES,
PAGESIZE,
decimal((bigint(npages)*bigint(pagesize)/(1024*1024))),

decimal(1-((decimal(pool_data_p_reads)+decimal(pool_index_p_ reads))/

(1+decimal(pool_data_l_reads)+decimal(pool_index_l _reads))),20,5)*100
as "BP Hit Ratio"
from table( snapshot_bp( '<DBNAME>', -1 ))
as snapshot_database inner join syscat.bufferpools bp
on bp.BPNAME = snapshot_database.BP_NAME

Make sure you update "<DBNAME>" with your database's name.
[from a db2 newbie] Thank you for the post!
Question. What is column '4'? In my case they are all 0.
nat
Aug 23 '06 #4
On Wed, 23 Aug 2006 05:25:37 -0700, tsmithnj wrote:
It is the hit ratio. You probably need to turn on your monitor switches.
No. That's column 5. I have that. (Wadda ya think I was excited about?<g>)
But column 4 in my case is 0.

nat

Aug 23 '06 #5
That's the size of the BP in GB. Change "1024*1024" to "1024" and
you'll have MB


natG wrote:
On Wed, 23 Aug 2006 05:25:37 -0700, tsmithnj wrote:
It is the hit ratio. You probably need to turn on your monitor switches.
No. That's column 5. I have that. (Wadda ya think I was excited about?<g>)
But column 4 in my case is 0.

nat
Aug 23 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Paul | last post by:
Hi, is there any way to know the content of the bufferpool : - space usage not present in snapshot - tables actually cached in and number of pages, same for index ... thx
1
by: Christian Berg | last post by:
Hi, I have got a problem with resizing the bufferpool of a DB2 v.8.2 instance. The DB2 runs on an AIX 5.x platform. Problem is that an "ALTER BUFFERPOOL ..." command is not persistent if...
0
by: DB2 News Group | last post by:
Using the new snapshot function in version 8 I have come across something interesting. When computing the bufferpool hit ratio and tablespace buffer pool hit ratio, both of these show up as very...
5
by: Hemant Shah | last post by:
Folks, I am not sure what I am doing wrong, but We have an transaction that does some serious calculation on small chink of data over and over again. It selects few rows from the table...
20
by: Hemant Shah | last post by:
Folks, I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create? I tried to create a 4GB bufferpool db2 complained that is cannot allocate enogth memory. I have 16GB on this...
3
by: dotyet | last post by:
Hi Everyone, I am in a very strange situation, and am looking for suggestions to tackle it. I have a 10 gig database on 64-bit windows 2003 running platform. The database has about 5 gigs of...
1
by: Raja Shekar | last post by:
HI Every body , I would like to know whether is it mandatory to give Tablespace page size and Bufferpool page size equal..? i also heard like while creating tablespace if pages size of tablespace...
5
by: db2admin | last post by:
hello, admin guide says that pool_data_p_reads - pool_async_data_reads will give me number of physical reads that were performed synchronously (that is, physical data page reads that were...
3
by: dunleav1 | last post by:
In 9.1 and 9.5 (Linux 64 bit) when a buffer pool is set to self- tuning, how are blocks configured in respect to blocked vs non-blocked when self-tuning is set to on? (ie) I have one bufferpool...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.