DB2 Basics: Cloning DB2 Databases Using Redirected Restore

This article gives you the basics of cloning a DB2 for Linux, UNIX, or Windows database.

Roman Melnyk (roman_b_melnyk@hotmail.com), Staff Information Development, IBM Toronto

Photo: Roman B. MelnykRoman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than eleven years at IBM, Roman has written and edited numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. Roman recently edited Apache Derby -- Off to the Races.



31 October 2002

Also available in

© 2002 International Business Machines Corporation. All rights reserved.

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

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13178
ArticleTitle=DB2 Basics: Cloning DB2 Databases Using Redirected Restore
publish-date=10312002