By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,665 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,665 IT Pros & Developers. It's quick & easy.

DB2 - forcing a table to cache all data in the buffer pool.

P: n/a
Is there a DB2 setting that will force a table to be cached in
the
buffer pool? We have four tables that we want to cache completely
in
the buffer pool to ensure that all the data is read from memory.
The
buffer pool ratio should be close to 100%. It's about 80% at the
moment.

Would a "select all" statement cache everything in the table?
Nov 3 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Ian
Patrick Finnegan wrote:
Is there a DB2 setting that will force a table to be cached in
the
buffer pool? We have four tables that we want to cache completely
in
the buffer pool to ensure that all the data is read from memory.
The
buffer pool ratio should be close to 100%. It's about 80% at the
moment.

Would a "select all" statement cache everything in the table?
There is no setting for pinning a table into the bufferpool.
I fail to see why you would need to do this -- when you get
into the job of trying to outsmart the DBMS, something is
usually wrong.

Regardless, if your bufferpools are large enough, pinning will
effectively happen. You may need to put each table into
a separate tablespace, and assign each tablespace to its own
unique bufferpool.

Nov 3 '08 #2

P: n/a
Ian wrote:
Patrick Finnegan wrote:
>Is there a DB2 setting that will force a table to be cached in
the
buffer pool? We have four tables that we want to cache completely
in
the buffer pool to ensure that all the data is read from memory.
The
buffer pool ratio should be close to 100%. It's about 80% at the
moment.

Would a "select all" statement cache everything in the table?

There is no setting for pinning a table into the bufferpool.
Well, there is. You describe it below.
put each table into
a separate tablespace, and assign each tablespace to its own
unique bufferpool.
Just make sure the BP size >= tablespace size

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Nov 3 '08 #3

P: n/a
On Nov 3, 9:58*pm, Serge Rielau <srie...@ca.ibm.comwrote:
Ian wrote:
Patrick Finnegan wrote:
Is there a DB2 setting that will force a table to be cached in
the
buffer pool? *We have four tables that we want to cache completely
in
the buffer pool to ensure that all the data is read from memory.
The
buffer pool ratio should be close to 100%. *It's about 80% at the
moment.
Would a "select all" *statement cache everything in the table?
There is no setting for pinning a table into the bufferpool.

Well, there is. You describe it below.put each table into
a separate tablespace, and assign each tablespace to its own
unique bufferpool.

Just make sure the BP size >= tablespace size

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks. I will try that.

Nov 4 '08 #4

P: n/a
The discussion so far was about pinning a table in the bufferpool.
What you describe here is a way to pinning buffer pool in memory.
Two different kinds of page faults.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Nov 4 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.