Restore DB2 9.1 backup problem

[modeler]

Hello,

I am trying to restore a DB2 9.1 fp2 backup to another DB2 9.1 fp2 machine, both are on Windows.

C:\Program Files\IBM\SQLLIB\BIN>db2 -tvf C:\db2_data\test.db2
UPDATE COMMAND OPTIONS USING S ON Z ON MYDB_NODE0000.out V ON
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

SET CLIENT ATTACH_DBPARTITIONNUM 0
DB20000I The SET CLIENT command completed successfully.

SET CLIENT CONNECT_DBPARTITIONNUM 0
DB20000I The SET CLIENT command completed successfully.

RESTORE DATABASE mydb FROM 'C:\' TAKEN AT 20080310154100 INTO mydb REDIRECT WITHOU
T ROLLING FORWARD
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.

SET TABLESPACE CONTAINERS FOR 2 USING ( FILE 'C:\db2_data\userspace1'
256000 )
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 3 USING ( FILE 'C:\db2_data\regtbs'
768000 )
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 4 USING ( FILE 'C:\db2_data\idxtbs'
128000 )
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 5 USING ( FILE 'C:\db2_data\lobtbs'
512000 )
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 6 USING ( PATH 'C:\db2_data\testspace' )
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

RESTORE DATABASE mydb CONTINUE
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.

As you can see, although all the redirects finish successfully, it fails to run the restore db mydb continue

1. The script is generated by db2

2. I manually run the above commands too, got the same problem

3. There are 0~7 tablespace containers, but 0, 1, and 7 are automatic storage tablespace,
e.g.

db2 => set tablespace containers for 7 using (file "C:\db2_data\C0000000.LRG" 32)
SQL20319N The SET TABLESPACE CONTAINERS command is not allowed on an
automatic storage table space. SQLSTATE=55061

4. The only thing I changed is the file size in the SET TABLESPACE CONTAINERS command, since I don't have enough space for the tablespace allocated on the original machine, but I do have enough space to hold all the data (the backup file is about 28GB only).

Any suggestion/help is appreciated!

[Brian Jackson]

I have just had the same problem, but I have finally tracked down the cause.
One of more of the "redirect" commands is using a container name that is already in use, OR a redirect is missing for a tablespace and the original location is still in use.
There is no error message when issuing the redirect and a totally misleading message when you try to "continue", but the file name has already been used.
To find which tablespace is causing the problem, look in the diagnostic log. There should be a message.
The conatiner may be in use by another database. This was the problem I had. The redirect commands were generated by a script, which missed them some tablespaces.
The "continue" will not work if it knows that a container is already in use - it is asking you for a missing redirect command.
I hope this helps.

[vijay2082]

Hi,
You need to provide path for the automatic storage tablespaces. This the reason why your restore is failing.How will the database figure out where to set automatic storage containers for the tablespace (0, 1, and 7 in youe case)

You will also see mesages related to container failed operation while doing a rollforward continue operation in the db2diag.log file.

ideally your restore command should be like one below
-- assuming that you have sufficient space on D: and that teh drive is present on the server

db2 "RESTORE DATABASE mydb FROM 'C:\' TAKEN AT 20080310154100 on ' D:\' dbpath on 'C:\' INTO mydb REDIRECT WITHOUT ROLLING FORWARD "


Ref: http://publib.boulder.ibm.com/infoce...c/r0001976.htm


Cheers, Vijay