Restoring selected tables / table data

[Troels Arvin]

Hello,

Every so often, I'm asked to help people recover data from tables that
were either dropped or where to much data was DELETEed.

The complications related to restoring data are a problem. The SAS users
are laughing because they can (to a certain extend) easily go back to an
earlier SAS table by simply starting dsm. Of course, a flat file table is
different than a relational table; but still, the trouble related to
restoring DB2 data are resulting in massive table copying in the
databases.

I've read some of the DB2 recovery documentation by now. Am I right in
concluding that whenever I want to restore data (including dropped
tables) to a point in the past, it involves blocking the related
tablespace while restoring the data? - And that the only options to avoid
this are:

- restore the complete database to a specific point in time, using
a separate server (which entails setting up containers, etc, on
the separate server), or

- once in a while, dump all the tables to flat IXF files; but can
this be done in "uncommited read" mode, so that I minimize user
distubance (the users would then have to accept potentially
inconsistent data)

For cases where tablespace blocking is acceptable:
The tablespace typically contain many tables, and for most of them it
would be a disaster if I simply rolled the whole tablespace back to some
point in time. In other words: How do I restore a single table to a
specific point in time, without disturbing the rest of the tables in the
tablespace? Is the following possible:
1. roll the whole tablespace back to noon the day before
(entails a RESTORE)
2. export the relevant table to an ixf file
3. roll the whole tablespace further on to present time again
(entails another RESTORE, as far as I can see)
4. import the ixf file from step 2
If I have to go set up a dedicated db2 instance for restore operations:
Our production databases typically contain a large number of tablespaces,
potentially containing many containers. Is there a way to avoid having to
define all these structures in a restore-instance? - I.e.: Is there a way
to restore a complete database (to a point in time), telling DB2 to
ignore tablespace/container settings and just restore all schemas into
one single tablespace?

--
Regards,
Troels Arvin <tr****@arvin.d k>
http://troels.arvin.dk/

[Serge Rielau]

Troels,

I think the "recover manager" can recover lost data from the logs.
It costs $$, but hey, there is an educational aspect to it :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Hemant Shah]

While stranded on information super highway Troels Arvin wrote:

Hello,

Every so often, I'm asked to help people recover data from tables that
were either dropped or where to much data was DELETEed.

The complications related to restoring data are a problem. The SAS users
are laughing because they can (to a certain extend) easily go back to an
earlier SAS table by simply starting dsm. Of course, a flat file table is
different than a relational table; but still, the trouble related to
restoring DB2 data are resulting in massive table copying in the
databases.

I've read some of the DB2 recovery documentation by now. Am I right in
concluding that whenever I want to restore data (including dropped
tables) to a point in the past, it involves blocking the related
tablespace while restoring the data? - And that the only options to avoid
this are:

- restore the complete database to a specific point in time, using
a separate server (which entails setting up containers, etc, on
the separate server), or

- once in a while, dump all the tables to flat IXF files; but can
this be done in "uncommited read" mode, so that I minimize user
distubance (the users would then have to accept potentially
inconsistent data)

For cases where tablespace blocking is acceptable:
The tablespace typically contain many tables, and for most of them it
would be a disaster if I simply rolled the whole tablespace back to some
point in time. In other words: How do I restore a single table to a
specific point in time, without disturbing the rest of the tables in the
tablespace? Is the following possible:
1. roll the whole tablespace back to noon the day before
(entails a RESTORE)
2. export the relevant table to an ixf file
3. roll the whole tablespace further on to present time again
(entails another RESTORE, as far as I can see)
4. import the ixf file from step 2
If I have to go set up a dedicated db2 instance for restore operations:
Our production databases typically contain a large number of tablespaces,
potentially containing many containers. Is there a way to avoid having to
define all these structures in a restore-instance? - I.e.: Is there a way
to restore a complete database (to a point in time), telling DB2 to
ignore tablespace/container settings and just restore all schemas into
one single tablespace?

--
Regards,
Troels Arvin <tr****@arvin.d k>
http://troels.arvin.dk/

How many tables do you have?
Maybe you can have one table per table space, that way you will not affect
other tables while restoring one table.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Troels Arvin]

On Thu, 24 May 2007 15:10:25 +0000, Hemant Shah wrote:

How many tables do you have?

"select count(*) from syscat.tables" yields 5276.

Maybe you can have one table per table space, that way you will not
affect other tables while restoring one table.

One table per tablespace is not realistic here. Of course, we can think
about table/tablespace refactorings, but most often, the mistakes happen
in tables/tablespaces with high activity.

--
Regards,
Troels Arvin <tr****@arvin.d k>
http://troels.arvin.dk/

[aj]

Hi Troels

Another approach is to just export the tables. I'm not sure about
the granularity you require, but here's a shell script
that dumps pipe delimited ascii backups of specified files, keeping 5
versions worth and compressing them to keep disk space down. For
dropped tables, I would switch the Export to IXF format so you get
structure AND data. Run this as often as you need....

HTH

aj

############### ############### ############### ############### ####
# This script backs up database tables in ascii format

cd /db2/backups/ascii

echo "connect to <MYDB;" /db2/backups/ascii/backup.sql
echo "set schema <MYSCHEMA;" >/db2/backups/ascii/backup.sql

# *************** *************** *************** *************** *****
for table in <LIST TABLES, SPACE DELIMITED, YOU WANT TO BACK UP>
do
if [ -s $table.5.gz ]
then
rm $table.5.gz
fi

if [ -s $table.4.gz ]
then
mv $table.4.gz $table.5.gz
fi

if [ -s $table.3.gz ]
then
mv $table.3.gz $table.4.gz
fi

if [ -s $table.2.gz ]
then
mv $table.2.gz $table.3.gz
fi

if [ -s $table.1.gz ]
then
mv $table.1.gz $table.2.gz
fi

if [ -s $table.txt.gz ]
then
mv $table.txt.gz $table.1.gz
fi

echo $table
done | /usr/bin/awk '
NF == 1 {
printf("EXPORT TO %s.txt OF DEL MODIFIED BY coldel| chardel\"\"
decpt. decplusblank datesiso MESSAGES %s.msg SELECT * FROM %s
;\n",$1,$1,$1) ;
}' >/db2/backups/ascii/backup.sql

echo "connect reset ;" >/db2/backups/ascii/backup.sql
db2 -tvf /db2/backups/ascii/backup.sql

Troels Arvin wrote:

Hello,

Every so often, I'm asked to help people recover data from tables that
were either dropped or where to much data was DELETEed.

The complications related to restoring data are a problem. The SAS users
are laughing because they can (to a certain extend) easily go back to an
earlier SAS table by simply starting dsm. Of course, a flat file table is
different than a relational table; but still, the trouble related to
restoring DB2 data are resulting in massive table copying in the
databases.

I've read some of the DB2 recovery documentation by now. Am I right in
concluding that whenever I want to restore data (including dropped
tables) to a point in the past, it involves blocking the related
tablespace while restoring the data? - And that the only options to avoid
this are:

- restore the complete database to a specific point in time, using
a separate server (which entails setting up containers, etc, on
the separate server), or

- once in a while, dump all the tables to flat IXF files; but can
this be done in "uncommited read" mode, so that I minimize user
distubance (the users would then have to accept potentially
inconsistent data)

For cases where tablespace blocking is acceptable:
The tablespace typically contain many tables, and for most of them it
would be a disaster if I simply rolled the whole tablespace back to some
point in time. In other words: How do I restore a single table to a
specific point in time, without disturbing the rest of the tables in the
tablespace? Is the following possible:
1. roll the whole tablespace back to noon the day before
(entails a RESTORE)
2. export the relevant table to an ixf file
3. roll the whole tablespace further on to present time again
(entails another RESTORE, as far as I can see)
4. import the ixf file from step 2
If I have to go set up a dedicated db2 instance for restore operations:
Our production databases typically contain a large number of tablespaces,
potentially containing many containers. Is there a way to avoid having to
define all these structures in a restore-instance? - I.e.: Is there a way
to restore a complete database (to a point in time), telling DB2 to
ignore tablespace/container settings and just restore all schemas into
one single tablespace?

[Troels Arvin]

On Fri, 25 May 2007 12:56:45 -0400, aj wrote:

Another approach is to just export the tables.

Yes, I thought about that. But I still need "true"==transac tionally
consistent backupdata. So if I start dumping to flat files as well, my
storage consumption will rise rather dramatically -- and worse: as far as
I know, EXPORTing blocks the table being exported (right? - or can EXPORT
run in UR isolation mode?).

I'm not sure about the
granularity you require, but here's a shell script that dumps pipe
delimited ascii backups of specified files, keeping 5 versions worth and
compressing them to keep disk space down.

Thanks a lot for inspiration.

--
Regards,
Troels Arvin <tr****@arvin.d k>
http://troels.arvin.dk/