Question Which Db Parameters To Change On New More Powerful Server?

Rob Fitzpatrick

ProgressTalk.com Sponsor
Regarding the buffer pool, when your DB is bigger than -B it's also a good idea to add "-Bp 10" to your probkup online command, assuming you use probkup to back up your DBs. This causes the backup client to use 10 private read-only buffers for its block reads, and leaves the rest of the buffer pool untouched. It prevents your post-backup users from having a lot of useless cached data and doing a lot of in-paging to fetch the data they need.
 

Vito

New Member
Thank you, Rob! That part about PROBKUP -Bp I've read last night and already modified my nightly backup scripts
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Using Alternate Buffer Pool (-B2) isn't entirely straightforward. It requires knowledge of how to use the feature effectively, and it requires analysis and testing. In particular, you need to know the CRUD stats of your objects. As CJ pointed out, you haven't set -tablerangesize or -indexrangesize appropriately so you don't have that information.

Try this:
Code:
find last _file no-lock where _file._tbl-type = "T" use-index _file-number.
find last _index no-lock where not _index._index-name begins "_" use-index _index-number.
display _file-number _idx-num.
You will need to set -tablerangesize and -indexrangesize to appropriate values and then collect table and index stats for some meaningful period of time to understand the application's I/O profile. If you have small, relatively static tables or indexes that are heavily read then they are good candidates for the ABP. It is a good idea to also assign the Schema Area to the ABP. Once you decide which objects and areas you want to assign to the ABP, you will need to size -B2 appropriately.

In 11.7, you can get object and area sizes, in blocks, from the proutil -C viewB2 command. Depending on the rate of growth of assigned objects and areas, and the frequency with which you restart the DB, you may want to size -B2 larger than necessary. The important point is that you always want to have empty buffers in -B2 and have the LRU2 replacement policy disabled. The status of the latter can be seen in promon R&D 2|3.
 
Top