--- title: "DB2 Buffer pools and Its Best Practices" description: "Here are eight primary things on bufferpools. These are useful for practice." url: https://srinimf.com/2016/03/01/8-key-points-you-need-to-know-about-db2-buffer-pools/ date: 2016-03-01 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2016/03/pexels-photo-9075799.jpeg?fit=1880%2C1253&ssl=1 categories: ["DB2 V11"] tags: ["All About DB2 Buffer Pools for Developers", "DB2 Certification Sample Questions on Buffer Pools"] type: post lang: en --- # DB2 Buffer pools and Its Best Practices In DB2, Buffer Pools definitely a way to improve performance. In this post, I have explained eight key points on how buffer pools works. ## **#1 One Buffer Pool for One Table Space** The most important reason to use more than one user table space is to manage buffer utilization. A table space can be associated with only one buffer pool, but one buffer pool can be used for more than one table space. ![Db2 Sample Question-1](https://i0.wp.com/srinimf.com/wp-content/uploads/2016/03/db2-sample-question-1.png?resize=556%2C367&ssl=1) ## **#2 Size of Buffer Pools** The goal of buffer pool tuning is to help DB2 make the best possible use of the memory available for buffers. The overall buffer size has a significant effect on DB2 performance, because a large number of pages can significantly reduce I/O, which is the most time-consuming operation. However, if the total buffer size is too large, and there is not enough storage to allocate them, a minimum system buffer pool for each page size is allocated, and performance is sharply reduced. ## **#3 How to Calculate Buffer Pool Size** To calculate the maximum buffer size, DB2 considers all other storage utilization, the operating system, and any other applications. Related[DB2 Catalog Tables Where Accessing of Remote System Updated](https://srinimf.com/2015/08/13/mainframe-db2-catalog-tables-in-accessing-remote-data/) Once the total available size is determined, this area can be divided into different buffer pools to improve utilization. If t[here are](https://srinimf.com/2014/09/15/db2-joins-performance-tuning/) [table spaces](https://srinimf.com/2012/01/24/db2-buffer-pools-and-table-spaces/) with different page sizes, there must be at least one buffer pool per page size. ![DB2 Sample Question-2](https://i0.wp.com/srinimf.com/wp-content/uploads/2016/03/db2-sample-question-2.png?resize=555%2C479&ssl=1) ## **#4 How many buffer Pools you need – how to decide** Having more than one buffer pool can preserve data in the buffers. For example, you might have a database with many very-frequently used small tables, which would normally be in the buffer in their entirety to be accessible very quickly. You might also have a query that runs against a very large table that uses the same buffer pool and involves reading more pages than the total buffer size. When this query runs, the pages from the small, very frequently used tables are lost, making it necessary to re-read them when they are needed again. ## **#5 How Buffer Pool Work for Small Tables** If the small tables have their own buffer pool, thereby making it necessary for them to have their own table space, their pages cannot be overwritten by the large query. This can lead to better overall system performance, albeit at the price of a small negative effect on the large query. Often tuning is a trade-off between different functions of a system to achieve an overall performance gain. It is essential to prioritize functions and keep total throughput and usage in mind while making adjustments to the performance of a system. ![DB2 Sample Question-3](https://i0.wp.com/srinimf.com/wp-content/uploads/2016/03/db2-sample-question-3.png?resize=574%2C210&ssl=1) ## **#6 When You can Modify Buffer Pools** With DB2 Version 8 and higher, you can change buffer pool sizes without shutting down the database. ## **#7 How to use Alter Buffer Pool** The ALTER BUFFERPOOL statement with the IMMEDIATE option takes effect right away, unless there is not enough reserved space in the database-shared memory to allocate new space. ## **#8 When you need to modify Buffer Pools** This feature can be used to tune database performance according to periodic changes in use, such as switching from daytime interactive use to nighttime batch work. DB2 Version 9.1 and higher enables fully automated size management of a bufferpool. DB2 self-tuning memory manager (STMM) controls this automation process. Related Posts - [DB2 Buffer Pools: A Comprehensive Look](https://srinimf.com/2023/11/21/5-db2-buffer-pools-top-ideas/)DateNovember 21, 2023 - [Buffer pools Vs. Table spaces in DB2](https://srinimf.com/2012/01/24/db2-buffer-pools-and-table-spaces/)DateJanuary 24, 2012 - [DB2 Catalog Tables Where Accessing of Remote System Updated](https://srinimf.com/2015/08/13/mainframe-db2-catalog-tables-in-accessing-remote-data/)DateAugust 13, 2015 ### 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%2F2016%2F03%2F01%2F8-key-points-you-need-to-know-about-db2-buffer-pools%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2016%2F03%2Fpexels-photo-9075799.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20Buffer%20pools%20and%20Its%20Best%20Practices) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2016/03/01/8-key-points-you-need-to-know-about-db2-buffer-pools/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2016/03/01/8-key-points-you-need-to-know-about-db2-buffer-pools/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2016/03/01/8-key-points-you-need-to-know-about-db2-buffer-pools/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2016/03/01/8-key-points-you-need-to-know-about-db2-buffer-pools/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Buffer%20pools%20and%20Its%20Best%20Practices&body=https%3A%2F%2Fsrinimf.com%2F2016%2F03%2F01%2F8-key-points-you-need-to-know-about-db2-buffer-pools%2F&share=email) - ### Like this: Like Loading…