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

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?

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

Is the BLOB defined with the LOGGED option?

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

dollar

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.

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

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

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