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:
Quote:
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?
>
