Link to home
Start Free TrialLog in
Avatar of Enuda
Enuda

asked on

Why am I running out of bufferpool pages?

Hi all,
A developer was running a view when he received the following error:  [DB2/LINUX]SQL1218N: There are no pages currently available in bufferpool "4096".  
Why would DB2 want to use a buffer pool; named "4096" when all referenced tables have buffer assigned when they were created. Under what situation can a bufferpool run out of pages? I thought DB2 will mediate bufferpool usage between applications, swapping pages in and out of memory as required?  

Here a few facts that might help: At the time of the error, DB2 was archiving log files and there might be other applications running but then, we've been running these for a while now!

Can someone share their experience and what they did to resolve thtis problem?

Thanks
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image


Hi Enuda,

That's something of a nuisance error that simply means that you need to do a little bit of database tuning.  Ironically, the error comes about because the default bufferpool is too small, or because you've configured additional bufferpools that dwarf the default bufferpool.

You can find out which with a single query:

  SELECT * from syscat.bufferpools;

That will tell you if you have user define bufferpools to adjust.  If not (and you probably don't) simply increasing the number of pages in the default bufferpool should suffice.

Check your database configuration for the variable BUFFPAGE.  It's probably set to the installatation default.  Doubling it should do wonders.  :)  

Good Luck,
kent
Avatar of Enuda
Enuda

ASKER

Hi Kdo,
All of what you have said about bumping up default BP pages assumes of course that I have the memory to support the increase, right?   Where can I find the correct memory size that I have at any given time? I will like to always know what my total memory is, what % current applications are using and what I have left of the total?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Enuda

ASKER

As always, excellent input