We have a production database hosted on a server with 110 GB memory (real). The bufferpools are gigantic at 40GB and 30GB. There are a couple of development systems in the production landscape with considerably less memory than the production box.
It is required to refresh (redirected-restore) the dev boxes from production, once in a couple of weeks.
Since the bufferpools are a part of database configuration, db2 tries to create production sized bufferpools in the poor dev boxes during the ‘rollforward stop' operation. This hoses up the dev boxes, as a very big chunk of memory for bufferpools gets allocated from the paging space. Often it takes hours for the database to come online on the dev boxes after the rollforward completes.
The solution to avoid this kind of an issue is to use the db2 registry variable DB2_OVERRIDE_BPF.
Here is the sequence of steps:
1. Post restore and before starting the database rollforward operation (assuming you are restoring from an online backup) set the registry variable DB2_OVERRIDE_BPF to a specified number of pages (keep this value low so that it may fit in the memory available on your dev box)
db2set DB2_OVERRIDE_BPF=<no. of pages>
For example:
db2set DB2_OVERRIDE_BPF=50000
Setting the above registry variable will instruct db2 to override the actual bufferpools and instead create bufferpools with the given page size - in this case 50000.
Note: If you are restoring from an offline backup, set the registry variable either before the restore or just after the restore finishes, but before you issue a connect/activate to the database.
2. Recycle the database manager
db2stop; db2start
3. Continue with the rollforward operation or the connect/activate database operation.
4. Reset bufferpool sizes to the optimum value for the dev. Box
ex: alter bufferpool BPF1 size 100000;
5. Reset the DB2_OVERRIDE_BPF registry variable
db2set DB2_OVERRIDE_BPF=
6. db2stop/db2start
