Hi,
I am working on DB2 UDB. The space allocated for my database is approx 5 GB. For good tuning of the database, How much % of the total space I shud use for the bufferpool??? I have taken 4 GB (Approx 75 %) as the buffer pool size but i guess this is quite high...Pls suggest me proper bufferpool size....

I have created buffer pool as below:

CREATE BUFFERPOOL XYZ
SIZE 125000
PAGESIZE 32K
;

If you are using 32 bit, you are limited to 1.75gig total; You can use 1gig,250000 4k pages for buffer pool; not knowing what the application is, it is difficult to recommend. look at AUTOCONFIGURE command; it will make suggestions. with 64 bit you do not have the 2gig limit on db2

The actual amount of memory that DB2 can address depends on the OS. For AIX it is about 1.75, for Solaris it is higher, for HP/UX and Linux it is lower. For Windows, it can be higher, depending on the exact version of Windows one is using.

In this situation, 64-bit instances of DB2 are highly recommended if you have that much memory for a single DB2 instance.

Normally, 50-75% of real memory for bufferpools is about right. Remember that this is the total of all bufferpools for all activated databases in the DB2 instance. A database is activated if it has any connections, or has been explicity activated.

Thnx a lotts for ur replies !!!!

1.75Gb is the theoretical limit in AIX for all database shared memory (i.e. sortheap, bufferpool, locklist etc). In practise, AIX allocates memory in 256Mb chunks. if you are using the table snapshot functions (you are using fenced udfs), this trims off another 256Mb from the theoretical limit. If you use intra-parallelism this trims off another 256Mb. With a suitably large sort heap, the actual amount of physical memory for bufferpools is closer to 1Gb.

Under Linux, it's even smaller, but under Solaris you can apparantly address much more physical memory.

I updated the DB CFG BUFFPAGE parm and I get a msg that BUFFPAGE is effective only if SIZE is -1.
Therefore I did
ALTER BUFFERPOOL ......SIZE -1;

Now before I issue a UPDATE DB CFG BUFFPAGE, I want to what's the most value I can assign.
I am using windows 2000, and I see that it uses a RAM of 512MB
So, just knowing my RAM, how do arrive at my number (max value) for BUFFPAGE ?

thanks.
AK

The BUFFPAGE parameter is obsolete and the DB2 version 8.1 manuals say that you should not use it.

Instead, alter the bufferpool size to the size you need with SQL.

If your server only has 512 MB of memory, at it is only used as a DB2 server, then I would keep the total of all bufferpools for active databases to about 100 MB. That would be about 24,000 4K pages total for all bufferpools.

A database is active if at least one application is connected to it, or if it is explicitly activated.