5 DB2 Buffer Pools Best Practices

Here’re the Buffer Pools best practices in DB2. You will get quick response to query when you use Buffer Pools.

Beginner’s Notes

• Buffer pools play prime role because when you execute SQL query, the DB2 will not do 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 the 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.

Best Practices

Buffer pool is not a physical memory. You can say 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:

i. Create Buffer Pool

CREATE BUFFERPOOL randombuffpool IMMEDIATE SIZE 61440 NUMBLOCKPAGES 0 PAGESIZE 8192; 

ii. Alter Buffer Pool

 ALTER BUFFERPOOL bufferpoolname SIZE 81920; 

iii. Drop Buffer Pool

DROP BUFFERPOOL bufferpoolname;

Related Posts