Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Is there way to retain bufferpool size on backup server?

    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!!
    Last edited by db2user24; 06-08-11 at 15:39.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    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!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    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?
    Last edited by db2user24; 06-08-11 at 18:38.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Take a look at DB2_OVERRIDE_BPF registry variable. You can set on the target system.

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by BELLO4KA View Post
    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 --
    http://www.dbforums.com/db2/1643721-...e-57011-a.html

    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!!

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2user24 View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by Marcus_A View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •