By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

Restoring selected tables

P: n/a
Hello,

I've run into situations where a table was accidentally dropped. The
related database contained lots of other tables in lots of other schemas
used by many different users. So I couldn't simply recover to a previous
point in time (the other users' changes would be lost and/or there would
be periods of no database availability).

It seems that my only option was a complete, redirected restore. (Compete,
because redirected restore operations can't operate on selected
tablespaces). As some of our databases are very large, a complete,
redirected restore takes a lot of time.

This turns out being a problem.

What are my options? I'm considering supplementing our regular (TSM)
online backup scheme with daily dumps to raw files, based on EXPORT and
db2look. With a little text massage of the dumps, I would be able to
restore selected tables without going through RESTORE. - And in some cases
this would be fine because the dropped table(s) might not have been
updated recently. Is my thinking (in)sane?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Jan 31 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I presume you are running DB2 V8 on LUW.
At that level, every tablespace is automatically created with a parm the
enables DROPPED TABLE RECOVERY.
When the table is dropped, DB2 writes in the log file a unique transaction
id with the drop command.
The same info is also wrtitten in the DB2DIAG.LOG file with the DDL of the
table.
Do: db2 list history DROPPED all to see the messages.

To recover:
Restore the tablespace of the table and roll it forward to end of logs.
The ROLLFORWARD command wil HAVE TO specify [RECOVER DROPPED TABLE
dropped-table-id TO export-directory]
The id you get from the db2diag.log and you specify the directory. The file
is DEL format.
After successful roll forward, you can use the DDL to create the table **
and the data will be in the export directory.

If you are at V7, then the tblspaces had to have been created or ALTERed
with DROPPED TABLE RECOVERY ON. If not, you are back to where you were in
your note.

Hope this helps, Pierre.
PS: If you know (guess) what tables are candidates for drop, runninf db2look
to extract the DDL before hand will save you time as you can keep that in a
file and use it where I typed **

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Troels Arvin" <tr****@arvin.dka écrit dans le message de news:
ep**********@news.net.uni-c.dk...
Hello,

I've run into situations where a table was accidentally dropped. The
related database contained lots of other tables in lots of other schemas
used by many different users. So I couldn't simply recover to a previous
point in time (the other users' changes would be lost and/or there would
be periods of no database availability).

It seems that my only option was a complete, redirected restore. (Compete,
because redirected restore operations can't operate on selected
tablespaces). As some of our databases are very large, a complete,
redirected restore takes a lot of time.

This turns out being a problem.

What are my options? I'm considering supplementing our regular (TSM)
online backup scheme with daily dumps to raw files, based on EXPORT and
db2look. With a little text massage of the dumps, I would be able to
restore selected tables without going through RESTORE. - And in some cases
this would be fine because the dropped table(s) might not have been
updated recently. Is my thinking (in)sane?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Jan 31 '07 #2

P: n/a
aj
No, I don't think your thinking is insane. In fact, 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.
Assuming daily runs, as long as you realize within 5 days, you can
get table data back. For dropped tables, I would switch the Export
to IXF format so you get structure AND data..

You know this, but remember this is not Point In Time (PIT) Recovery!!
Its only as good as the last run. This, combined w/ some audit trail
stuff of mine, has gotten me out of trouble a few times..

Don't know what OS you have. Mine is Linux.

cheers

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,

I've run into situations where a table was accidentally dropped. The
related database contained lots of other tables in lots of other schemas
used by many different users. So I couldn't simply recover to a previous
point in time (the other users' changes would be lost and/or there would
be periods of no database availability).

It seems that my only option was a complete, redirected restore. (Compete,
because redirected restore operations can't operate on selected
tablespaces). As some of our databases are very large, a complete,
redirected restore takes a lot of time.

This turns out being a problem.

What are my options? I'm considering supplementing our regular (TSM)
online backup scheme with daily dumps to raw files, based on EXPORT and
db2look. With a little text massage of the dumps, I would be able to
restore selected tables without going through RESTORE. - And in some cases
this would be fine because the dropped table(s) might not have been
updated recently. Is my thinking (in)sane?
Jan 31 '07 #3

P: n/a
On Wed, 31 Jan 2007 16:58:59 -0500, Pierre Saint-Jacques wrote:
I presume you are running DB2 V8 on LUW.
At that level, every tablespace is automatically created with a parm the
enables DROPPED TABLE RECOVERY.
The version is 8.1.1.96 (from db2level), but I believe that the particular
database was created some versions before the current DB2 generation.

When I check the tablespaces, almost all of them have DROP_TABLE=N. I
suspect that the particular database defaults to DROP_TABLE=N for new
tablespaces. Is there a way to specify that the default DROP_TABLE state
should be Y for future tablespaces?

Thanks for your detailed description, by the way.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 1 '07 #4

P: n/a
If your V8 tblspces show DROP_TABLE=N, it is because they were migrated from
versions lower than V8 .
For each of the pre-V8 tablespaces, you will need to use:
ALTER TABLESPACE tbspacename...DROPPED TABLE RECOVERY ON
Every tablespace you create post V* install will automatically have the DROP
ON. It is the default for all new cpaces as of V8.1.1
Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Troels Arvin" <tr****@arvin.dka écrit dans le message de news:
ep**********@news.net.uni-c.dk...
On Wed, 31 Jan 2007 16:58:59 -0500, Pierre Saint-Jacques wrote:
>I presume you are running DB2 V8 on LUW.
At that level, every tablespace is automatically created with a parm the
enables DROPPED TABLE RECOVERY.

The version is 8.1.1.96 (from db2level), but I believe that the particular
database was created some versions before the current DB2 generation.

When I check the tablespaces, almost all of them have DROP_TABLE=N. I
suspect that the particular database defaults to DROP_TABLE=N for new
tablespaces. Is there a way to specify that the default DROP_TABLE state
should be Y for future tablespaces?

Thanks for your detailed description, by the way.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 2 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.