About DB2 backup and lock

[lcklee]

I have a application on AIX and it need to insert data to DB2 frequently. Now I find that the application encounter lock time out during the database performing full database backup. Is there any method to avoid this problem, since my application insert data all the time and it is hard to arrange the database backup to a period that no transaction in the database.

Is there any setting on the database full backup so that I can perform database backup without affect transaction process.

Calvin

 

[gregsimpson]

Calvin,

When you invoke the BACKUP command you should be aware of the following

DB2 must be started(obviously it is in your case)

The database must be in a normal or backup pending state.

When using the backup utility refer to the database name by its alias.

You can run the backup in online mode via the ONLINE parameter or in offline mode which is the default. Online mode allows other applications to remain connected to the database and do active work while the backup is proceeding. Alternatively for an offline backup only the backup job itself may be connected to the database.

For online mode the database must be recoverable, that is archive loggin must be enabled. When running in online mode DB2 attempts to acquire S (share) locks on tables with LOB's. THis might result in failure ruuning BACKUP due to applications connected to the databse holding incompatible locks.

You could also consider the following to improve BACKUP performance

Using the PARALLELISM parameter. This can reduce the the amount of time required to complete the BACKUP. It defines the number of processes or threads that can be started to read the database.

BACKUP buffer size and number of buffers. IF you use multiple buffers and I/O channels you should use twice as many buffers as channels to ensure that the channels do not have to wait for data. Choose a buffer size which is a multiple of the largest table space extent size. IF you dont' specify the default value is taken from the DBM, which is initially set ot 1024(4 KB) pages.

An example including the above would be along the lines of

BACKUP DATABASE DB2TSTDB TO /dev/rmt0
WITH 2 BUFFERS
BUFFER 512
PARALLELISM 2
ONLINE

Let us know how you get on.

Cheers
Greg