Hi y'all,

I've searched, found and read many issues regarding this mather, but I haven't been able to find a solution yet. Perhaps this will help.

I've been trying to clone my production database into a (extra) test database. All are situated on the same instance on the same volume. I've used the Control Center 'wizard' to backup the production DB and restore as new into a new DB. The backup was created with no errors. But when trying to restore I've encountered two errors.

First I got error messg SQL0294N, saying: "The container is already in use". Fair enough, I understand I have to use the redirect option as well, as far as I understand to copy the existing tablespaces (which are used by the production DB) into new ones (which are to be used by the testDB). But when trying to redirect these containers, it gives me an "unknown system error", over and over again. This error occurs when trying to select the appropriate backup image in the Control Center wizard.

I've yet created several backup images, using Control Center or using a script, but none seem to give the right solution. I understand it is not an issue regarding the backup file.

Any ideas?

Im working on DB2 Enterprise Server Edition, version 8.2.
Extra information: Db2 V8.1.8.870, fixpack 8.

Thanks in advance

What is your OS ?

The following works on UNIX 5.2.0.0.:

"

restore db ebs use tsm taken at 20051014172247 into sumweb newlogpath '/db2aux/sumweb/' replace existing redirect

set tablespace containers for 0 using (path '/db2aux/sumweb/c0')
set tablespace containers for 1 using (path '/db2aux/sumweb/c1')
set tablespace containers for 2 using (path '/db2aux/sumweb/c2')
set tablespace containers for 3 using (DEVICE '/dev/rlsumweb01' 256000)

restore db ebs continue

"

Remeber to run it line by line

Hope this helps

My bad.. apparently forgot something.
The server is an Windows 2003, having Service Pack 1

I just tried a work around, by first creating the new table, followed by the command to fill it with the backup image (as generated by Command Editor and slightly edited by me).

RESTORE DATABASE DM_IDB user **** using **** FROM "D:\DB2 Backup\(DM_iDB) 2006-07-05" TAKEN AT 20060705084631 TO DM_KBD WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
SET TABLESPACE CONTAINERS FOR 0 USING (FILE "D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT" 25600);
SET TABLESPACE CONTAINERS FOR 1 USING (PATH "D:\IDB\NODE0000\DM_IDB\TEMPSPACE1");
SET TABLESPACE CONTAINERS FOR 2 USING (FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_1.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_0.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_2.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_3.DAT" 4194304);
SET TABLESPACE CONTAINERS FOR 3 USING (PATH "D:\IDB\NODE0000\SQL00002\SYSTOOLSPACE");
SET TABLESPACE CONTAINERS FOR 4 USING (PATH "D:\IDB\NODE0000\DM_IDB\SYSTOOLSTMPSPACE");
RESTORE DATABASE DM_IDB CONTINUE;

When I state 'TO DM_KBD' it generates the warning 2539, which says that it had to replace the existing DB. The result is I do have all the systables, but none of the other tables which are in the backup image.

When I state INTO DM_KBD (with or without the DB actually present) it just doesnt work.

Thanks for your help by the way!

RESTORE DATABASE DM_IDB user **** using **** FROM "D:\DB2 Backup\(DM_iDB) 2006-07-05" TAKEN AT 20060705084631 TO DM_KBD WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;

1 : the redirect is missing
2 : to d:\xxx\cc : the complete path

Thanks!

Since I'm using a Windows envirenment I've read that I should enter de drive as a path, inserting a directory structure would end in an error messg.
So I've inserted the drive as a path and inserted the redirect. It's now telling me the restore was succesfull, though with the famous warning:
Restore has detected that one or more table space containers are
inaccessible, or has set their state to 'storage must be defined'.
Watching the DB tells me that it has only got all the 94 systables, none of the user tables.

Also: I was in the assumption that when using INTO a path wasnt necessary. Just a DB alias. It would create one when the stated alias wasnt present. Trying this gives me the same error messg.

So another step closer (it's at least telling me the restore is succesfull.. although it isnt ), but not quite there yet..

the problem is when restoring a database into another db on different system or same system the restore wants to use the same path for the containers for tablespaces (not tables). as this path/file is used by original db or not existing in new server, you have to redirect the restore.
gather source info : db2 connect to sourcedb
db2 list tablespaces (returns the tablespaceid)
db2 list tablespace containers for id (repeat this for each ts/container
create redirect stmt for each path and restore the db
have a look in sql/command reference for restore and there is a sample of redirected restore.

Whow.. fast reaction.. Thnx

As you said: it will use the same tablespaces, which it can't. As far as my knowledge goes (and seeing the examples), my set statements are in order.

First I start the restore (I manualy created the destination DB called DM_KBD) and want to create it as a clone from DM_IDB.
Statement:

RESTORE DATABASE DM_IDB user *** using *** FROM "D:\DB2 Backup\(DM_iDB) 2006-07-05" TAKEN AT 20060705084631 TO "D:\DM_KBD"
REDIRECT WITHOUT PROMPTING ;

Then, I redirect the Tablespaces / containers (or at least trying to )
There are 4 tablespaces, which are individually called and replaced. They are placed on the D volume of the disk, and so will the new ones be. Do I have to select the destination path, or will it use the path specified in the restore statement?

SET TABLESPACE CONTAINERS FOR 0 REPLAY ROLLFORWARD CONTAINER OPERATIONS USING (FILE "D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT" 25600);
SET TABLESPACE CONTAINERS FOR 1 USING (PATH "D:\IDB\NODE0000\DM_IDB\TEMPSPACE1");
SET TABLESPACE CONTAINERS FOR 2 USING (FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_1.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_0.DAT" 4194304,
FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_2.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_3.DAT" 4194304);
SET TABLESPACE CONTAINERS FOR 3 USING (PATH "D:\IDB\NODE0000\SQL00001\SYSTOOLSPACE");
SET TABLESPACE CONTAINERS FOR 4 USING (PATH "D:\IDB\NODE0000\DM_IDB\SYSTOOLSTMPSPACE");

Of course, I end it all with the continue statement.
RESTORE DATABASE DM_IDB CONTINUE;

What am i missing here?

I must say, quite a challenge...

very simple
so what is the error in this case
the to in the restore command is only for the catalog destination
it should take the new path/file statements
are the directory existing ? I think you should create them
always indicate the errors if any - also have a look in db2diag.log

latest error was:

Restore has detected that one or more table space containers are
inaccessible, or has set their state to 'storage must be defined'.

It said the restore was succesful, but it didn't showed me all the tables. In fact, when I access the DB in Control Center, it says the restore is incomplete.
All the 'set commands' are called to be completed successfully.

I have created the DB and the path (D:\DM_KBD). I haven't created the full path to the locations of the needed containers. Should I define these too?

seems clear - user error
check in source all containers (( I mean all ))
list tablespaces show detail - check status
list tablespace containers for x
in target do the same
copy the output in a file from source and from restore - so it will be clear for others not sitting in front of your terminal.

Sorry, wasn't realising that I wasn't making myselve clear enough.

List of Tablespaces of my source DB: (I removed all the page info as it doesnt seem to be that important)

DM_IDB
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Number of containers = 4

Tablespace ID = 3
Name = SYSTOOLSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Number of containers = 1

Tablespace ID = 4
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Number of containers = 1

The destination DB isnt accessible because of
"a previous restore is incomplete."

and what about the list tablespace containers for x (for each tablespace)
after this I leave work and go home...

No worries, I appreciate all the help I can get. I'll be online tomorrow again
But adding to answer your request:

Tablespace Containers for Tablespace 0
Container ID = 0
Name = D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT
Type = File

Tablespace Containers for Tablespace 1
Container ID = 0
Name = D:\IDB\NODE0000\DM_IDB\TEMPSPACE1
Type = Path

Tablespace Containers for Tablespace 2
Container ID = 0
Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_1.DAT
Type = File

Container ID = 1
Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_0.DAT
Type = File

Container ID = 2
Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_2.DAT
Type = File

Container ID = 3
Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_3.DAT
Type = File

Tablespace Containers for Tablespace 3
Container ID = 0
Name = D:\IDB\NODE0000\SQL00001\SYSTOOLSPACE
Type = Path

Tablespace Containers for Tablespace 4
Container ID = 0
Name = D:\IDB\NODE0000\DM_IDB\SYSTOOLSTMPSPACE
Type = Path

answering from home
D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT
D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT

these are both the same source container and set ablespace container command

the idea is to redirect old containers to new direction.....and not the same
that is the reason why it is in use... also for the other

I did not read all the responses, but I am not a DBA and hit a similar problem. Instead of selecting the backup file, you must select the backup directory the file is in...

Hope this is what you are looking for....