Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Posts
    77

    Question Unanswered: Time required for DB2 backup process

    I'm looking for an estimate of time taken by DB2 for Backing up database of various sizes. I'm using DB2V7.2 on Windows 2000.

    When I took the backup last time, the size of database image was about 25 GB and it took about 45 minutes to complete .

    Basically, I want to find out whether the graph between the time taken in backup and the size of database is linear.
    I mean if the size of database will become 150 GB, the backup time is going to increase 6 times, or more than that.
    Also, is there any limitation on the size of database for taking a DB2 backup .

    It'll be a great help to me if you can tell me (your experience with it or knowledge) about this size-time relationship in a backup.

    Thanks a lot,
    -Preeti

  2. #2
    Join Date
    May 2003
    Posts
    369

    our backups

    We have a 300Gb, 150Gb and 50Gb databases for our PROD, QA and DEV environments. All three are DB2 EEE 7.2 under AIX 4.3.3. The largest database takes about 2 hours for a full offline backup using the db2 backup utility. We backup to an ESS Shark SAN via Tivoli TSM.

  3. #3
    Join Date
    Jun 2003
    Posts
    77

    Re: our backups

    Thanks for your reply. It'll help a lot.

    Originally posted by mixxalot
    We have a 300Gb, 150Gb and 50Gb databases for our PROD, QA and DEV environments. All three are DB2 EEE 7.2 under AIX 4.3.3. The largest database takes about 2 hours for a full offline backup using the db2 backup utility. We backup to an ESS Shark SAN via Tivoli TSM.

  4. #4
    Join Date
    May 2003
    Posts
    369

    checks

    make sure to monitor the backup you can check it with

    db2 list history backup
    db2adutl

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Time required for DB2 backup process

    I agree that you need to know the time taken for a backup to complete , to plan your other scheduled jobs ...

    At the same time, IMO, we need to remember, the time taken for a backup depends on a number of factors ... The most important ones I can think of at this moment :
    a) The IO capability of the database containers
    b) Your target(ie where the backup goes to) device
    c) CPU usage and database activity(if online backup) on the system at that point of time

    For eg, if the backup writes to a device on the LAN, then if other network activity is heavy, your backup becomes slower ...

    Also, if you are using a higher config machine on prod and you use a medium config one on your dev, then for the same size of the database, the two databases might differ significantly to do a backup ...

    I suggest you take a look at imporving the performace of the backups ... Have a look at the backup command ... For eg, you can increase util_heap_sz cfg parm and/or change the parallelism, buffersz,numbuffers,multiple dest directories in the backup command ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2003
    Posts
    77

    Re: Time required for DB2 backup process

    Thanks for your help. Your suggestions are very useful.

    We are in a process of deciding our back-up policy.
    We are planning to take a daily online backup first to the disk itself, using db2 backup command. And then copy the backup image from disk to tape drive( Sony AIT-3 External Tape Drive). The max size of database is estimated to be 200 GB.

    Some more questions I have are-
    I want to know what is a good way and location to save the Database Logs(to be used for roll forward).
    What if we direct the Logs to some other drive on the same Machine and that drive fills up? Is there any optimum method for doing this?


    Thanks again,
    -Preeti


    Originally posted by sathyaram_s
    I agree that you need to know the time taken for a backup to complete , to plan your other scheduled jobs ...

    At the same time, IMO, we need to remember, the time taken for a backup depends on a number of factors ... The most important ones I can think of at this moment :
    a) The IO capability of the database containers
    b) Your target(ie where the backup goes to) device
    c) CPU usage and database activity(if online backup) on the system at that point of time

    For eg, if the backup writes to a device on the LAN, then if other network activity is heavy, your backup becomes slower ...

    Also, if you are using a higher config machine on prod and you use a medium config one on your dev, then for the same size of the database, the two databases might differ significantly to do a backup ...

    I suggest you take a look at imporving the performace of the backups ... Have a look at the backup command ... For eg, you can increase util_heap_sz cfg parm and/or change the parallelism, buffersz,numbuffers,multiple dest directories in the backup command ...

    HTH

    Sathyaram

  7. #7
    Join Date
    May 2003
    Posts
    369

    logs and backups

    you can setup the userexit program to manage your log archives or what we do is to backup the old logs to tivoli storage manager on the jukebox disk array and delete them off of the server using db2 prune command.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Time required for DB2 backup process

    Preeti ... As you have an online backup it is mandatory to have all logs created after the start of the oldest backup ... ie, if you have 7 days worth of backup, you need to have 7 days worth of logs also ...

    It is not advisable to leave the old database logs in the active log path .... This is because if your log disk fails, you loose all the logs .. The other reason is a problem of disk full which will affect the database transactions ... Hence,the userexit program will be good to have ... A sample C program supplied with db2 (called db2uext2.cdisk - for disk copy in the sqllib/samples directory) can be customized easily to archive the closed log files ... Once archived, the active log file is reused by DB2 .. Have the archive logs on a different physical disk to your active logs (both for performance and preventing data loss) ...

    On a regular basis(usually once a day) you can backup the archive logs to tape and delete them from disk to make room for new archives to come in ...

    For you backup, again, for Windows, it is a good idea to create the backup image on the disk and copy it to the tape .... Again, for optimal performance, ensure your backup disk is not any of your database container disks ... As an alternative, you can backup directly to tape ...

    If you are using a storage management product like tsm, you can backup directly using the product ... The same can be done for log archive also ...

    If your archive log device fills up, the log will not be archived ... DB2 will retry to archive the log again (I think it is 5 mins usually- the message will be written to the db2diag.log file ) ... Your normal database operation will continue , but with the risk of your active log directory disk filling up ...

    For pruning log files, history etc, for Unix, you can use the script downloadable from
    http://www.database-guys.com/downprun.shtml

    Have a look at this script and maybe you can do a windows equivaent :-)

    Good luck


    Cheers

    Sathyaram


    Originally posted by preetim
    Thanks for your help. Your suggestions are very useful.

    We are in a process of deciding our back-up policy.
    We are planning to take a daily online backup first to the disk itself, using db2 backup command. And then copy the backup image from disk to tape drive( Sony AIT-3 External Tape Drive). The max size of database is estimated to be 200 GB.

    Some more questions I have are-
    I want to know what is a good way and location to save the Database Logs(to be used for roll forward).
    What if we direct the Logs to some other drive on the same Machine and that drive fills up? Is there any optimum method for doing this?


    Thanks again,
    -Preeti
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Jun 2003
    Posts
    77

    Re: Time required for DB2 backup process

    Thanks for providing these important tips. I'll try using the userexit for archiving the logs..
    I really appreciate your help.

    -Preeti

    Originally posted by sathyaram_s
    Preeti ... As you have an online backup it is mandatory to have all logs created after the start of the oldest backup ... ie, if you have 7 days worth of backup, you need to have 7 days worth of logs also ...

    It is not advisable to leave the old database logs in the active log path .... This is because if your log disk fails, you loose all the logs .. The other reason is a problem of disk full which will affect the database transactions ... Hence,the userexit program will be good to have ... A sample C program supplied with db2 (called db2uext2.cdisk - for disk copy in the sqllib/samples directory) can be customized easily to archive the closed log files ... Once archived, the active log file is reused by DB2 .. Have the archive logs on a different physical disk to your active logs (both for performance and preventing data loss) ...

    On a regular basis(usually once a day) you can backup the archive logs to tape and delete them from disk to make room for new archives to come in ...

    For you backup, again, for Windows, it is a good idea to create the backup image on the disk and copy it to the tape .... Again, for optimal performance, ensure your backup disk is not any of your database container disks ... As an alternative, you can backup directly to tape ...

    If you are using a storage management product like tsm, you can backup directly using the product ... The same can be done for log archive also ...

    If your archive log device fills up, the log will not be archived ... DB2 will retry to archive the log again (I think it is 5 mins usually- the message will be written to the db2diag.log file ) ... Your normal database operation will continue , but with the risk of your active log directory disk filling up ...

    For pruning log files, history etc, for Unix, you can use the script downloadable from
    http://www.database-guys.com/downprun.shtml

    Have a look at this script and maybe you can do a windows equivaent :-)

    Good luck


    Cheers

    Sathyaram

Posting Permissions

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