473,883 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

what is bufferpool in db2?

1 New Member
what is bufferpool in db2 Pls give me a detail notes
Oct 7 '09 #1
3 20512
Parnamwulan
16 New Member
Hello chandb,

the bufferpool is how database is defined to handle memory

- you have a table
- this table resides on some tablespace
- the tablespace needs to have container to handle space
- the tablespace needs to have bufferpool to handle memory

- the bufferpool is assigned to tablespace and contains of number of pages and pagesize

If you expect the table will be small and a lot of simple queries will be run on it use the big number of small pages

If you expect the table will be huge or very complex queries will be running on it use big pages and relatively small number

the counting is this:
- the standard pagesize is 32 (can be 8, 16, 32, 64 depends on your system and on tablespace definition)
- the pagesize works as multiplier for the number of the pages when counting final memory to be available for the bufferpool
example: the IBMDEFAULTBP has pagesize 32 and 1000 pages = it can run queries consuming maximum of 32768000 bytes of memory

the command to be used:
db2 create bufferpool <name> size 25000 pagesize 32768
... it will create bufferpool with around 1GB memory for processing statements upon tablespace

other commands which can help you:
db2 "select * from syscat.bufferpo ols" - all bufferpools
db2 list tablespaces show detail - all tablespaces
db2 LIST TABLESPACE CONTAINERS FOR 5 - check all containers assigned for tablespace
db2 "list tablespace containers for 5 show detail"
db2 "SELECT TBSPACE, BUFFERPOOLID FROM SYSCAT.TABLESPA CES" - buffurpool assignment

hope its clear - its kind of complicated db2 stuff
Jan 13 '10 #2
Hi Parnamwulan,
its an excellent explanation given by you with good example and real time scenario.
Oct 27 '10 #3
ashuetrx
1 New Member
@Parnamwulan
Hello Parnamwulan,
Thanks for such a wonderful explanation.Hat s off to the effort taken by you to answer questions & the spirit that you have shown to help others who want to learn.
Mar 13 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2858
by: Jagdip Singh | last post by:
Hi all, Is there any recommended bufferpool size while designing database I mean some equation which governs bufferpool size regards, Jagdip
5
4138
by: Paul Shaw | last post by:
Can anyone explain why an insert might cause multiple logical bufferpool data reads? Here's a situation that has me scratching my head. Table A's data resides in tablespace B Table A's indexes reside in tablespace C Table A has no long data No other data resides in either of these tablespaces.
5
2400
by: Paul | last post by:
Hi, is there any way to know the content of the bufferpool : - space usage not present in snapshot - tables actually cached in and number of pages, same for index ... thx
1
6555
by: Christian Berg | last post by:
Hi, I have got a problem with resizing the bufferpool of a DB2 v.8.2 instance. The DB2 runs on an AIX 5.x platform. Problem is that an "ALTER BUFFERPOOL ..." command is not persistent if the database is stopped with the db2stop and restarted with the db2start command.
20
8141
by: Hemant Shah | last post by:
Folks, I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create? I tried to create a 4GB bufferpool db2 complained that is cannot allocate enogth memory. I have 16GB on this system. # db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096 SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until
3
6246
by: dotyet | last post by:
Hi Everyone, I am in a very strange situation, and am looking for suggestions to tackle it. I have a 10 gig database on 64-bit windows 2003 running platform. The database has about 5 gigs of bufferpool. It's a DB2 UDB 8.2 FP9a database. I am trying to restore an online backup image of this database on a
1
5541
by: Raja Shekar | last post by:
HI Every body , I would like to know whether is it mandatory to give Tablespace page size and Bufferpool page size equal..? i also heard like while creating tablespace if pages size of tablespace and bufferpool , the statement itself will fail ...why i'm asking is if we chose a different page size ( ofcourse less than bufferpool page size ) only it shoud affect the performance of db2 .. Thanks in Advance Raja Shekar
2
3453
by: James | last post by:
CASE 1: The following sentences were copied from the book "Administration Guide Performance". "If more pages have been written to disk, recovery of the database is faster after a system crash because the database manager can rebuild more of the buffer pool from disk instead of having to replay transactions from the database log files." My question is:
3
5030
by: Mark A | last post by:
<dcruncher4@aim.comwrote in message news:fuevgh02ug6@drn.newsguy.com... I would try these: db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON (this will override CHNGPGS_THRESH) db2set DB2_SKIPINSERTED=ON (this will reduce lock contention on inserts) db2 update db cfg for test using NUM_IOCLEANERS 8 db2 update db cfg for test using LOGBUFSZ 256 db2 update db cfg for test using DBHEAP 2000 (unless it already at least
3
7938
by: dunleav1 | last post by:
In 9.1 and 9.5 (Linux 64 bit) when a buffer pool is set to self- tuning, how are blocks configured in respect to blocked vs non-blocked when self-tuning is set to on? (ie) I have one bufferpool that is 16k that the IBMDEFAULTBP is shared between all tablespaces. The data tablespace has prefetch automatic set to on.
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11121
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9564
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5793
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.