hi,

i need advise from db2 experts on bufferpool.

scenario is given below.

i have a (32k) tablespace and bufferpool for the same 600mb and this tablespace contains around 120 gb of datas.

tablespace 32k bufferpool 32k total size of data (tables)
20000 120 gb

How much i can increase the bufferpool size in order to support the 120gb of data and performance?

pls advice


regards

Sanjai

If you have a 64 instance, you can allocate as much real unused memory as your server currently has. Real means physical memory, and not virtual. Unused means no other process on that server is using it (it is free memory). Just increase the bufferpool size (number of pages).

If you have a 32 bit instance, you are limited to about 1 GB - 2GB for bufferpools depending on the operating system.

Thanks Mr. Marcus, For DWH environment, if i alter the buffer pool as block based bufferpool will the performance improve. kindly advice

regards

sanjai

Block based might help if you have a lot of table scans.

Since it is very unlikely that you can fit all the data in bufferpools and keep it there with a large data warehouse, you have to figure out which data should be (such as indexes and highly used small tables) put in a seperate bufferpool that has about the same amount of memory as there is data assigned to that bufferpool. This will enable your bufferpool hit ratio to be high for at least some important objects (indexes and small tables), even if the larger tables will have a lower bufferpool hit ratio.

In order to assign indexes to a seperate bufferpool than the table data, it must be in a DMS tablespace, and you must specify a seperate tablespace for indexes when the table is created.