Browse DevX
Sign up for e-mail newsletters from DevX


Tuning DB2: Where Your Data Is and Why it Matters-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Memory for DataBuffer Pools
A buffer pool is a disk cache specifically designed for tables and indices, allowing data requests to be filled from memory rather than disk. Buffer pools use fixed page sizes, which must be the same as the page size of any tablespaces they serve.

What you get by default:
DB2 provides a buffer pool called IBMDFTBP, which is very small (1 MB, or 250 pages, on Windows NT).How to create a new buffer pool:
To create a new buffer pool, use the command CREATE BUFFERPOOL or use the DB2 Control Center.

How to I allocate a tablespace to a bufferpool?
Specify a BUFFERPOOL clause with CREATE TABLESPACE or ALTER TABLESPACE.Why create a new buffer pool? There are four basic reasons. First, the default buffer pool is very small. You can change the size of the default buffer pool but that's quite dangerous. When a database is initialized, DB2 attempts to allocate memory for all buffer pools in that database. If there is insufficient memory to do so, it loads only IBMDFTBP (the default buffer pool) plus one small buffer pool for each other page size in use, which allows you connect to the database anyway. If the default buffer pool is also too large, you will be unable to connect to the database even to correct it.

Second, if you have tablespaces with differing page sizes, you will need to create a buffer pool for each page size. Third, you might have a large table that is frequently scanned completely, which would flush out the buffer pool every time. You could use a separate tablespace and assign it to a small buffer pool. Finally, if you have a smaller table you want to keep entirely in memory at all times for fast and frequent access, you can use a separate tablespace with a dedicated buffer pool.Most often, though, you would start with just one additional buffer pool. A quick rule of thumb for systems dedicated to DB2 is to allocate 75 percent of physical memory to the buffer pools. It will need to be much less when coexisting with other applications.

Five Tips to Remember
I've barely covered the basics of data storage in DB2. But here are my top five recommendations if you're getting started:

  1. Be generous with memory and hard disk space
  2. Use DMS table spaces
  3. Create larger buffer pools
  4. Try the Control Center's Performance Tuning wizard and read up on any parameter it nominates for change
  5. Download and read the DB2 v7 Tuning Redbook (see Related Resources)

Greg Nash is an integration engineer with a manufacturing company in Australia.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date