DB2 Backup

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