DB2 restores after unrecoverable load in single and mulit-partitioned DB

[dbagirltx]

We have done some testing with mixed and forgotten results. So I'm
hoping that asking here can clarify some issues for us. Right now we do
one weekly warm backup. Throughout the week there are multiple
unrecoverable loads. We are tyring to come up with the best backup
strategy for this system? It is a large dev data warehouse.

What happens when a table is loaded unrecoverable and then we need to
restore (no DDL has been done)? Can we recover using our last warm
backup if we rollforward just to before the load?

What about a table in a multi-table tablespace, what happens to other
tables? We have found that after an unrecoverable load the only thing
we can do is drop the table but most times the table is one of many.

What happens in a multi-partition environment? Let's say our table is
across 2 partitions?

What if we have a tablespace backup taken right after the unrecoverable
load but before the next full backup, can we do a restore then? How?

What happens to the backup with the unrecoverable load is happening at
the same time as the backup?

Thanks for any comment,
DBAGIRLTX

[Pierre Saint-Jacques]

Well, I guess you are living in intresting times !!!
See below. ######

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"dbagirltx" <db*******@yahoo.com> a écrit dans le message de news:
11**********************@b68g2000cwa.googlegroups. com...

We have done some testing with mixed and forgotten results. So I'm
hoping that asking here can clarify some issues for us. Right now we do
one weekly warm backup. Throughout the week there are multiple
unrecoverable loads. We are tyring to come up with the best backup
strategy for this system? It is a large dev data warehouse.

What happens when a table is loaded unrecoverable and then we need to
restore (no DDL has been done)? Can we recover using our last warm
backup if we rollforward just to before the load? #######
It does not matter whether DDl has been done. You cannot do a tablespace
restore/rollforward prior to drop.
You have to a full db restore/ rollforward to Point in Tine prior to drop.
Extract ddl and data the inactive objct in tablespace. Then re restore the
full db, roll it forward to end of logs which is through the load, drop the
inactive nonrecoverable load table, and then use extracted ddl and data to
reconstruct.
What about a table in a multi-table tablespace, what happens to other
tables? We have found that after an unrecoverable load the only thing
we can do is drop the table but most times the table is one of many. #######
See above, The pint is that a nonrecoverable load brings the minimum PIT of
the tablespace to the timestamp of the load. From that point on the
tablespace can only be recovered at leas to that minimum PIT but not before
it.
Method 1 above answers to protecting the other tables in the tablespace as
the second restore/rollforward will put them consistent with the db and also
the inactive table is then dropped and rebuilt.
What happens in a multi-partition environment? Let's say our table is
across 2 partitions? #######
I believe that because the tabvlespace which has the table is on more than
one partition, you would have to apply the step above to all partutions on
which the tablespace is defined.
What if we have a tablespace backup taken right after the unrecoverable
load but before the next full backup, can we do a restore then? How? #######
In this case, your strategy becomes:
Resotre the image taken after the unrecoverable load and roll it forward as
as you need, iff you only want to recover it.
For a full db restore where theimage of the db is prior to the unrecoverable
load, thwen your strategy is as follows:
restore the full db
restore the tablespace image taken after the unrecoverable load
roll forward the whole db to the end of the logs.
As the tablespace has been restored it is consistent as of the time of its
backup. The roll forward will ignore any transaction for that table space
which precedes the timestamp of the restored image.
What happens to the backup with the unrecoverable load is happening at
the same time as the backup? #######
If tyou are executing a load on a table, the db puts the tablespace in load
pending or other states as the load progress. For a tablespace to be backed
up it "must" be in normal state so the backup request will not launch.
Similarly, if the tablespace backup is launched the load command will bounce
so that the two cannot ever happen concurrently.
Thanks for any comment,
DBAGIRLTX

HTH, Pierre.