Buffer Pools in DB2

Buffer Pools in DB2

1. A buffer pool is an memory area in RAM where data is being read, write and stored for processing during transaction.
2. Its the most important component from database performance point of view.( Its like more RAM faster is the PC, similarly larger the bufferpool size better is the performance of Database)
3. After an select query is fired, data from the database(located in the HDD) is loaded into the RAM(Bufferpool). This loading of data doesn't not take place row by row, it takes place page by page.
4. A page is the smallest unit used for memory transfer from HDD to RAM.
5. DB2 supports 4K,8K,16K and 32K size of pages.
6. Bufferpool acts as an container for this pages. As per the database configuration, if database is using only 4K size pages then only one bufferpool is required but if database is configured with data of different size pages then we required bufferpools of those sizes.
7. All kinds of data from database needs to be loaded into the bufferpool for processing except for
1. LONG data
2. Large Objects
8. Size of Page is decided on the basis of size of rows in the database tables,  as a single page can store upto 255 rows at max.
9. By default, a 4K buffer pool called “IBMDEFAULTBP” is created upon creation of a new database.
10. Following are the commands for creating and managing bufferpools.
1. CREATE BUFFERPOOL BP16K SIZE 1000 PAGESIZE16K
2. ALTER BUFFERPOOL BP16K SIZE 2000
3. DROP BUFFERPOOL BP16K