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

[Patrick Finnegan]

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?

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

[Serge Rielau]

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

[Patrick Finnegan]

On Nov 3, 9:58*pm, Serge Rielau <[e-mail]:

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.

[Serge Rielau]

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