how to pin/cache a table in the bufferpool.(when db starts)

[Former Member]

Hi ,

Is there any way to pin/cache a table in any bufferpool on db2 udb when db starts?

I have found a reference to assign a bufferpool to a tablespace.

but I don't think it caches a tablespace when db starts but pins a tablespace to the bufferpool when any data acess is occured.

Do we have any option to pin a table in the bufferpool when db starts?

What I know it on Oracle is there is the option "ALTER TABLE ... CACHE".

Many Thanks,

Jonghoon

Edited by: Jonghoon Kim on May 20, 2009 5:11 AM

[malte_schuenemann · Accepted solution]

I do not know what exactly you want. Indeed, you can assign tablespaces to bufferpools, but not a single table.

If your question is that you want to have a table in the bufferpool at DB start, then there is no option from configuration point of view. However, you are free to force it into the bufferpool by running a SELECT onto the whole table. The problem is that data may be displaced by other requests that come in.

As an example, you might configure a dedicated bufferpool for one of more tablespaces with rather static (content not changing) tables you always want to keep in memory. If you configure the bufferpool size properly to acquire a good hit ratio (e.g. 98%), then this bufferpool should almost always hold the data you need (once you got them into the bufferpool after restart).

On the other hand, too many bufferpools make the administration more complicated. STMM might help to find the proper size of the bufferpools automatically. But note that STMM does not free you from the need of tuning knowledge.

In any case, you need to monitor carefully to decide if such a setup makes sense for your environment.

Hope this helps

Malte

[Former Member · Accepted solution]

Thanks for the information.

I just wanted to know about caching a table.No way to perform it as a unit of a table but tablespace.

The reason why I want to get this method is that;

since when a customer turned off the FileCache function of tablespaces on AIX system

some transactions got degraded.

Turning off the FC is recommended via SAP/IBM both sides.

Instead of turning it off, the way to increase buffer pool could help the performance.

and made him understood no way to cache a table.

Many Thanks,

JH

[malte_schuenemann · Accepted solution]

Instead of following this path I would suggest to find out why performance degraded for certain tables / transactions / reports.

As an example, try checking for bad performing SQL statements and investigate them. You might consider DBACOCKPIT -> Performance -> SQL Cache . Other options to track (in case you know a specific action that performs badly) is the use of traces, e.g. SQL Trace (transaction ST05), or DbSl trace (SAP note 31707). However, the last option requires quite some knowledge.

Also, did you check bufferpool hit ratios (overall and per tablespace), read and write times of the database, ...

Your current attempt to attack the issue will not go to the core of the problem.

Malte

[Former Member · Accepted solution]

Hi,

I have also performed SQL traces on the process chain and could easilly find that the process got stuck in "creating index on F*table of BI".

Hit ratio was under 75% (100MBytes) when FC was ON.

And then made him to change its buffer size to 1.6GB with FC off.( Hit ratio is now around 85% but KPI is over 95%.)

OLTP is getting better than eailier( when turning on FC with 100MB buffer).

But after turnning off FC a process chain got little bit degraded than when FC is ON.

When it comes to "process chain",it works as follows;

- delta data extraction

- data transformation

- data load ( drop the index on F table and load delta data and then create the index).

Just wonder whether "turning off FC" affects the activity "create index on F* tables".

Thanks,

Jonghoon

[malte_schuenemann · Accepted solution]

Hit ratio below 85% is still very bad - it should be around 95% (for BW) or higher. I do not expect INDEX creation times to drop when caching is off.

Malte

[Former Member]

Hi,

you can't do this. You can only cache a complete table in the SAP buffer. But it is only practically with a small table. When the first read on this table will performed the complete table will read to the buffer. For large tables it will take too much time.

You can make this configuration in the data Dictionary (transaction SE11) for the table.

Kind Regards

Lars Burde