---
title: "DB2 Buffer Pools: A Comprehensive Look"
description: "DB2 Buffer Pools are logical memory, used to speed up SQL queries, enhance database performance and control CPU resources usage."
url: https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/
date: 2023-11-21
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2019/11/pexels-photo-731510.jpeg?fit=1880%2C1253&ssl=1
categories: ["DB2"]
tags: ["Db2 Buffer Pools guide"]
type: post
lang: en
---

# DB2 Buffer Pools: A Comprehensive Look

Here are the DB2 [Buffer Pools](https://srinimf.com/2012/01/24/db2-buffer-pools-and-table-spaces/) best practices. The primary use of these is [to get](https://srinimf.com/2012/12/24/get-ahead-without-the-corporate-ladder/) a quick response to the SQL queries.

Table of contents

1. [DB2 Buffer Pools](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/#db2-buffer-pools)
2. [Best Practices of Buffer Pools](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/#best-practices-of-buffer-pools)
3. [How to Create, Alter, Drop Buffer Pools](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/#how-to-create-alter-drop-buffer-pools)

![DB2 Buffer Pools](https://i0.wp.com/srinimf.com/wp-content/uploads/2019/11/pexels-photo-5233262.jpeg?resize=1880%2C1245&ssl=1)*Photo by Khunkorn Laowisit on Pexels.com*

## DB2 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 of Buffer Pools

**Physical Data** **==>** **Buffer Pools** <== **SQL Query Engine**

Buffer pool is not a physical memory. You can say it as logical memory.

**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.

**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.

**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.**

**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.

## [How to](https://srinimf.com/2015/03/04/jcl-my-job-submitted-but-not-running/) Create, Alter, Drop Buffer Pools

Before you go details, take a look on [8 Key Points on Db2 Buffer Pools](https://srinimf.com/2016/03/01/8-key-points-you-need-to-know-about-db2-buffer-pools/). You can do three different things on DB2 buffer pools:

1. Create Buffer Pool
2. Alter Buffer Pool
3. Drop Buffer Pool

Related[5 Key DB2 Topics to Read Before Your Interview](https://srinimf.com/2018/04/28/5-top-db2-sections-you-need-to-touch/)

**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;
```

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F11%2F21%2F5-db2-buffer-pools-top-ideas%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2019%2F11%2Fpexels-photo-731510.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20Buffer%20Pools%3A%20A%20Comprehensive%20Look)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Buffer%20Pools%3A%20A%20Comprehensive%20Look&body=https%3A%2F%2Fsrinimf.com%2F2023%2F11%2F21%2F5-db2-buffer-pools-top-ideas%2F&share=email)
-

### Like this:

Like Loading…