DB2 Restore a 9.7 database to new server with 10.5

Jim Youmans
Jim Youmans used Ask the Experts™
DB2 9.7 and 10.5 on Windows 2012 Server
I am trying to restore a 9.7 database to a new server with new directories that is running 10.5 and am about to pull my hair out.  I have read the IBM references about restore with redirect and they do not make much sense to me and of course none have good examples for Windows.  

Here is the example given
   
db2 restore db sample redirect without prompting
   SQL1277W A redirected restore operation is being performed. 
   During a table space restore, only table spaces being restored can 
   have their paths reconfigured. During a database restore, storage 
   group storage paths and DMS table space containers can be reconfigured. 

   DB20000I The RESTORE DATABASE command completed successfully.

   db2 set tablespace containers for 2 using (path 'userspace1.0', path    
   'userspace1.1')
   DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

   db2 restore db sample continue
   DB20000I The RESTORE DATABASE command completed successfully.

Open in new window

Nowhere does this specify the backup file for the database and it does not explain what the set tablespace for 2 means?  Shouldn't I have to give it the tablespace names?  
On the server it is running on now the data is on D:\db2\data\... and the logs are on L:\db2\logs.
On the new server I want them all on the X: drive.
Then I need to run the upgrade for 10.5.
This is a small database and not used much but it is killing me to get this moved over.
Can anyone point me at an example of how to do this (and please don't reference IBM knowledge pages, I have read them and they are not helpful).

Thank you!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Warehouse / Database Architect
Hi Jim,

Run LIST TABLESPACE CONTAINERS (on the original database).

They will be numbered, and that number is the value to use in SET TABLESPACE CONTAINERS.

  set tablespace containers for 5 using    (file 'f:\ts3con1' 20000, file 'f:\ts3con2' 20000)

The original database' tablespace number is on f:\tscon1.  It will be on f:\ts3con2 in the new database.


Just substitute your tablespace number(s) and the paths that pertain to your environment.

You'll need/want to SET TABLESPACE CONTAINERS statement for each tablespace that will be moved.

Kent
Jim YoumansSr Database Administrator

Author

Commented:
That helps a lot.  Do I have to have the directories created or will the set command do that for me?

Thank you !!!!!
Kent OlsenData Warehouse / Database Architect
I don't know.  But I suspect that DB2 will make them as needed.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Jim YoumansSr Database Administrator

Author

Commented:
You are a god send.  Thank  you!
Kent OlsenData Warehouse / Database Architect
Hi Jim.

I told you wrong.  Dang it.  (I was in a hurry and tried to answer quickly.)

  set tablespace containers for 5 using    (file 'f:\ts3con1' 20000)
  set tablespace containers for 5 using    (file 'f:\ts3con1' 20000, file 'f:\ts3con2' 20000)
  set tablespace containers for 5 using    (file 'f:\ts3con1' 20000, file 'f:\ts3con2' 20000, file 'f:\ts3con3' 20000)

SET TABLESPACE doesn't care about the old location.  The parameter list is the location(s) on the new server where the tables will reside.  It could be one location or several.

Kent
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Hi,

Here is a link to look at and also if your database has the automatic storage enabled  then you should read this link

Regards,
    Tomas Helgi
Kent OlsenData Warehouse / Database Architect
I take it you got it to work despite me missing that left turn at Albuquerque?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial