DB2 – Redirect Restore

 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