Is there a Redbook or other publication that has step by step directions on moving a database from one server to another?

We are looking to install a new server and would like to move our major database off of the current server and onto the new one.....

It will have a new host name and IP address. Not sure if we will need to update this information on all the servers that talk to the database....

Thanks in advance....

The easiest way is to a full off line backup and then restore on the new server.

So all I would have to do is run the Full backup, shutdown the database.... drop the database and instance on the current server, create the instance on the new server using the same name, create the database on the new server using the same name, do a full restore and I should be back in business....

I am not sure how databases talk to each other. In oracle, it had a listener that would "listen" for requests on that server. Does DB2 care what server it is on or does the requests go by database name only?

Thanks......

First what I am putting is for non-partitioned database.also the new server and the old server(instance) should have same fixpak.
use db2level and apply the fixpaks before doing the restore on the new server

There are some points that need to be understand.

1. new instance will be created and will be running at the new machine. name makes no difference.

2. There is no shutdown command like in oracle.force all the applications(consider the rollback issue) and use the backup in offline mode.You can also do the online backup but it is tricky.

3. Save the dbm and db cfg parameters.

4. Try to have the same directory structue on both machines.
if there is directory contention issue then you need to do the redirect restore.

5. No need to drop the instance at the current machine.Infact , I usually keep it other wise tar the sqllib structure.

6. The restore command with the same name as the image infact implicitly calls the create database command and uses the image to extract the structure and do the other stuff.it also restore the db config file from the backup.sets the authentication as in the image file and you can change the newlogpath with the options in the restore command.

7. the restore command also restores the recovery history file.
can be checked after the restory with list history command.

8. if the current database in rollforward mode(i-e logretain or userexit in on) , the database after the restore will be in rollforward pending state, it needs to be stopped/complete. using rollforward comand

I usually use the tail -f db2diag.log file during the restore operation to know what is actually going on in another terminal session.



Regards,

mujeeb

You may want to use redirected restore if the filesystem names are different ...

Visit, www7b.boulder.ibm.com/dmdd and search for the terms 'db2 redirected restore' ...

Copy the dbm and db2set values from the old system to the new one ...
Database config parameters (except logpath) are automatically copied over when you do a restore as a new database ... Before you start the database, set the logpath using

db2 update db cfg for <dbname> using newlogpath <value>

The only other thing you may want to configure is the SVCENAME dbm parameter ... This identifies the port number the instance listens at ... If the value is a number, then it is the port ...If it is alphanumeric, then put an entry for the port in /etc/services file ....

To get network clients talk to the new database, you will have to recatalog node and the database on the client side ...

Do not drop the original database,instance and filesystems ... Leave it for a day or two until you are really comfortable with the new system ..

If you are using non-SQL Stored Procedures or functions, you should copy the relevant modules from sqllib/function directory ... I have heard that large SQL Procedures sometimes give problems ... You may have to drop and recreate them (you need a C compiler on the server to do this) or use the GET_ROUTINE_SAR and PUT_ROUTINE_SAR(provided both the OS are on the same level) ...

If the new database instance is at a different fixlevel, remember to bind the package list db2ubind.lst and db2cli.lst from the database server and from each OS-fixlevel combination of clients you use ...


To keep the downtime of the database to minimum, you can consider using online backup, the restore followed by rollling forward using shipped logs ... But certain operations(like non RECOVERABLE Load, ALTER TABLESPACE, Activating not logged intially etc) may make shipped logs useless ... If this method interests you, search for the term 'log shipping' in the developer domain ...

HTH

Sathyaram

I have used Restore Redirect when refreshing a development database with the production. We did this every Monday from the weekends online backup. Had to redirect since we were restoring DMS tablespaces and they where on different filesystems names........

Thanks for all the help.

Think my biggest problem will be convincing the application team that they will need to change the hostname on all thier clients......

A short cut is to give the old server name as the alias to the new server in your DNS System ..

Not an elegant method, I know

cheers
Sathyaram

They already suggested that..... But we are moving our largest database to a new.... Faster, More Memory, More CPU server and the old server will be getting a couple of production databases currently sitting on a development server....

So I can not use the old IP address or Hostname......

You can create the profile at the new server in a file by using db2cfexp command and send that to each client.
Clients can import it using db2fimp.

If you are using JDBC in middle-tier then you can have the data source as JDNI, so no need to change anything at the client.