How to enable Incremental Backup in db2 ? Incremental / Delta Backup Commands and steps

          This tutorial covers how to take incremental / delta backup and also covers advantages over incremental backup. As database sizes are growing larger ( even up to terabyte and petabyte range) , cost of taking full backups every time will be high , in terms of storage for the backup images and time required to take the backups . It is not advisable to back up the entire database every time, when a small percentage of the data changes happen. The solution to the above problem is taking incremental backup , it allows the user to backup only the changes that have been made since the last backup , instead of having to backup the entire table space or entire database . Incremental backup image contains only pages that have been updated since the previous backup was taken also contains all of the initial database meta data (such as database configuration, table space definitions, database history, and so on) similar to full backup images.

DB2 supports two types of incremental backup

1.Incremental :  An incremental backup image contains database data that has changed since the last, successful, full backup operation. This is cumulative backup image, because last incremental backup image will have the contents of the previous incremental backup image plus changes after that.

2.Delta : A delta, or incremental delta, backup image contains database data that has changed since the last successful backup ( it may be full, incremental, or delta) of the table space. This is also known as a differential, or non-cumulative, backup image .

Now let us see how to enable incremental backup and how to take full incremental backup and Delta .

In our example , i have used the database SALES and involved two tables Sales_Master , Sales_TR

To check wheather the incremental backup is enabled or not , give the command db2 get db cfg for salesIf the output contains the below line, then incremental backup is not enabled that means we can not use the incremental backup functionality .

  Track modified pages     (TRACKMOD) = OFF

To enable incremental backup, turn the database configuration parameter TRACKMOD on . Give the following command

db2 connect to sales
db2 update db configuration for sales using TRACKMOD ON

  SQL1363W. One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, all applications must disconnect from this database before the changes become effective. 

If the above message is displayed then issue the following commands

db2 force applications all
db2 terminate

         When TRACKMOD is on , db2 database keeps track of table spaces that have been modified. So when the incremental backup command is issued , the tables spaces that have not been modified since last backup , are skipped.

Incremental backups require a one time full backup to make as a reference point for incremental changes.

db2 connect to sales
db2 backup db sales online to d:\db2bkp

Incremental

Now make some changes in tables.

db2 "update sales_master set price=price*0.20 where cat_code =4"
db2 "insert into sales_tr (item_code, category,quantity, Sales_date) values ('1027', '7', 30, '2011-12-06')
db2 "insert into sales_tr (item_code, category,quantity, Sales_date) values ('1014', '4', 20, '2011-12-06')

Now issue the following to take incremental backup

db2 backup db sales online incremental to d:\db2bkp

The size of the incremental backup may be less than the complete backup size because it contains only changes since last full backup. You can check the backup size by issuing the dos or linux command

dir D:\db2bkp\SALES.0\DB2\NODE0000\CATN0000\20111206 or ls -ltr /home/db2inst1/db2bkp

Incremental Delta

Now make some more changes in the tables.

db2 "update sales_master set price=price*0.10 where cat_code =7"
db2 "insert into sales_tr (item_code, category,quantity, Sales_date) values ('1056', '3', 50, '2011-12-07')

To take incremental delta backup , issue

db2 backup db sales online incremental delta to d:\db2bkp

As i already said , delta backup contains all pages changed since the last backup ( delta, an incremental, or a full backup image).

To take only incremental backups at table space level issue

db2 backup db sales tablespace(ts_sales) online incremental delta to d:\db2bkp

Now again make some more changes in the tables and issue

db2 backup db sales tablespace(ts_sales) online incremental to d:\db2bkp

Note the timestamp of above backup. Let us say TS4

The above incremental backup image (TS4) contains all the changes happened since the full backup in the tablespace ts_sales, because it is not a delta backup.