doyouDB2.com - ROLLFORWARD to END OF BACKUP

I always hated DB2 for the way it had structured the rollforward to work post restore from an online backup. Most of the times, while restoring to a target database on a different system, I just wanted to rollforward my database till the end of backup and would have liked a single straightforward command. But until v9.5 arrived, I had to use a roundabout way to accomplish this feat.

Prior to v9.5, to find the minimum recovery time of the database backup, I had to issue a the following command:

$ db2 rollforward db SAMPLE to 2008-07-24 and stop
SQL1275N The stoptime passed to roll-forward must be greater than or equal to
"2008-07-24-09.07.22.000000 UTC", because database "SAMPLE" on node(s) "0"
contains information later than the specified time.

Here 2008-07-24 was an arbitrary timestamp to find the first recoverable point-in-time. I generally used the backup timestamp here. This gave the correct minimum PIT to rollforward the database to. The subsequent command finally performed the rollforward.

$ db2 rollforward db SAMPLE to 2008-07-24-09.07.22.000000 and stop
DB20000I The ROLLFORWARD command completed successfully.

In v9.5, IBM finally made is simpler to rollforward the database by introducing the much needed "END of BACKUP" option with the rollforward command.

Now with v9.5 if you just want to rollforward your database to the minumum PIT just give the following command:

$ db2 rollforward db SAMPLE to end of backup and stop

This is even more useful in partitioned databases where it can be difficult to find the minumum PIT across all partitions.

Moreover, it is now easy to incorporate the rollforward to point-in-time operation in a script.

Be lazy, write a script and let it be known as automation :)

Comments

Add New

Write comment

Name:

Email:

Website:

Title:

UBBCode:

Please input the anti-spam code that you can read in the image.

Drew DeNardo - This should be a restore option

|Sunday, 07 September 2008 01:36

The basics for a garden-variety restore of an online backup SHOULD be built in
to the RESTORE command. Today to restore an online backup I have to:

mkdir
/tmp/logtemp
db2 restore db XYZ from /the/backup/file/location/ logtarget
/tmp/logtemp replace existing
db2 get db cfg for XYZ | grep "Path to
log"
cp /tmp/logtemp/*LOG {directory name returned by previous command}
db2
rollforward db XYZ to end of logs and stop
rm -rf /tmp/logtemp

I *think* that
this is a common (and plain-vanilla) thing to do with online backups -- the
closest analog to using an offline backup.

I'd love to be able to do this
db2
restore db XYZ from /the/backup/file/location ROLLFORWARD INCLUDEDLOGS replace
existing

I understand that there are lots of combinations for what you may want
to do with online backups -- so if you don't specify "ROLLFORWARD" (and
its sub-clause of "INCLUDEDLOGS" that flag whether or not to
restore/rollforward the logs included in the backup file) the restore command
will do the exact same thing it does today.

I do understand that offline and
online backups are different. Why can't the restore command, at least in the
basic/simple/common cases, mask the complexity for us?

Reply | Quote

db2admin - Try the RECOVER command

|Monday, 08 September 2008 04:14

Drew,

I agree with you that the RESTORE/ROLLFORWARD routine in DB2 in
unnecessarily complex. It becomes more complex if you have to do a redirected
restore.

What I would love to have is an option of restarting the restore from
a consistent point if it fails due to some issue like insufficient space in the
device of TSM error. In case of large databases, its a big pain to see the
restore fail due to TSM error after running it for 8 hours, and then have to
restart it from the very beginning.

You may also check out the RECOVER command
which I think is available since version 8.2. Its RESTORE and ROLLFORWARD
combined into one (just as you want)

Reply | Quote

doyoudb2

ROLLFORWARD to END OF BACKUP - Finally!

3.25 Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."

Search Site

Subscribe

Categories

Sign In/Register

Syndicate Feeds - Subscribe

Related Articles