Hi,

Our production server has 32GB of memory and backup server currently has 32 GB. The bufferpools on production were created keeping the 32GB in mind and every night we have the images restored on the backup server.

Basically, we want to reduce the memory on the backup to 8GB. Currently, every night the database on the backup server is dropped and recreated and as expected, it gets the same bufferpool configuration as that of production.

Is there a way to set up a different bufferpool configuration on backup ( keeping in mind memory = 8GB) and retain it ( ie not let it be overwritten by the images from production)?

Thanks!!

Each time you restore the production database to the backup, the bufferpools will have the production configuration. There is nothing you can do about this during the restore. What you do is in your script that restores to the backup server, after the rollforward process, alter the bufferpools to the sizes you want on the backup system first thing, then disconnect from the database and make sure it goes inactive. This will give you the bufferpool configuration you want.

Andy

thanks, I was thinking the same thing..we have something like this in our backup script --

db2 "force applications all"

db2 terminate
db2stop
db2start

db2 restore db dbname incremental automatic from /DBDATA/backup/ taken at `ls -trd /DBDATA/backup/* | tail -1 | xargs basename | cut -d '.' -f 6` logtarget /home/db2inst1/logs/ REPLACE EXISTING || {
echo "inc restore failed";
exit 1
}

db2 rollforward db dbname to end of logs and stop overflow log path \(/home/db2inst1/logs\) || {
echo "inc rollforward failed";
exit 1
}


------ should i add

connect to dbname

alter bufferpool command

disconnect from dbname

----



that should do it.. correct? thanks!

Yes, that should do it. I should note that since you production bufferpools are too big to fit in the backup server memory, the rollforward will still work using the hidden bufferpools. You will also see warnings when resizing the bufferpools stating that the bufferpool is not active (because it was too big). This is what you would expect. After the disconnect following the bufferpool resize, the bufferpools will then be able to be created on the next connection or activation.

Andy

thanks, usually I give the alter bufferpool command with the IMMEDIATE keyword so the changes are made immediately... for example :

db2 "ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE 64000"

Won't the bufferpool become active as soon as I run a command with this keyword? Or is it only when connecting next time around?

Take a look at DB2_OVERRIDE_BPF registry variable. You can set on the target system.

Thanks Bella, I also found this article from 2009 --
SQL1218N There are no pages currently available in bufferpool "". SQLSTATE=57011

So if I understand correctly, even though the rollforward currently works on our backup server.. it may not work in future restores using the values of the hidden bufferpools so it's better to use the DB2_OVERRIDE_BPF variable?

Should the variable be set before the database image is restored and then change bufferpools / unset variable after the rollforward? Something like this --


db2 "force applications all"

db2 terminate
db2stop
db2start


[[ SET DB2_OVERRIDE_BPF variable ]]

db2 restore db dbname incremental automatic from /DBDATA/backup/ taken at `ls -trd /DBDATA/backup/* | tail -1 | xargs basename | cut -d '.' -f 6` logtarget /home/db2inst1/logs/ REPLACE EXISTING || {
echo "inc restore failed";
exit 1
}

db2 rollforward db dbname to end of logs and stop overflow log path \(/home/db2inst1/logs\) || {
echo "inc rollforward failed";
exit 1
}

[[ ALTER BUFFERPOOL COMMANDS -- SPECIFIC TO BACKUP SERVER ]]

[[ UNSET DB2_OVERRIDE_BPF variable ]]

------------------------------------------------------------------------------------

If we have 8GB of memory, what's an ideal value of DB2_OVERRIDE_BPF? thanks!!

Depends on how many bufferpools you have. It will make each one the same size (as specified in the db2set command). Do the math.

great, thanks.. I figured it out.