Redirect Restore 2

[june54]

Iam a newbie ... We have 2 system running SAP4.6c and DB2 I want to restore DB2 from my Development box to my Sandbox . Anyone got scripts or procedure on how that is done ..Or point me to a website ......

 

[ifxtechie]

You need to use Redirected restore.
This is what I got from another usergroup [lazydba].This may be helpful.

==========================================
This article is written for IBM® DB2® Universal DatabaseTM for Linux, UNIX®, and Windows®

A database restore operation uses a database backup image to recreate a database. If you want to clone a database from one machine to another, the simplest way is to restore the database from a backup image. The file system paths used by the database are also contained within the backup image. Therefore, unless the file systems and the physical devices that are referenced by the database on the source system are set up exactly the same way on the target system, you will need to perform a redirected restore operation.

A redirected restore operation consists of a two-step database restore process with an intervening table space container definition step:

1.. Issue the RESTORE DATABASE command with the REDIRECT option.
2.. Use the SET TABLESPACE CONTAINERS command to define table space containers for the restored database (DB2 needs to know where you want the table spaces to reside on the target system.)
3.. Issue the RESTORE DATABASE command again, this time specifying the CONTINUE option.
It is important to remember that the entire redirected restore operation must be invoked from the same session; otherwise, SQL0900N is returned and the restore operation fails. One way to ensure that this does not happen is to create and run a script that contains all three parts of the redirected restore procedure.

The following example shows you exactly how to successfully perform a redirected restore operation on Windows operating systems or on UNIX-based systems. Instead of restoring a backup image from another server, I will show you how to restore a backup image from a different instance. The principle is the same, and the details are identical.

The source instance for the Windows example is called PROD. The target instance is called MYINST. The existing database on PROD that is going to be restored (created) on MYINST is called MOVIES. The MOVIES database is located on drive D.

The source instance for the UNIX example is called prod. The target instance is called myinst.

1.. Create a full database backup image of the MOVIES database. Before issuing the BACKUP DATABASE command on Windows, ensure that the current instance is PROD:
set DB2INSTANCE=PROD
db2start
db2 backup db movies


The timestamp for this image is 20030909143225. On Windows, the image is located in D:\MOVIES.0\PROD\NODE0000\CATN0000\20030909. DB2 creates this subdirectory tree off the directory from which the BACKUP DATABASE command is issued. In the UNIX example, the image is located in /home/prod.

On Windows, the subdirectory names represent the following:


<database_alias.backup_type>\<instance_name>\<node_number>\
<catalog_node_number>\<date>



Inside this subdirectory tree, there will be a file (143225.0) representing the actual backup image. The file name represents the time at which the backup was taken.

Note: If you were relocating the backup image to another machine, create the above path on the target machine (substituting the appropriate target instance name for PROD) and copy the backup image (in this case 143225.0) to that directory. You can also use compression tools to package the backup image while maintaining the directory tree for transport.

In the UNIX example, the actual backup image is named as follows:


MOVIES.0.prod.NODE0000.CATN0000.20030909143225.001


The components of that name represent the following:


<database_alias>.<backup_type>.<instance_name>.<node_number>.
<catalog_node_number>.<timestamp>.<sequence_number>


2.. On Windows, start MYINST and then invoke the first step of a redirected restore operation.

set DB2INSTANCE=MYINST
db2start
db2 restore db movies from d taken at 20030909143225 redirect


The FROM parameter must be specified if the RESTORE DATABASE command is invoked from a directory other than the one that contains the backup image.

On the UNIX-based system, issue the following command while logged in as myinst:


db2 restore db movies from /home/prod taken at 20030909143225 redirect


3.. Define new table space containers for the three default table spaces that are associated with the restored database (SYSCATSPACE, TEMPSPACE1, and USERSPACE1). On Windows, issue:

db2 set tablespace containers for 0 using (path 'd:\tsc_movies\ts0con1')
db2 set tablespace containers for 1 using (path 'd:\tsc_movies\ts1con1')
db2 set tablespace containers for 2 using (path 'd:\tsc_movies\ts2con1')


On the UNIX-based system, issue:


db2 "set tablespace containers for 0 using (path 'ts0con1')"
db2 "set tablespace containers for 1 using (path 'ts1con1')"
db2 "set tablespace containers for 2 using (path 'ts2con1')"


You can use the LIST TABLESPACES SHOW DETAIL command to get information about all of the table spaces in the source database; this will help you ensure that containers have been set for all of the table spaces in the target database.

4.. Complete the redirected restore operation:

db2 restore db movies continue


5.. Verify that the restored database has been cataloged properly, and that the table space containers that you have defined for it are in fact associated with the restored database:

db2 list db directory
db2 connect to movies
db2 list tablespace containers for 0
db2 list tablespace containers for 1
db2 list tablespace containers for 2
db2 connect reset
db2 terminate
db2stop

========================================================
Good luck

 

[june54]

Thanks ifxtechie I will take a shot at it .........