I am a newbie to DB2 LUW and have joined this group with great enthusiasm to learn advanced concepts.
How to tune Buffer Pool is my first topic to discuss...
Based on table definition we can arrive at page size of a table...
Based on page size of table we can assign a Buffer Pool for it. But how we can calculate an optimum number of pages for buffer pool to start with....
How we can monitor parameters like BP HIT RATIO and tune the various parameters like IOSERVERS and PREFETCHERS...what are all the snap shot we might need and want to monitor for at what frequency....
Can we tune parameters based on data base size or frequently used queries etc...?
Also please let me know if there is any documentation available for such questions....i have gone through the DB2 manuals but of not much help....
You can google about that and you will find a lot of info. He some I got in contact:
2) Use one of the administrative view BP_HITRATIO or go directy to the snaphot table SYSIBMADM.SNAPBP(the table wich BP_HITRATIO is build on) if you want more details or customize your consult.
3) get snapshot for all bufferpools
Observation: remember to turn the Swith Monitor for BP ON.
Thanks for the response..i went through the db2top video and was very useful in explaining HIT RATIOS.
I am looking for more in the area of configuring the number of pages and other related parameters based on database attributes/data size etc...
The following command "db2pd -db dbame -pages" provides the content of buffer pools.
By analysing the output we can know what are the most frequently accessed tables/tablespaces and their number of pages...By collecting this info over a period of time we can isolatethose tables and tablespaces into a separate bufferpool for effective performance...