Version 9.1 on AIX

Lets assume that I have a 50GB tablespace. There is one table in this tablespace, some data is modified in this table after the full backup.


During incremental database backup, will DB2 read the whole 50GB tablespace to look for changed pages?

Changed pages only.

But 50 GB is not all that large. How long does a full backup take? I would guess less than 20 minutes.

It would probably help to use multiple file locations for the backup path so that DB2 can use more parallelism. See the Command Reference manual for examples. If the different paths on are on separate mount points, that might help the most, but it would probably help even if on the same mount point.

I understand each tablespace has a flag which gets set when there are changed pages, but how does DB2 know which pages have changed and which have not?

DB2 has a spacemap where this information about changed pages is flagged. You have to turn on the TRACKMOD database parm to enable incremental backups and then DB2 will start keeping track of which pages have been modified since the last incremental backup. There is some extra overhead and possible contention of setting TRACKMOD to ON if you have very high insert rates.

We noticed full and incremental backups take almost the same time if there is at least one changed page in each tablespace so it looks DB2 is reading all pages and not just the modified pages.

It might be because you are taking incremental and not delta. With Incremental it read all the changes from the last full. With Delta it only read changes from the last backup regardless what kind it was.

If every tablespace has at least one changed page, then DB2 will scan all pages in the tablespace to look for pages that have been modified. There is a bit/flag per tablespace that lets incremental backup know whether there is a changed page in that tablespace or not, that's the extent of DB2 knowledge. So, if all tablespaces have modified pages, then DB2 has to read every page in the database for incremental backup. In this sense the reading will be no different for non-incremental and incremental backups. So, it may take same amount of time during incremental db2 database backup. However, the backup image can be significantly smaller for incremental backups.