db2 v8.2 ESE on Win2003
Physical RAM 3.99GB Free RAM - 1.9GB
Database SIZE = 400GB.
Number of databases = 1

currently we have 4 bufferpools all 4K pages. The deails are:
Bufferpool1DATA size 78275 (306MB)
Bufferpool1INDEX size 176118 (688MB)
Bufferpool2DATA size 7827 (30KB)
Bufferpool2INDEX size 7827 (30KB)

Due to the size of the one table and indexes i am moving to that (only one) table and indexes into new 8K page tablespace. (Table size is 30GB and index size is 65GB). One 8K TS for table and one 8K TS for index.

Now I want to create the 8K page bufferpool. how do I calculate the size?
You mentioned before that only 2GB size BP is allowed in Windows.

Please post some advise.
Thanks.

Why not leave the indexes in the 4K tablespace? You can have table in 8K and the indexes on that table in a 4K tablespace.

For the table, just take some space out of the 4K bufferpool for the new table. How much depends on how important it is that you get a high BP hit ratio on the table compared to other tables.

Thanks Marcus.

The table size is only 28GB. But the Index size is over 60GB. So i have to move the indexes to the 8K page TS.
Is it possible to have 4K tablespace for table and 8K tablespace for INDEXES?

Also is there any way to check the BP hit rate please?

Thanks.

Yes, you can have a 4K table size and 8K index size. I don't know what version of DB2 you are using, but if you deifine a large tablespace, then the maximum size of a 4K page DBS tablespace is 2048 GB.

It is hard to say how much space you should allocate to the bufferpools. It would require knowledge of the application and database design to determine that. But maybe you are over-analyzing it too much.

But if your indexes on a given table are twice the size of the table, there may be (or may be not) a problem with the indexes.

Yes, db2top. I've posted "howto use in MS win" in another forum: doyouDB2.com - db2top in a 100% ms-windows shop - doyouDB2
The 2Gb boundry is there on 32bit platform (you could bypass that with this: IBM - Exploiting Large Memories - Update) Mind you, I am pointing this out, I am NOT advising this. You'd better move to a 64 bit platform. I've installed db2 on win2003 both 64 bit and that runs fine with mega bufferpools. Speaking of which, an upgrade to V9 with STMM assimilates all your issues.

I have created a 4k TS for table and 8k TS for indexes and imported the data on our UAT machine. The application is working fine. however i can not do the volume test to prove the performance.
Will this cause any performance issue when i apply this to LIVE?

The Index size is shows as 62GB on CC (estimate size). is any other way to get the actual index size?

Thanks.

If you do runstats on the table with indexes all, and then check NLEAF in SYSCAT.INDEXES, it will tell you how many pages are being used for that index.

Regarding performance, I don't know how important those indexes are relative to other indexes and tables, so I cannot recommend a bufferpool size size without a lot more information. But generally, you want a higher hit ratio for indexes than tables, assuming most of your queries use index access. Bufferpool tuning is more of art than a science, and many factors have to be balanced when you have more than one bufferpool.