Results 1 to 5 of 5

Thread: Buffer Pool

  1. #1
    Join Date
    May 2003
    Location
    Toronto
    Posts
    29

    Unanswered: Buffer Pool

    1. I updated the Buffer Pool in database configure parameter
    update db cfg for sample using buffpage 5000

    2. Then I went to control center to check the Buffer Pool in SAMPLE, but I found the buffer pool size was still 1000, not the new 5000

    3. I created a new buffer pool for SAMPLE in Control Center
    Creat bufferpool BPNEW size 2000

    4. Then I checked the Buffer Pool parameter, it was still 5000

    Is there any relationship between them?

    Any help would be greatly appreciated!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In previous versions of DB2 (prior to version 8), you could create or alter a buffer pool using a default size using buffpage, and the value of buffpage would be taken as the default. In Version 8, you should explicitly specify the size of your buffer pools, using the SIZE keyword on the ALTER BUFFERPOOL or CREATE BUFFERPOOL SQL statements. The change can be immediate or deferred to restart depending on which parameters are used. Also note that the Control Center caches information and sometimes you need to use the refresh option on the menu.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2003
    Location
    Toronto
    Posts
    29
    Thanks you, Marcus_A!

    Just now I did some tests on DB2 UDB 7.1 & 8.1, and searched documents, I think I should understand buffer pool in this way:

    1. When I create a database, the BUFFPAGE size is the same as IBMDEFAULTBP, and database will use IBMDEFAULTBP for the database

    2. the BUFFPAGE parameter of db is only used when one of defined buffer pool size -1.

    3. When I want to bind the buffer pool to one table which is very large, I should put this table in one specific tablespace and bind the tablespace with specific buffer pool. Could I bind one table directly with one buffer pool?

    4. Once I define 2 or more buffer pool, and startup the database, the operation system will allocation memory to satisfy all the defined buffer pool.

    Do I understand right? Hopefully you guys shed some light.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Buffer pools can only be allocated to tablespaces, not tables. Once a database is activated (either via the activate command or a connection to the database) all bufferpool memory in that database is allocated.

    Bufferpool size and allocation is a very important performance configuration parameter. It is often better to have one large buffer pool instead of multiple smaller ones, especially in an OLTP database. However if you want multiple buffer pools, here is my suggestion:

    BP1 - small and medium size tables and all indexes
    BP2 - very large tables that are frequently accessed via tablespace scan, probably defined as 16K or 32K page size.

    Note that you need a separate bufferpool for each page size (4K, 8K, 16K, or 32K) used in the tablespace.

    The default values for buffer pool size are too small for a production system. On most database servers, the buffer pools should be at least 50% of total physical memory available on the machine (allowing for memory used by other DB2 resources, the OS or other processes).

    I would recommend using the database configuration assistant on the Control Center to come up with a good starting point for bufferpools and other DB2 memory allocations.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Location
    Toronto
    Posts
    29
    Marcus_A , thanks you so much, you made me more clear about buffer pool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •