db2 restore with automatic storage tablespace

I was trying to restore a db from inst1 to inst2. The db path (file system paths) in inst1 were different as compared to inst2. The file systems between the 2 have very restricted permissions. Usually when I am restoring a database from one system to another I am used to creating a blank database with correct path and then in the restore I specify REDIRECT keyword. And my assumption in doing so was the restore will happen to the path that is there in the database (not in the backup set). So, when I tried to restore a db from inst1 to inst2, my assumption did not work, received following error message,

SQL0970N  The system attempted to write to a read-only file.  SQLSTATE=55009

I looked at the db2diag.log and found following message.

INSTANCE: inst2                NODE : 000         DB   : {existing db name}
EDUID   : 9933                 EDUNAME: db2loggr ({existing db name}) 0
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:10
MESSAGE : ZRC=0x840F0001=-2079391743=SQLO_ACCD "Access Denied"
          DIA8701C Access denied for resource "", operating system return code
          was "".
CALLED  : OS, -, mkdir
OSERR   : EACCES (13) "Permission denied"
DATA #1 : File name, 78 bytes
/home/inst1/db2fs/inst1/NODE0000/{old db name}/LOGARC/inst2/{existing db name}/NODE0000/C0000001/
DATA #2 : signed integer, 4 bytes
488
DATA #3 : signed integer, 4 bytes
488
DATA #4 : String, 105 bytes
Search for ossError*Analysis probe point after this log entry for further
self-diagnosis of this problem.

Looks like db2 still tries to restore to the path that is there hidden in the backup set. In my case inst2 was trying to restore on inst1's (db) file system paths, to which it did not had access permissions.

So whats the solution ? use following format of restore command.

db2 "RESTORE DB {dbname in image} FROM {backup image file path} TAKEN AT {timestamp} ON {db2fs_path} DBPATH ON {db_path} INTO {existing db name}
db2 "rollforward db {existing db name} complete"

And this the case with "automatic storage tablespace paths" and I guess the DMS should not have this issue.

db2 restore with mix of full and incremental backups

Following are the steps to restore db2 database when you have mix of db backup with full, incremental and delta are involved.This illustration is based on Windows system.

text in blue are db2 commands
text in green are my comments
text in black are output seen on command line console.

db2 create db zzz_tmp
db2 restore db zzz incremental automatic from C:\ taken at 20110707154108 into zzz_tmp
db2 rollforward db zzz_tmp to end of backup and complete

-- you will see following message, if roll-forward is not complete

SQL4970N Roll-forward recovery on database "ZZZ_TMP" cannot reach the
specified stop point (end-of-log or point-in-time) on database partition(s)
"0". Roll-forward recovery processing has halted on log file "S0000026.LOG".

-- if you see a message as listed above you need to get(copy or restore from TSM)
-- the specified log file to zzz_tmp's active log directory

db2 rollforward db zzz_tmp to end of backup and complete

-- if successfull (you mostly will be) you will see following message

Rollforward Status


Input database alias = zzz_tmp
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read = Log files processed = S0000026.LOG - S0000026.LOG
Last committed transaction = 2011-07-07-22.41.13.000000 UTC


DB20000I The ROLLFORWARD command completed successfully.

-- do some smoke testing, by connecting and possibly quering the db

db2 connect to zzz_tmp

Database Connection Information
Database server = DB2/NT 9.7.0
SQL authorization ID = db2admin
Local database alias = ZZZ_TMP