[ADSM-L] TSM db2 restore script, and a shell-programming challenge.

Howdy all.  Included below please find a shell script about which I
invite kibitz and constructive criticism.  I hope it may save some of
you time in the future.

I'd also like help from those of you whose C shell kung fu is great: I
have one PERL interlude which I consider aesthetically poor, and if I
could do it all in shell I would find it better.

----

I recently had occasion to update my procedures for running a db2
restore from TSM.  Mumblety years ago, I had a rather clunky thing
that required gobs and gobs of tempspace and used lots of effort to
e.g. calculate tablespaces and assign containers.

Since then, we've begun doing our full backups 'WITH LOGS', and so I
pulled up the old creaky script and reengineered.  To my surprise and
distinct pleasure, I've come up with something that's not just simpler
than last time, it's drastically more efficient of space.

Most importantly, it doesn't require a-priori knowledge of the
tablespace catalog.  The best IBM article on the topic I've found

http://www.ibm.com/developerworks/data/library/techarticle/0212mulligan/0212mulligan.html

depends on access to the to-be-restored database when one is planning
ones plans.  This is nice if you've got it, but terribly frustrating
if you don't or if you got it wrong.

So, here's pseudocode of what I do.

0) Before the script is attempted, Make sure that the source instance
   has granted TSM access to the destination instance and use.
   Pleasantly, this can be done even if the source database is in
   splinters on the floor.

1) Start a redirected restore with appropriate TSM re-redirection
   options.

2) LIST TABLESPACES.  Feed this into a contorted and fragile pile of
   PERL which turns something like

 Tablespace ID                        = 10
 Name                                 = SYSTOOLSPACE
 Type                                 = System managed space
 Contents                             = Any data
 State                                = 0x0000

   into something like

   $bag = {
   "Tablespace ID"  => "10",
   "Name"           => "SYSTOOLSPACE",
   }

   and thence into

set tablespace containers for 10 using ( PATH 
/export/db2/dbzui020/cont/RMDB/SYSTOOLSPACEQQ )  ;

   Drop these into a tempfile.

3) Execute the set of assignments thus constructed

4) continue the restore

5) roll forward the logs, to end of log.

This leaves the database ready to connect.



The key is that when you start a redirected restore, you enter into an
odd state w.r.t. DB2: You have, in that session, a 'connection' to a
'database' which will otherwise not be acknowledged by the database
manager.  If you exit that shell, your 'connection' evaporates and the
restore is terminated.  So if you do similar things from an e.g. PERL
script with a series of system("command"); you'll fail.


- Allen S. Rout


----- Begin restore.scr.base


#!/bin/sh

set -x
#
#  This is designed to function in the following circumstances:
#
#  + The DB2 backup was performed WITH LOGS.
#  + The DB2 backup was to TSM.
#
#  Copy the file to e.g. restore.scr.YOURDBNAME and edit.
#
#  By default, it'll arrange one directory per tablespace,
#  all situated in what I'm calling the "tablespace root".
#  It'll make a file noting the tablespace assignments, and
#  permit you to edit it if you please.  Then you can continue.
#
#  On source DB:
#
#  db2adutl grant user [DESTUSER] on nodename [DESTNODE] for db [SRCDB]
#
#  You should then, from the target instance, be able to do
#
#  db2adutl query db [SRCDB] nodename [SRCONODE] owner [SRCOWNER]
#

# Use your tablespace root here,  there'll be opportunity to change it later.

TSBASE=

SRCNODE=
SRCOWNER=
SRCDB=

# Timestamp.  Best source for these is db2adutl query.
TAKEN_AT=

# If you're going to rename the database in the transition, do it here.
DSTDB=$SRCDB

TSPATH=$TSBASE/$DSTDB
LOGPATH=$TSPATH/tmplogs

TSFILE=./tsassign.$DSTDB.sql

if [ "$TSBASE" = "" -o  "$SRCNODE" = "" -o  "$SRCOWNER" = "" -o  "$SRCDB" = "" 
-o  "$TAKEN_AT" = ""  ]
then
 echo "Many variables must be set; look carefully... "
 exit 1;
fi

exit 1;

mkdir -p $LOGPATH
rm $LOGPATH/*.LOG

#
date

db2 restore database $SRCDB use tsm options "'-fromnode=$SRCNODE 
-fromowner=$SRCOWNER'"  taken at $TAKEN_AT into $DSTDB logtarget $LOGPATH 
redirect without prompting

date

db2 list tablespaces | perl -ne ' next unless ($k,$v) =  /(Tablespace 
ID|Name).+= (.+)$/; $bag->{$k} = $v;  $k eq "Name" && print sprintf(" set 
tablespace containers for %s using ( PATH '%s/%s' )  ; \n",$bag->{"Tablespace 
ID"},$ENV{TSPATH},$bag->{Name}); ' > $TSFILE


cat $TSFILE

echo "Is this what you intended?  If not, edit $TSFILE"
echo -n "Continue? (yes/anything else): "
read CONT

if [ "$CONT" != "yes" ]
then
      exit 1;
fi

db2 -t -v -f ./$TSFILE

date

db2 restore database $SRCDB continue

date

db2 " rollforward database $DSTDB to end of logs and stop overflow log path ( 
$LOGPATH ) noretrieve  "

date


----- Begin restore.scr.base