469,328 Members | 1,301 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

what is bufferpool in db2?

1
what is bufferpool in db2 Pls give me a detail notes
Oct 7 '09 #1
3 20325
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.bufferpools" - 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.TABLESPACES" - 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
@Parnamwulan
Hello Parnamwulan,
Thanks for such a wonderful explanation.Hats 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

Post your reply

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

Similar topics

1 post views Thread by Jagdip Singh | last post: by
5 posts views Thread by Paul Shaw | last post: by
5 posts views Thread by Paul | last post: by
1 post views Thread by Christian Berg | last post: by
20 posts views Thread by Hemant Shah | last post: by
1 post views Thread by Raja Shekar | last post: by
2 posts views Thread by James | last post: by
3 posts views Thread by Mark A | last post: by
3 posts views Thread by dunleav1 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.