Friday, August 19, 2011

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.

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.