© 2002 International Business Machines Corporation. All rights reserved.

Introduction

If you ever need to copy an IBM® DB2® database and its data from one environment to another, a DB2 redirected restore is the fastest, most thorough way to accomplish your goal. And, if your database has a lot of table spaces, then you may be interested in a semi-automated approach using scripts that is faster, more reliable and easier to use than building a redirected restore and running it by hand.

This article describes the script method for accomplishing a DB2 redirected restore, offers examples of KORN shell scripts and a Perl script that I developed to semi-automate a DB2 redirected restore process, and tells you, step-by-step, how to use them.

Back to top

Pros and cons

A DB2 redirected restore is the fastest way to copy an entire database along with its data from one environment to another. Using scripts along with the search and replace features of a GUI or text-based editor can speed up the process to build the redirected restore statements you will need. The script method will be more reliable and easier to do than performing the process by hand.

However, a DB2 redirected restore cannot be used to move data from one operating system to another. For example, you may not restore a DB2 backup taken on a server running the AIX® operating system to a server running the z/OS^TM, OS/400® or Windows® operating systems.

Environment

The scripts in this article were tested on a server running the AIX operating system and DB2 versions 8, 7 and 6. The KORN shell and Perl scripts should work on any server that has a UNIX®-like operating system that supports these types of scripts. (Linux and Linux/390 come to mind.) These scripts are designed to run in a native UNIX environment and are not intended to run under the DB2 Script Center Client Window.

Before you get started, a couple of words of caution:

• Practice running the scripts with a small test database before trying this with a large database.
• Back up your target database environment before you attempt to restore into it if you have any settings or data you want to preserve.

Back to top

The scripts

Back to top

Setting up the environment

Set these scripts up to run under a DB2 instance ID. They can be set up to run under any ID that has the correct level of authority to execute various DB2 commands. The directory paths and names used below may be changed. Here are the steps you need to take to prepare to use the scripts:

1. Logon to the source server with an ID that has the authority to back up the source DB2 database. (Typically a DB2 instance ID.) NOTE: You need to repeat these steps on the target server with an ID that has the authority to restore into the target DB2 database.
2. Edit your .profile file and add the three lines below to the bottom of the file for your ID in your $HOME directory path. See the profile.ksh file attached with this article for an example.

   export PATH=$PATH/$HOME/bin: . dbaenv.ksh >/dev/null echo "DBA environment set up for the $ScriptDBName database."

   
   
3. Execute the commands below from your home directory to create a script directory (called bin) and an output directory.

   mkdir bin mkdir output

   
   
4. Change directories to the script directory. cd bin
5. Copy, download, FTP or transfer the dbaenv.ksh and db2*.* scripts attached with this article to the new bin directory.
6. Edit the dbaenv.ksh script file and follow the instructions in the comments. The key item you need to change is the databaseName to your source DB2 database name. You may also want to change the email address and the path names if you use something other than what is documented in this article.
7. Make the scripts executable. chmod 755 *
8. Change directories to your home directory. cd
9. Enter the command . .profile

   This command may need to be different depending on how your UNIX administrators set up your server. If it does not work correctly, then try to open a new window and logon with the ID again.

10. Repeat steps one through nine above on the target server and ID.

Back to top

Running the scripts

These scripts assume that you have the KORN script program installed in /bin/ksh and the Perl script program installed in /usr/bin/perl. Also, this process assumes that you will manually move any user-defined functions or stored procedures that are stored outside of the database if you need them in the target environment because these objects are not included on DB2 backups.

You can restore a DB2 backup and redirect it into another DB2 database that is running under the same type of operating system. The DB2 command statements required to perform a redirected restore are:

1. One DB2 RESTORE DATABASE INTO command
2. One SET TABLESPACE CONTAINERS command for each table space in the database
3. One DB2 RESTORE DATABASE CONTINUE command.

You may also need to restore DB2 log files and issue a DB2 ROLLFORWARD DATABASE command against the target database if you are restoring an on-line backup.

The db2redirected.restore.ksh script below will generate all of the commands you need in an SQL file, and it will generate a script in a ksh file that can be used to perform the DB2 redirected restore. You will have to edit the generated ksh script before it will run. This is a safety feature to protect you from yourself.

You may have to edit the SQL file and use search and replace commands to change SET TABLESPACE CONTAINERS statements if the file containers need to be redirected to different paths on the target server. You must run the db2redirected.restore.ksh script against the source database that was backed up. You must run the generated ksh script against the target database environment that you want to restore into.

NOTE: If the source database backup was taken a long time ago and you run this process against the current DB2 catalogs for that database, either or both of two problems might occur:

• First, the script might generate SET TABLESPACE CONTAINERS statements for new table spaces that did not exist in the database when the original backup was taken. These will result in warning messages when you run the DB2 redirected restore into the target database.
• Second, the script will not generate SET TABLESPACE CONTAINERS commands for old table spaces that existed in the database when the original backup was taken but that have since been dropped. These missing statements will result in error messages when you run the DB2 redirected restore into the target database. You would need to manually add the missing statements for all table spaces dropped since the original backup was taken. If the source database matches the backup then no warning messages or errors will occur that are caused by using this approach.

RECOMMENDATION: To avoid either of the two problems mentioned in the note above, schedule the db2redirected.restore.ksh script with the required parameters to run at the same time as your source DB2 database backups, and keep the generated ksh and SQL output files backed up. Then, if you ever need to run a redirected restore of a database from an old DB2 backup, you will have the table space container statements that match the DB2 catalog at the time that the backup was taken.

Back to top

Detailed steps

If you've followed all the preparatory steps above, you should be ready to go:

1. Logon to the source server and backup the source DB2 database. You may perform an off-line or on-line backup to TSM (Tivoli® Storage Manager), disk or tape. The source backup must be available and visible on the target server. If you run an on-line backup you will need a copy of the DB2 log files from the time of the backup so that you may restore and roll forward the target DB2 environment. If you already have a backup you may go directly to the next step.
2. Run the db2redirected.restore.ksh script. There are three required parameters and four optional parameters. There are comments in the script concerning all seven parameters. Here are some examples of how to run this script:

Example 1 - If you run the script without any parameters, it will display comments telling you what values are required.

db2redirected.restore.ksh                                 

The Backup Database Name must be passed to this script as the first parameter.
Backup TimeStamp CCYYMMDDHHMMSS must be passed to this script as the second parameter.
The Target Database Name must be passed to this script as the third parameter.

Example 2 - If you run the script against the db2prod database under the db2prod ID using the parameters, db2prod 20020901030000 db2test, then a ksh script and SQL file will be generated that can be used to restore a db2prod backup into a db2test database.

db2redirected.restore.ksh db2prod 20020603020309 db2test

Example 3 - If you run the script against the db2prod database under the db2prod ID using the parameters, db2prod 20020901030000 db2test production test, then a ksh script and sql file will be generated that can be used to restore a db2prod backup into a db2test database. Also, every occurrence of the word "production" in the generated sql file will have been changed to the word "test."

 
db2redirected.restore.ksh db2prod 20020603020309  
  db2test production test 

In all of the examples above, the script will display the comments like those shown in Listing 1 below which tell you where the generated ksh and SQL output files are located. These comments provide instructions concerning the steps you must take and the changes you may need to make in order to complete the DB2 redirected restore process.

   
db2redirected.restore.ksh Vers: 09/28/2002 on  ServerName on  
     Mon Jun 10 08:31:22 CDT 2002 ended. 
Highest Return Code =  0 
Output     located in /db2/db2prod/output/  
     db2redirected.restore.ksh.db2prod.into.db2test.20020928.out 
SQL        located in /db2/db2prod/output/ 
db2redirected.restore.ksh.db2prod.into.db2test.20020928.out.sql 
New script located in /db2/db2prod/output/ 
db2redirected.restore.ksh.db2prod.into.db2test.20020928.out.ksh 
============================================================== 
= > Change to the output directory. 
cd /db2/db2prod/output 
= > Edit the generated sql file and use search and replace to 
change directory paths to support the target environment if needed. 
Change the restore from tsm to restore from disk or tape if your 
source DB2 backup is on one of these environments. Change the 
restore from an offline to an online if needed. 
vi db2redirected.restore.ksh.db2prod.into.db2test.20020928.out.sql 
= > Edit the generated ksh file and comment out the exit statement. 
vi db2redirected.restore.ksh.db2prod.into.db2test.20020928.out.ksh 
= > Logon to the target environment. 
= > Create the target DB2 database if it does not already exist. 
= > Copy the generated ksh and sql files from the source  
  environment. 
= > chmod 755 *.ksh to allow the generated script to be executed. 
= > Run the generated script.  
  db2redirected.restore.ksh.db2prod.into.db2test.20020928.out.ksh  
============================================================== 

Follow the instructions in the comments above. If you see any errors while running the generated redirected restore script you may need to fix these and re-run. If for any reason you need to stop the initial restore process and start over, you can stop the redirected restore with the following command:

db2 restore database db2test abort

You also may need to drop the target database and re-create it before a re-run will work.

WARNING: If your redirected restore fails due to a space error and the log files have filled up, issuing a df -k command may not show the problem directly. The reason for this might be that DB2 releases any secondary logs when it stops the restore process and rolls back the work it attempted to do, thus freeing up the space that was filled. DB2 does not use an overridden logpath from the configuration of the target database but instead uses the default log path when performing a redirected restore. If the default logpath does not have enough space to support the redirected restore, contact your system administrators and ask them to add more free space to the file system under this path.

An alternative to this approach would be to add the newlogpath statement to the restore command in the generated SQL file so long as the new log path has a file system with enough space to support the restore.

If you restored an online backup, then you need to copy the backup of the log files that match the source online backup to the target environment and issue the command:

db2 rollforward database db2test to end of logs and stop

After the DB2 redirected restore completes, check to see that you can connect to the target database:

db2 connect to db2test

If you can connect to the database, check to see that you can select rows from a table. For example, you might issue the command:

db2 "select * from syscat.tables"

If you need to change the target environment's DB2 log path you may issue the following command:

db2 update db cfg for db2test using newlogpath logpath

(where logpath is the path at the target that you want to designate for the logs).

If a new instance was created for the new target database, then you may need to set up the environment to support TCP/IP so that people can connect to the new database remotely with client software like the DB2 Control Center tools.

Ask your operating system administrator to add two new entries to the /etc/services file similar to the ones shown below. The db2test_com 50008 and db2test_int 50009 entries are the service name and port number that people will need in order to configure client software so that it can connect to the DB2 instance.

 
db2test_com		50008/tcp 
db2test_int		50009/tcp

When the AIX administrator has completed the /etc/services entries then you need to execute the two commands shown below.

 
db2set DB2COMM=tcpip 
db2 update dbm cfg using svcename db2test_com 

The first command sets the DB2COMM registry variable to support TCP/IP communications and the second command configures the database management service name to match the /etc/services communications service name.

Issue a db2stop and db2start to stop and restart the instance, and you should now be able to configure workstation client software and connect remotely to the new DB2 instance and to the database that was restored from the backup.

Back to top

Postscript - Using TSM

Several people have contacted me to ask for help in configuring Tivoli Storage Manager (TSM) to support DB2 redirected restores across two servers. The following instructions will help you run a redirected restore of a DB2 database on a target server using the TSM backups created from a source server.

1. Log on to the target server and create a DB2 instance and a DB2 database that you want to restore the backup into.
2. Update the database configuration using the following commands:
   

   db2 update db cfg for <database> using ADSM_MGMTCLASS <mgmtclass> db2 update db cfg for <database> using ADSM_OWNER <instance owner id> db2 update db cfg for <database> using ADSM_NODENAME <api client nodename> db2 update db cfg for <database> using ADSM_PASSWORD <api client node password>

   
   

   (The mgmtclass, instance owner, nodename and password are from the source server's configuration.)

3. If a TSM client configuration is already set up on the target server, modify the system options file to disable the PASSWORDAccess Generate option.
   

   vi /usr/tivoli/tsm/client/api/bin/dsm.sys

   
   

   Change "PASSWORDAccess Generate" to "PASSWORDAccess Prompt". Also change the system options file NODENAME option to the nodename for the source server.

4. Stop and restart the instance using db2stop and db2start on the target server to invoke the changes.
5. Run the redirected restore.
6. After the completion of the restore, reverse the changes to the system options file and then remove the database configuration parameters as follows:
   

   db2 update db cfg for <database> using ADSM_MGMTCLASS NULL db2 update db cfg for <database> using ADSM_OWNER NULL db2 update db cfg for <database> using ADSM_NODENAME NULL db2 update db cfg for <database> using ADSM_PASSWORD NULL

   
   

   These parameters should only be used to override the default TSM client configuration, and setting them to NULL will result in DB2 using the target server TSM settings if the new target DB2 database is ever backed up.

7. Stop and restart the instance using db2stop and db2start on the target server to invoke the changes.

Back to top

Conclusion

Moving a DB2 database and its data from one environment to another using a DB2 redirected restore is a complex process. This article and these scripts attempt to make this process a little easier. Like most complex tasks on computers, once you get all of this set up and working for one environment it will be much easier to use for other environments. If you ever have the requirement to move a database containing many table spaces and containers, you will find that this approach will really help.

If you have any questions, feel free to contact me, Mark Mulligan, at markmull45@hotmail.com and I will be glad to help in any way that I am able. If you or your company would like to make a contribution toward the advanced education of my children, you may contact me using this approach as well.

Back to top

Download

Name	Size	Download method
restorescripts.zip	11KB	FTP | HTTP

Information about download methods

About the author

Mark Mulligan is a DBA living in the Dallas-Fort Worth area of Texas. He has a Bachelor of Science Degree in Business Administration from Merrimack College in North Andover, Massachusetts, and 25 years of experience working with computer software design, development, maintenance, enhancement and support. He has 10 years of experience working with DB2 on servers running UNIX-like operating systems and 10 years of experience working with DB2 on mainframes. Mark can be contacted at mark.mulligan.sr@sbcglobal.net.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Pros and cons
• The scripts
• Setting up the environment
• Running the scripts
• Detailed steps
• Postscript - Using TSM
• Conclusion
• Download
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers