In DB2, Buffer pools play a critical role. Why because, when you issue SQL query, the DB2 will not carry physical search in the Database.
Instead, it will do a logical search in the memory of Buffer Pools. The aim of this post is to explain real use of buffer pools in DB2.
When a row of data in a table is first accessed, the database manager places the page that contains that data into a buffer pool.
Pages stay in the buffer pool until the database is shut down or until the space occupied by the page is required by another page.
How Buffer Pool Works in DB2 a Beginners Guide
Physical Data ==> Buffer Pools <== SQL Query Engine
So, the first understanding is buffer pool is not a physical memory. You can call it as logical memory.
1.What happens when SQL Query Triggers…
When you executes a SQL query, DB2 first approaches Optimizer. The optimizer has Control to access index and then actual table, which is present in the physical memory of Database.
The Optimizer, first do read search in the Buffer Pool memory. So, you can save lot of I/O CPU time.
2. Interview Question on Buffer Pools
In the interviews, you can say as if we design Buffer Pools properly, we can increase the performance of Database.
3. When DB2 go for Physical Read
In DB2, after read search is over, unable to find any data in Buffer Pools, then Optimizer prefers to read data using Physical Search.
4. Caution while using Buffer Pools
The Buffer pools have certain memory constraints. If you miss index in SQL query, then DB2 has to do lot of Read Search (Buffer Pools) and Physical search. This consumes lot of CPU resources.
5. How to manage Buffer Pools
Before you go details, take a look on 8 Key Points on Db2 Buffer Pools. You can do three different things on DB2 buffer pools:
- Create Buffer Pool
- Alter Buffer Pool
- Drop Buffer Pool
Create Buffer Pool
CREATE BUFFERPOOL randombuffpool IMMEDIATE SIZE 61440 NUMBLOCKPAGES 0 PAGESIZE 8192;
Alter Buffer Pool
ALTER BUFFERPOOL bufferpoolname SIZE 81920;
Drop Buffer Pool
DROP BUFFERPOOL bufferpoolname;