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
