Creation Of Bufferpools

Buffer Pools:

•             A buffer pool is an area of memory allocated to the database manager to cache table and index data pages as they are read from disk or modified.
•             The database manager decide the when to bring data from disk into a bufferpool and when old data in a buffer pool is unlikely to be used in the short term and can be written back out to disk.
•             Data can be accessed much faster from memory than from disk.
•             The purpose of the buffer pool is to improve database system performance. For better performance multiple buffer pools can be created.
•             The configuration of buffer pools is a very important tuning area, Since you can reduce the delay caused by excessive physical I/O.

 

Note:  the creation of DB2 database will create a default buffer pool called       IBMDEFAULTBP 

Creating Buffer Pools:

 Syntax

CREATE BUFFERPOOL <BUFFERPOOL NAME>

SIZE <no of pages>

PAGESIZE <each page size>

 

Example:

                        CREATE BUFFERPOOL BPNAME4K -- Buffer pool name

                        SIZE 2000                                           -- Number of pages

                        PAGESIZE 4096                                  -- page size in bytes (1024*4=4096, 

                                                                                        BPSIZE=(2000*4)/1024=7.8 MB)

 

                        The above statement creates a buffer pool of 2000 four-kilobyte pages (8 MB)

 

                        CREATE BUFFERPOOL BPNAME8K -- Buffer pool name

                        SIZE 1000                                           -- Number of pages

                        PAGESIZE 8192                                  -- page size in bytes (1024*8=8192, 

                                                                                       BPSIZE= (1000*8)/1024=7.8 MB)

 

                        The above statement creates a buffer pool of 1000 eight-kilobyte pages (8 MB)

 

Alter Buffer Pools (Size):

 

            Synatax:          ALTER BUFFERPOOL  <BP_Name> IMMEDIATE SIZE  <No_of_Pages>

            Example:         ALTER BUFFERPOOL BPCERT4K IMMEDIATE SIZE 3000

 

Drop Buffer Pools:

            Syntax:            DROP BUFFERPOOL <BP_Name>

            Example:         DROP BUFFERPOOL BPNAME4K

 

TO CHANGE/ASSOCIATE A NEW BP TO TABLESPACE

 

            Syntax:                        ALTER TABLESPACE <TablespaceName> BUFFERPOOL <BPName> 

            Example:         ALTER TABLESPACE USERSPACE3 BUFFERPOOL BPNAME4K

 

No. of DB2 Commands applying on Bufferpools:    

                       

]$ db2 describe table syscat.bufferpools"

]$ db2 "select * from syscat.bufferpools"

 

BPNAME       BUFFERPOOLID   DBPGNAME   NPAGES   PAGESIZE   ESTORE  NUMBLOCKPAGES 

BLOCKSIZE   NGNAME

--------------------------------------------------------------------------------------------------------- -----------------   --------------

IBMDEFAULTBP    1                        -            199020       4096          N              0           0                -

BPCERT4K     2                   -               2000        4096          N              0           0                -

BPCERT8K     3                   -               1000        8192          N              0           0                -  

 

$ db2 get snapshot for all bufferpools

             Bufferpool Snapshot

Bufferpool name                         = IBMDEFAULTBP

Database name                          = SAMPLE

Database path                            = /home/itlokam/itlokam/NODE0000/SQL00001/

Input database alias                   =

Snapshot timestamp                   = 08/17/2006 18:51:26.962157

'

'

'

Node number                             = 0

Tablespaces using bufferpool       = 7

Alter bufferpool information:

Pages left to remove                   = 0

Current size                               = 199020

Post-alter size                            = 199020

 

Page cleaners:

            As pages are read and modified, they accumulate in the buffer pool.

            NUM_IOCLEANERS   ---------> In get db cfg.

            CHNGPGS_THRESH =60%    ---------> In get db cfg.