db2 Incremental backup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jurgen Haan - 27 Sep 2007 10:26 GMT

Hi all.

Yesterday we decided to switch from full online nightly backups to the
following schema:
sun: full online
mon-sat: incremental online.

Important to note; we don't use deltas.

Well it seems to work.
But one thing amazes me. Normally, our nightly backups are about 65GB of
size... But the first increment since the last full backup (we started
with a full backup on wednesday morning) was 45GB of size.

I'm kinda puzzled by the fact that on a 66GB database the increment is
45GB over a 24hour timespan.

I'm not sure if our applications would generate that much inserts and
alterations, but before I start pulling apart our software, I'd like to
know some extra info about the internal workings of incremental backups.

My question:
Is an incremental backup an overview of differences compared to the last
full backup (so unaware of the transactions, but just a blueprint of the
differences of the last full backup and this backup), or is an
incremental backup a collection of changes since the last backup (so a
restore is just a replay of transactions since the last full backup)?

Btw. Any ideas on why our increment COULD be this size is also appreciated.

Kind regards.

-R-

Reply to this Message

Jurgen Haan - 27 Sep 2007 10:29 GMT

> ... nightly backups are about 65GB of
> size...
>
> ... the fact that on a 66GB database the increment ...

Please ignore minor fluctuations in backups size in my previous story :P
~65-66GB. I have not yet met my daily coffee quota.

-R-

Reply to this Message

Jurgen Haan - 27 Sep 2007 11:27 GMT

Hmm.. in addition to my previous question;

Is all LOB data included in incr. backups, regardless of changed of
LOBs? Because the database DOES have a lot of large objects.

Reply to this Message

vijay.db@gmail.com - 27 Sep 2007 14:44 GMT

> Hmm.. in addition to my previous question;
>
> Is all LOB data included in incr. backups, regardless of changed of
> LOBs? Because the database DOES have a lot of large objects.

Hi,

If any changes happened to the LOB data, then it will also be included
in the incremental backup image.

Thanks,
vj_dba

Reply to this Message

Jurgen Haan - 27 Sep 2007 14:50 GMT

> If any changes happened to the LOB data, then it will also be included
> in the incremental backup image.
>
> Thanks,
> vj_dba

Hello. Well.. according to the documentation; any changes done to any
page in the tablespace containing the lOBS, DB2 will include *ALL* LOBS
contained in that tablespace.

I quote from IBM:
"If a table space contains long field or large object data and an
incremental backup is taken, all of the long field or large object data
will be copied into the backup image if any of the pages in that table
space have been modified since the previous backup."

A find on all *.LB* files on our storage states that we have 48GB LOB
data. So I think this kinda explaines our huge increment.

So now we chose to skip the increments, and send over logs instead and
use them in combination with a weekly backup in order to get a daily
'backup' (something from which we can restore to that day).

-R-

Reply to this Message

Sanjuro - 27 Sep 2007 16:13 GMT

> vijay...@gmail.com wrote:
>
[quoted text clipped - 22 lines]
>
> -R-

If you have compromised on the frequency of your backups because of
space constraints, I suggest you look at DB2 v9 compression. Row
compression may potentially reduce your database size by 75% (may be
less in your case of lot of large objects). Or you can just use Backup
compression or a combination of both.

Cheers,
~sanjuro

Reply to this Message