DB2 – Redirect Restore

16 October,2012 by Tom Collins

 Question: How do I relocate an existing DB2 database? I have a database on a path such as “/db2inst/db/”   and would like to move the database to “/db2inst/dbnew/”.

 Answer: One option is to backup and restore with a DB2 Redirect Restore

  Second option – use db2relocatedb

I prefer the first option  - DB2 Redirect Restore as it forces the DBA to take more precautionary steps. Although relocatedb should follow the same levels of caution such as backups.  In this response, I’ll focus on Redirect Restore and leave relocate db for another post.

 One of things I like about the Redirect Restore is DB2 will generate the script , a few modifications are required , execute the script and Redirect Restore completed!

Create a Restore Redirect script

Step 1- Ensure all relevant database server files are backed up. Usually I force a backup to tape. If it’s a VM consider a VM snapshot for  extra rollback flexibility.

Read more on DB2 - Backup all databases on Primary Server - DBA DB2

Step 2 – For the Restore Redirect : Confirm a FULL BACKUP is available and the FULL BACKUP was completed by DB2 backup facility

Step 3 -  Confirm you can connect to this database. If crash recovery is required complete first.


Step 4 - Is the database on multiple partitions and no target database exists – then the script must be executed partitions by partition. I prefer to create a target dummy database which runs the script over all the partitions

Step 5 –  Using the CLP – here is an example of how to generate the script


db2 restore db MYDB from /db2ins/backups taken at 20121126133630  redirect generate script mydb.clp


Step 6 – Open the redirected restore script in a text editor to make any modifications necessary. You can modify:

Login details – username\password

Restore options  - ON,DBPATH ON, INTO,NEWLOGPATH etc

Automatic storage paths

Container layout and paths

For example:

 ON = Indicates the file system where the database will be created

NEWLOGPATH = Indicates the log files location to be used for the new database


Step 6 – Execute the script


  db2 -tvf mydb.clp


Step 7 – Confirm  directory details


db2 list db directory show detail
db2 list db directory on /db2wbm/home

Related Posts on Restore DB2 database

DB2 – Restore database from a ONLINE backup

DB2 - SQL1051N while Redirected Restore

Author: Rambler(http://www.dba-db2.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on DB2 – Redirect Restore

Comments are moderated, and will not appear until the author has approved them.

dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright