DB2 UDB recovery

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neil Truby - 18 Apr 2004 10:31 GMT

There's something clearly missing in my understanding of recovery:

I set up a small sample datavase and deleted all the rows from a table.
Crucially, I omitted the "commit". I then shut down db2, and copied
everything under the db2inst1/db2inst1/NODE0000 directory to another server.

When I restarted db2 on the first server the rows were still missing.
But on the second server they were still there :-(
I repeated the exercise several times to be certain.

Obviously some sort of recovery has taken place on the second server.
Including a commit after the delete confirmed this: the rows were missing on
both sides. But I am confised as to how this would happen on the second
server but not the first.

Also, is there no implied commit in a db2stop?

thanks
Neil

Reply to this Message

Mark A - 18 Apr 2004 11:07 GMT

> There's something clearly missing in my understanding of recovery:
>
[quoted text clipped - 15 lines]
> thanks
> Neil

Most likely, the updates were rolled back when the original database was
stopped or restarted (since they were logged but not committed).

There is no implied commit for a db2stop. DB2 does not support copying
directories from one server to another.

Reply to this Message

Neil Truby - 18 Apr 2004 18:29 GMT

> > There's something clearly missing in my understanding of recovery:
> >
[quoted text clipped - 20 lines]
> Most likely, the updates were rolled back when the original database was
> stopped or restarted (since they were logged but not committed).

Indeed. But my question is: why did this rollback occur on the second
server, but not the first? Both presumably had the same set of logs (also
stored under the ~db2inst1/db2inst1/NODE0000 directory).

I fully take your point about this being an ineffective way to back up
databases: please see my reply to another correspondent.

Reply to this Message

Mark A - 18 Apr 2004 20:29 GMT

> > Most likely, the updates were rolled back when the original database was
> > stopped or restarted (since they were logged but not committed).
[quoted text clipped - 5 lines]
> I fully take your point about this being an ineffective way to back up
> databases: please see my reply to another correspondent.

The rollback did NOT occur on the second server. That is why the uncommitted
rows are still there. The rollback occurred on the original server which
deleted the rows because they were not committed.

I am not sure whether the rollback occurred on the original server during
db2stop or during restart. It might depend on whether you used the force
option on db2stop.

Reply to this Message

Philip Nelson - 18 Apr 2004 11:08 GMT

> There's something clearly missing in my understanding of recovery:
>
[quoted text clipped - 17 lines]
> thanks
> Neil

Neil, this is emphatically not the way to backup and recovery a DB2
database, or to move a DB2 database to another server. I know it is the
way that you do things on some other DBMSes, but not here. While I've
heard of some people doing this, I'd say that unless you use the IBM
supplied utilities "all bets are off" !!!

I wouldn't expect any uncommitted transactions to be implicitly applied by a
DB2 stop : the DBMS should not assume anything about the logic of an
application, so the only safe thing to do is a rollback. In addition, when
you start DB2 it does a "crash recovery" which will rollback any
uncommitted transactions.

Now to look at how you should do this -

Backups are taken using the BACKUP command, which produces a file (or files)
containing everything you need to recover (in the case of an offline
backup) or everything bar the logs (in a case of an offline backup). In
the new "Stinger" the online backup will even package with the backup the
logs needed to do the recovery as well.

To bring this backup onto another server you use the RESTORE and (possibly)
ROLLFORWARD commands. If you are happy to bring everything back under
exactly the same tablespace container details as the backup then a normal
restore will do. If you want to change the tablespace container locations
then you need to do a "redirected restore", where you use the "SET
TABLESPACE CONTAINERS" command between a "RESTORE ... REDIRECT" and a
"RESTORE ... CONTINUE" to specify the changes you require. I have a script
that generates a base redirected restore script from the source database.

If you want to move data between two databases then you should be looking at
EXPORT, IMPORT, LOAD and the "wrapper" utility db2move. If you want to
extract DDL for a database you should be looking at db2look.

HTH

Phil Nelson

Reply to this Message

Neil Truby - 18 Apr 2004 19:00 GMT

> > There's something clearly missing in my understanding of recovery:
> >
[quoted text clipped - 29 lines]
> you start DB2 it does a "crash recovery" which will rollback any
> uncommitted transactions.

In fact, what I am doing is demonstrating the facility to use a particular
storage array to take "flash copies" of a DB2 database for subsequent use as
a backup or as a development/support server. I'll bow to your undoubtedly
superior knowledge of DB2 but will opine that this is a very effective
solution to, say, Informix or Oracle databases. From my reading of various
papers the same techniques *can* be used with DB2 UDB.

We have implemented exactly this technique at a very risk-adverse UK
retailer, albeit on Informix. In Informix it is important to ensure that a
"blocked checkpoint" occurs for the few seconds that the flash copy takes,
to write out all buffer pool data to disk and prevent new transactions
starting. This gives a physically-consistent database. My careful reading
of the DB2 UDB manual's Crash Recovery section suggests that this may be
unnecessary on DB2, and if it is necessary I can't find a suitable utility.

Informix supports an external restore from such a "backup" - I've done this
successfully in DB2 too. Whether or not DB2 supports subsequent application
of logs for a point in time recovery I don't know, but I'd like to bet that
it does.

Why my two servers behaved differently with exactly the same tablespace data
is still unexplained though :-((

Reply to this Message

Philip Nelson - 18 Apr 2004 21:55 GMT

Neil,

Now you have explained what you are trying to achieve I can point you down
another road. What you described is supported by DB2, but again you have
to use the correct DB2 commands to achieve it.

You want to read the "Data Recovery and High Availability Guide and
Reference" for full details. The place to start is Chapter 5 in the
section "High Availability Through Online Split Mirror and Suspended I/O
Support".

The basic process is -

On the source machine -

db2 "set write suspend ..."
Use OS Tools to Split The Mirror (or take your flash copy)
db2 "set write resume ..."

On the target machine -

db2start
db2inidb ...

The details of the "OS Tools" varies based on your hardware vendor.

There was an excellent presentation by someone from Network Appliances at a
fairly recent IDUG. I can probably dig this presentation out for you if
you wish.

Phil

>> > There's something clearly missing in my understanding of recovery:
>> >
[quoted text clipped - 61 lines]
> Why my two servers behaved differently with exactly the same tablespace
> data is still unexplained though :-((

Reply to this Message

Neil Truby - 18 Apr 2004 22:35 GMT

> Neil,
>
[quoted text clipped - 19 lines]
> db2start
> db2inidb ...

Beautiful! just what I wanted to know, thank you.
May I be permitted one last question? I found a discussion of this which
states: "Initializes a mirrored database in a split mirror environment. The
mirrored database can be initialized as a clone of the primary database,
placed in roll forward pending state ..." What would be the steps to apply
backed-up logs from the original server for a roll forward?

cheers
Neil

Reply to this Message

Philip Nelson - 19 Apr 2004 13:30 GMT

>> Neil,
>>
[quoted text clipped - 7 lines]
>> section "High Availability Through Online Split Mirror and Suspended I/O
>> Support".

>> The basic process is -
>>
[quoted text clipped - 19 lines]
> cheers
> Neil

Neil,

That is described in gory detail in the chapter in the manual I referred to.
It's probably best for you just to read this, rather than have me type up a
summary here. Manuals from -

http://www.software.ibm.com/data/db2/library

Reply to this Message