DB2 Backup
By Vamshidhar K
Data loss and corruption are unfortunate realities which need
to be handled proactively.
DB2 provides set of commands backup and recovery. The
following section explains some basic details about the DB2 Backup. The two
possible types of backup are:
·
Offline Backup: The
offline backup can only be taken while the database is inactive and not being
used at the time of backup. The database is consistent upon restore without
applying any transaction logs.
·
Online Backup: The
online backup can be taken while the database is in active and is in use. Database
is inconsistent upon restore and log files required to get the database to
consistent state.
To backup a database use DB2
BACKUP DB database_name command. It looks relatively simple. When size of
the database grows i.e. to few hundred GB of data then we need to know various
options available in DB2 BACKUP. In
recent past, I came across such situation and some options that I tried and
found useful are listed below.
a)
Backup a
database redirecting the output to two different directories
DB2 BACKUP DB database_name TO output_directory1, output_directory2
b)
Backup a
database with compression
DB2 BACKUP DB database_name
COMPRESS
c)
Backup a
database with compression and redirecting the output to two different
directories
DB2 BACKUP DB database_name TO output_directory1, output_directory2
COMPRESS
Note:
-
Buffers and
parallelism are used to enhance the performance. If you do not specify, DB2
will select optimal values. Use this keyword with above command if you would
like to specify buffers and parallelism. WITH 2 BUFFERS BUFFER 4096 PARALLELISM 4.
-
Use can also use WITHOUT
PROMPTING option if you do not wish to see
any user intervention during backup
Size of the DB2 Backup file & Time taken for DB2 Backup
are two important parameters one would like to know during DB2 BACKIUP
activity. The following table gives some
statistics collected while performing backup of database of size 77.6 GB. Please note these
statistics may vary based on the performance of the system, data and other environmental
factors that affect the overall backup activity.
|
Sl. No.
|
Database
Size in GB
|
Time in
minutes
|
Backup
is compressed?
|
Backup
File
Size
in GB
|
Parallelism
|
Backup
to multiple destinations?
|
Destination
Different from Source?
|
|
1
|
77.6
|
90
|
Yes
|
27.2
|
1
|
No
|
No
|
|
2
|
77.6
|
52
|
Yes
|
27.2
|
2
|
No
|
No
|
|
3
|
77.6
|
49
|
Yes
|
27.2
|
4
|
No
|
No
|
|
4
|
77.6
|
50
|
Yes
|
14.1,13.1
|
2
|
Yes
|
Partial*
|
|
5
|
77.6
|
19
|
No
|
77.6
|
2
|
No
|
Yes
|
|
6
|
77.6
|
23
|
No
|
40.2,
37.8
|
2
|
Yes
|
Yes
|
* Partial - one directory is on source drive.
Below are my observations from this activity
- DB2 BACKUP without COMPRESS option is quick but creates backup file size almost equivalent to size of the DB.
- DB2 BACKUP with COMPRESS option takes longer duration but creates relatively low size backup file. Overall backup duration can be reduced by adjusting Buffers and parallelism
To check the status of the database
backup activity, issue below command from another db2cmd window.
DB2 LIST UTILITIES SHOW DETAIL
This will list the details about the current backup activity. See the
below example:
|
ID = 2882
Type = BACKUP
Database Name = database_name
Partition
Number = 0
Description = offline db
Start Time = 22-08-2012 11:00:05.522606
State = Executing
Invocation
Type = User
Throttling:
Priority = Unthrottled
Progress
Monitoring:
Estimated Percentage Complete = 27
Total Work = 83709106173 bytes
Completed Work = 22903402547 bytes
Start Time = 22-08-2012 11:00:05.522645
|
