Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: System freeze during online backup

    DB: DB2 UDB V7 FP9
    OS: MS Windows 2000

    Hi guys:

    Is there any known problem with taking online backup of a database which has all SMS tablespaces? We have one database around 10G and everytime we take backup it uses up all the system resources.

    I have been recently asked to investigate the issue so I haven't seen this behaviour myself but I don't have the go ahead to take the backup and caue diruption to our prod systems. So please suggest any log file to look etc.

    Thanks in advance.

    dollar

  2. #2
    Join Date
    Mar 2003
    Posts
    343
    Don't know about windows, but on aix we have a multi-TB system with all SMS tablespaces which backs up online every weekend.

    Some things which come to mind are :
    a] It would help to know what exactly maxes out - memory? CPU? And what happens - dos th backup fail?
    b] What is the backup statement?
    c] What are you backing up to (disk, vendor, TSM?)
    d] What is the util_heap for this database set to?

  3. #3
    Join Date
    Sep 2002
    Posts
    456
    Thanks for the quick response and here are some of the answers you seeking:

    a. Backup does not fail, but it just takes long and does not allow other applications to do anything, even though it's online.
    b. Backup statement is: db2 backup database xxxx user test using test online to \\xx.xx.xx.xx\backup\
    c. We are backing up to a network drive
    d. Utility heap size is set to 5000 4k pages.

    Other thing is, we have many columns with BLOB datatype, could that affect the backup?

    dollar


    Originally posted by cchattoraj
    Don't know about windows, but on aix we have a multi-TB system with all SMS tablespaces which backs up online every weekend.

    Some things which come to mind are :
    a] It would help to know what exactly maxes out - memory? CPU? And what happens - dos th backup fail?
    b] What is the backup statement?
    c] What are you backing up to (disk, vendor, TSM?)
    d] What is the util_heap for this database set to?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Is the BLOB defined with the LOGGED option?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2002
    Posts
    456
    Yes, all the BLOB columns are defined with LOGGED option. Why does it matter?

    dollar

    Originally posted by Marcus_A
    Is the BLOB defined with the LOGGED option?

  6. #6
    Join Date
    Mar 2003
    Posts
    343
    I do not know the implications of BLOBs on online backups.

    Some things which come to mind are
    a] Try playing with the num buffers and buffersize parms in conjuntion with the util_heap
    b] Since the backup is going to a networked drive I would check on the network and how well it is utilized and tuned for this kind of use.

    What really stumps me is the fact that other applications cannot connect during the online backup.

  7. #7
    Join Date
    Sep 2002
    Posts
    456
    Sorry, if I wasn't clear earlier but other applications can connect but are not able to do anything. I don't have any log or snapshot information to investigate to make matter worse.

    The purpose of my posting was to find out if anybody else out there have witnessed the same problem with V7 FP3 or FP9.

    dollar

    Originally posted by cchattoraj
    I do not know the implications of BLOBs on online backups.

    Some things which come to mind are
    a] Try playing with the num buffers and buffersize parms in conjuntion with the util_heap
    b] Since the backup is going to a networked drive I would check on the network and how well it is utilized and tuned for this kind of use.

    What really stumps me is the fact that other applications cannot connect during the online backup.

  8. #8
    Join Date
    Apr 2003
    Posts
    191
    Hi,

    online backup locks tables with blobs until after backup. This holds for not logged blobs, at least. Don't know if this is different if they are logged.

    Johann

    Originally posted by dollar489
    Yes, all the BLOB columns are defined with LOGGED option. Why does it matter?

    dollar

  9. #9
    Join Date
    Sep 2002
    Posts
    456
    Yes, you are right. I found this piece in DB2 documentation as well:

    DB2BACKUP_ONLINE
    Online allows database access by other applications while the backup operation occurs.

    Note:
    An online backup operation may appear to hang if users are holding locks on SMS LOB data.


    I think we need to somehow figure a way to minimize the disruption caused by the backup or alter the columns with NOT LOGGED option.

    Thanks.

    dollar

    Originally posted by jsander
    Hi,

    online backup locks tables with blobs until after backup. This holds for not logged blobs, at least. Don't know if this is different if they are logged.

    Johann

Posting Permissions

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