 | Level: Introductory Roman Melnyk (roman_b_melnyk@hotmail.com), Staff Information Development, IBM Toronto
31 Oct 2002 This article gives you the basics of cloning a DB2 for Linux, UNIX, or Windows database.
© 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:
- Issue the RESTORE DATABASE command with the REDIRECT option.
- 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.)
- 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.
- 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>
|
- 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
|
- 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.
- Complete the redirected restore operation:
db2 restore db movies continue
|
- 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
About the author | 
| | Roman 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. |
Rate this page
| |
