Preloading data/table in memory?

[Hemant Shah]

Folks,

I am using DB2 UDB 8.1 on RedHat ES 2.1. In order to improve performance
of one of our table, I would like to preload some or all of the data into
memory when I start DB2.

Is there a way to do it?
Thanks.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Serge Rielau]

Hemant Shah wrote:

Folks,

I am using DB2 UDB 8.1 on RedHat ES 2.1. In order to improve performance
of one of our table, I would like to preload some or all of the data into
memory when I start DB2.

Is there a way to do it?
Thanks.

That should be easy enough.
Create a bufferpool big enough for the table.
Create a tablespace fo rtehat bufferpool
place the table in that tablespace.
Provoke a scan after starting up DB2.
E.g. SELECT COUNT(DISTINCT *) FROM T;

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:36*************@individual.net...

That should be easy enough.
Create a bufferpool big enough for the table.
Create a tablespace fo rtehat bufferpool
place the table in that tablespace.
Provoke a scan after starting up DB2.
E.g. SELECT COUNT(DISTINCT *) FROM T;

Cheers
Serge

Don't forget about the index(es) for the table. If the table is very
important, then all of its indexes should be in memory also (unless the
table is so small that the indexes would not be used by DB2.

[Fan Ruo Xin]

The question should be -
Do you want to increase the response time of the whole system ?
or
Do you want ot speed up the response time for one peticular sql stmt?
Both questions might involve to improve performance of one special table.
BTW, db2 will not suggest you always cache one physical table or index. DB2
server's memory control manager has some algorithm to decide the age of each
data page and figure out which one supposed to be out. Most of time, it
would not help if you try to tell db2 server how to ....

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:cu**********@new7.xnet.com...

Folks,

I am using DB2 UDB 8.1 on RedHat ES 2.1. In order to improve performance
of one of our table, I would like to preload some or all of the data into
memory when I start DB2.

Is there a way to do it?
Thanks.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Hemant Shah]

While stranded on information super highway Fan Ruo Xin wrote:

The question should be -
Do you want to increase the response time of the whole system ?
or
Do you want ot speed up the response time for one peticular sql stmt?
Both questions might involve to improve performance of one special table.
BTW, db2 will not suggest you always cache one physical table or index. DB2
server's memory control manager has some algorithm to decide the age of each
data page and figure out which one supposed to be out. Most of time, it
would not help if you try to tell db2 server how to ....

This is a readonly table. It contain acturial (sp?) data. There are certain
transactions that read different subset of the table over and over again to
perform calculations. If the whole table is in memory then there is no I/O
latency like getting data from the disk.

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:cu**********@new7.xnet.com...

Folks,

I am using DB2 UDB 8.1 on RedHat ES 2.1. In order to improve performance
of one of our table, I would like to preload some or all of the data into
memory when I start DB2.

Is there a way to do it?
Thanks.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Hemant Shah]

While stranded on information super highway Serge Rielau wrote:

Hemant Shah wrote:

Folks,

I am using DB2 UDB 8.1 on RedHat ES 2.1. In order to improve performance
of one of our table, I would like to preload some or all of the data into
memory when I start DB2.

Is there a way to do it?
Thanks.
That should be easy enough.
Create a bufferpool big enough for the table.
Create a tablespace fo rtehat bufferpool
place the table in that tablespace.
Provoke a scan after starting up DB2.
E.g. SELECT COUNT(DISTINCT *) FROM T;

Thanks I will try that. I already have seperate bufferpool and table space
for the table.

The table has 354677 rows and each row will not be more then 3100 bytes.

I have defined a bufferpool of 10000 pages, each page is 32k.

"create bufferpool planfilepool size 10000 pagesize 32k"

I will have to increase the size of the bufferpool.

Do I need to create buffer pool with 4K pages because each row will be less
then 4K, so will 32K pagesize work with the table?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Hemant Shah]

While stranded on information super highway Mark A wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:36*************@individual.net...

That should be easy enough.
Create a bufferpool big enough for the table.
Create a tablespace fo rtehat bufferpool
place the table in that tablespace.
Provoke a scan after starting up DB2.
E.g. SELECT COUNT(DISTINCT *) FROM T;

Cheers
Serge
Don't forget about the index(es) for the table. If the table is very
important, then all of its indexes should be in memory also (unless the
table is so small that the indexes would not be used by DB2.

How do I load index in the memory?

There is only one index on the table and it is a unique index.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Knut Stolze]

Hemant Shah wrote:

The table has 354677 rows and each row will not be more then 3100 bytes.

I have defined a bufferpool of 10000 pages, each page is 32k.

"create bufferpool planfilepool size 10000 pagesize 32k"

I will have to increase the size of the bufferpool.

Do I need to create buffer pool with 4K pages because each row will be
less then 4K, so will 32K pagesize work with the table?

The page size of the buffer pool must match with the page size of the
tablespace in which you placed the table in question.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Hemant Shah]

While stranded on information super highway Knut Stolze wrote:

Hemant Shah wrote:

The table has 354677 rows and each row will not be more then 3100 bytes.

I have defined a bufferpool of 10000 pages, each page is 32k.

"create bufferpool planfilepool size 10000 pagesize 32k"

I will have to increase the size of the bufferpool.

Do I need to create buffer pool with 4K pages because each row will be
less then 4K, so will 32K pagesize work with the table?
The page size of the buffer pool must match with the page size of the
tablespace in which you placed the table in question.

Yes, the pagesize of the tablespace and the bufferpool are the same.

So, I assume that the row size of the table does not matter, as long as the
pagesize of the bufferpool and the table space match.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Serge Rielau]

Hemant Shah wrote:

While stranded on information super highway Knut Stolze wrote:

Hemant Shah wrote:

The table has 354677 rows and each row will not be more then 3100 bytes.

I have defined a bufferpool of 10000 pages, each page is 32k.

"create bufferpool planfilepool size 10000 pagesize 32k"

I will have to increase the size of the bufferpool.

Do I need to create buffer pool with 4K pages because each row will be
less then 4K, so will 32K pagesize work with the table?

The page size of the buffer pool must match with the page size of the
tablespace in which you placed the table in question.

Yes, the pagesize of the tablespace and the bufferpool are the same.

So, I assume that the row size of the table does not matter, as long as the
pagesize of the bufferpool and the table space match.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

I would go for 32K, less overhead.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Hemant Shah wrote:

While stranded on information super highway Mark A wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:36*************@individual.net...

That should be easy enough.
Create a bufferpool big enough for the table.
Create a tablespace fo rtehat bufferpool
place the table in that tablespace.
Provoke a scan after starting up DB2.
E.g. SELECT COUNT(DISTINCT *) FROM T;

Cheers
Serge

Don't forget about the index(es) for the table. If the table is very
important, then all of its indexes should be in memory also (unless the
table is so small that the indexes would not be used by DB2.

How do I load index in the memory?

There is only one index on the table and it is a unique index.

Same approach as for the table. As long as the bufferpool holds the
whole beast it's stick (unless your OS memory pages of course).
I woudln't worry somuch about pre-heating it... The first usage will
bring it in.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab