DB2 Backup and Recovery (BAR) - Part 04 - DB2 Backup with example

Part 00 - Introduction to DB2 Backup and Recovery artical

Part 01 - Introduction to DB2 Transaction Log files

Part 02 - Introduction to DB2 Log Manger

Part 03 - Introduction to DB2 Logging Mechanism

Introduction

• Backup is an important part of the overall backup and recovery strategy. 

• Backup along with archive log file will help to recovery the database to a Point in time or to a point before the failure.
• Depending on the backup type, the DB2 backup image contains all the pages in the database including the used and free pages.
• DB2 backup also stores following information
1. Tablespace details
2. Container details
3. Log control file
4. Recovery history files
5. Database Configuration parameters

Backup file naming convention

• Click here for more details on DB2 backup file naming convention.

• File name of the backup image created on disk is of following formation

DB_alias.Type.Inst_name.NODEnnnn.CATNnnnn.timestamp.Seq_num 

• For example:

    -- Database backup

SAMPLE.0.DB2INST1.NODE0000.CATN0000.20110518165214.001

    -- Tablespace backup

SAMPLE.3.DB2INST1.NODE0000.CATN0000.20110518165214.001

Types of Backup

1) Offline Backup

• Database is inaccessible to the users.
• It is a complete copy of the database. i.e each and every pages in the database are stored in the backup image.
• For example 
    
  

  Sample DB2 Command
  BACKUP DATABASE      <db_name>  TO     <backup location>  COMPRESS

• Only full database backup option is available.

2) Online Backup

• Database is accessible to the users.
• Database must be enabled for archival logging.
• For incremental or delta backup operation, DB CFG parameter TRACKMOD must be enabled follwed by complete full online or offline database backup.
• Following backup options are available in Online backup configuration

a) Full Online database backup

• Backup image stores all the used and free pages in the databases

• All changed and unchanged pages are stored as part of full online database backup.

Sample DB2 Command
BACKUP DATABASE     <db_name>  ONLINE  TO     <backup location>  COMPRESS

b) Incremental database backup

• In order to track only the changed pages in the database, TRACKMOD must be enabled

• Backup image stores only the pages that are changed from the last full database backup

• Unchanged pages are not backed up    

Sample DB2 Command

-- Set the TRACKMOD parameter  UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON;  -- Need a full backup of the database to use the  -- incremental or delata backup after setting the  -- TRACKMOD parameter. This enables TRACKMOD on all tablespaces.  BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS;  -- Example  BACKUP DATABASE     <DB_NAME> ONLINE INCREMENTAL  TO    <backup location> COMPRESS -- Click hear to know when the log files are include in backup INCLUDE LOGS

 

c) Delta database backup

• In order to track only the changed pages in the database, TRACKMOD must be enabled

• Backup image stores only the pages that are changed from the last full or incremental or delta backup

• Unchanged pages are not backed up  

Sample DB2 Command

-- Set the TRACKMOD parameter  UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON;  -- Need a full backup of the database to use the  -- incremental or delata backup after setting the  -- TRACKMOD parameter.This enables TRACKMOD on all tablespaces.  BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS;  -- Example  BACKUP DATABASE     <db_name> ONLINE INCREMENTAL DELTA  TO    <backup location>  COMPRESS  -- Click hear to know when the log files are include in backup INCLUDE LOGS

 

d) Full online Tablespace backup

Sample DB2 Command

-- Set the TRACKMOD parameter UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON;   --Need a full backup of the database to use the incremental or delata backup after setting the TRACKMOD parameter. This enables TRACKMOD on all tablespaces. BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS;   -- Example BACKUP DATABASE   <DB_NAME> TABLESPACE(<tbsp_name>) ONLINE TO     <backup location> COMPRESS -- Click hear to know when the log files are include in backup INCLUDE LOGS

e) Incremental Tablespace backup

Sample DB2 Command

-- Set the TRACKMOD parameter UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON; --Need a full backup of the database to use the  -- incremental or delata backup after setting  -- the TRACKMOD parameter. This enables TRACKMOD on all tablespaces. BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS; -- Example BACKUP DATABASE    <db_name> TABLESPACE(<tbsp_name>) ONLINE INCREMENTAL TO     <backup location> COMPRESS -- Click hear to know when the log files are include in backup INCLUDE LOGS

f) Delta Tablespace backup

Sample DB2 Command

-- Set the TRACKMOD parameter UPDATE DB CFG FOR <DB_NAME> USING TRACKMOD ON; -- Need a full backup of the database to use the  -- incremental or delata backup after setting the  -- TRACKMOD parameter. This enables TRACKMOD on all tablespaces. BACKUP DATABASE <DB_NAME> ONLINE TO . COMPRESS; -- Example BACKUP DATABASE    <db_name> TABLESPACE(<tbsp_name>) ONLINE INCREMENTAL DELTA TO     <backup location> COMPRESS -- Click hear to know when the log files are include in backup   INCLUDE LOGS