DB2 UDB recovery

[Neil Truby]

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

[Mark A]

"Neil Truby" <ne********@ard enta.com> wrote in message
news:c5******** ****@ID-162943.news.uni-berlin.de...

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

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.

[Philip Nelson]

Neil Truby wrote:

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

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

[Neil Truby]

"Mark A" <ma@switchboard .net> wrote in message
news:7r******** ******@news.usw est.net...

"Neil Truby" <ne********@ard enta.com> wrote in message
news:c5******** ****@ID-162943.news.uni-berlin.de...

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

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.

[Neil Truby]

"Philip Nelson" <te*****@scotdb .com> wrote in message
news:Xs******** *************** @news.easynews. com...

Neil Truby wrote:

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
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.

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 :-((

[Mark A]

> > 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.

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.

[Philip Nelson]

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

Neil Truby wrote:

"Philip Nelson" <te*****@scotdb .com> wrote in message
news:Xs******** *************** @news.easynews. com...

Neil Truby wrote:

> 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

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.

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 :-((

[Neil Truby]

"Philip Nelson" <te*****@scotdb .com> wrote in message
news:NW******** *************** @news.easynews. com...

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 ...

Beautiful! just what I wanted to know, thank you.
May I be permitted one last question? I found a discussion of this which
states: "Initialize s 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

[Philip Nelson]

Neil Truby wrote:

"Philip Nelson" <te*****@scotdb .com> wrote in message
news:NW******** *************** @news.easynews. com...

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 ...

Beautiful! just what I wanted to know, thank you.
May I be permitted one last question? I found a discussion of this which
states: "Initialize s 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

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