© 2002 International Business Machines Corporation. All rights reserved.

Introduction

Moving a database from one system to another can be a complex job, especially if the source database requires 24-hour, 7-days-a-week availablility. Wouldn't it be nice if you could move all of the database objects in one clean operation? No setting constraints, creating indexes, tablespaces, or bufferpools? Using DB2's backup and restore utility to move a database is the easiest way, bar none.

This article discusses how you can fully automate the movement of a recoverable production database to a development machine in order to perform tests on the most current production data. To do this, we will take a recoverable production database and perform an online backup using a script via DB2's Script Center GUI tool. A script on the development machine will then be executed to perform a redirected restore followed by a rollforward to apply the log files. More details will follow for using the REDIRECT option in our restore and also why the roll forward operation is necessary.

Back to top

Advantages

There are different ways to move a database from one system to another. The db2move utility allows movement of tables via the export and load/import APIs. This is a great method if you need to move a database across heterogeneous platforms. However, it does not move other database objects such as triggers, sequences, tablespaces, bufferpools, and indexes. These objects would have to be re-created in a separate operation with the aid of the db2look utility. And for LOB tables, there is a limitation of 26000 rows per table.

Performing a split mirror is another way to move a database. Although this is also a great way to clone a database, it is more complicated and requires a storage vendor's facilities to access the split mirror. In addition, you must have the exact directory paths on the development machine for the database directory, the tablespace containers, and log files as they would appear on the production system. Many times, the necessary drive letters are not even available on development machines to reproduce the pathing required. Also, the DB2 server's instance must be the same name.

DB2's backup and restore utility moves all objects in the database, and, you can specify alternate paths to for your tablespace containers to reside. This allows for maximum flexibility when moving your database.

Note: Some objects, such as user defined functions (UDFs) and stored procedures that are stored externally, will have to me moved separately since they are not included in the backup image.

Back to top

Environment

In the example provided later, we will be using two Windows® machines that are mapped to each other via a local network (it is required that both machines have the same OS platform). In our example, each machine has DB2 version 7.2 - Enterprise Edition, with fixpak 6 applied. The Control Center is included by default during installation of DB2 and is required for running the Script Center.

If you plan to use a UNIX® environment, the same rules apply. Both machines must have the same OS platform (an exception is a restore between SunOS and HP) and must have filesystems mounted via the network during the backup/restore procedure. Additionally, the Control Center component is not installed on UNIX by default. So it will be necessary to install it before using the scheduler (an alternative would be to execute the scripts using a cron job).

Back to top

LOGRETAIN and USEREXIT

It is important to recognize that we are performing an online backup of a recoverable production database. Because the database is recoverable, it is assumed that the necessary precautions were taken in the event that the database becomes inoperable. Specifically, the LOGRETAIN or USEREXIT database configuration parameter is turned ON so that rollforward recovery can take place. To determine if your production database is enabled for recovery, you can perform the following command on the production DB2 server to verify:

 
db2 "get database configuration for <database alias>" 

You will then need to look for either the LOGRETAIN or USEREXIT parameter to be ON. If neither of these parameters is turned on, you may want to consider a high-availability strategy using IBM's Data Recovery and High Availability Guide and Reference.

Note: Although it is possible to move a non-recoverable database from a production to a development environment, it will not be discussed in this article.

Back to top

Before creating the scripts

As a precautionary note, it is highly recommended to practice the backup and restore scripts using a test database for both the production and development machine before implementing a large scale backup and restore. With that said, there are some initial directories that will need to be created before executing the scripts. You will need the following directories:

Production Machine:

\scripts (holds the script that will perform the online database back-up)

Development Machine:

\backups (holds the backed-up image of the production database)

\scripts (holds the script to restore the database)

\tablespaces (holds the containers for the tablespaces)

Back to top

Creating the backup script (production machine)

Below are the commands to be executed in the backup_proddb.cmd script along with explanations:

1. rmdir /S /Q G:\backups\PRODDB.0 This command removes any database images from the development \backups directory. If more than two database images from the same database are located in the same directory, a timestamp value will have to be specified. We cannot do that easily in an automated script, so we delete the database image.
   
   Note: It is important to dedicate the \backups directory for this database only in order to prevent the accidental deletion of other database images. You may consider using a directory name that is unique for this operation. Remember that this a development directory, not a production directory.
2. db2cmd "db2 backup db proddb online to G:\backups"
   
   This command executes the DB2 command window session, runs the database backup command and saves the image on the development machine. Since it is assumed that the database must be running 24 hours a day, seven days a week, an online backup is necessary. In the instance above, the G: drive letter is pointing to the C: drive on the development machine.
   
   Note: It is required for you to have at least one full offline database backup of the production database before you can execute an online backup.

Back to top

Creating the restore scripts (development machine)

There are two scripts for the restore. The first script will call a DB2 command window session and execute the second script which, in turn, will execute multiple DB2 and OS commands.

Script 1

\scripts\restore_devdb.cmd ...executes the following... db2cmd restore_devdb_2.cmd

Script 2

\scripts\restore_devdb_2.cmd ...executes the following...

1. db2 "force application all"
   
   Before dropping the old database on the development machine, it is necessary to force any applications off the DB2 instance where the database resides. Currently, there is no automated way of forcing all applications off a single database. If there are any other services running that are dependent on the DB2 instance, it may be necessary to stop those services first before stopping the DB2 instance.
2. db2 "restore db proddb from C:\backups into devdb redirect without prompting"
   
   The restore will now create the new database devdb and indicate a redirect to allow containers to be specified for the tablespaces. Do not be worried when receiving the message SQL1277N. This is only a warning that containers can be defined for the tablespaces (see Step 3).
   
   The above REDIRECT option allows us to specify alternate tablespace container paths from the production system. If REDIRECT is not specified, you must create the same database <drive letter>\<instance name>\NODE0000 as is shown on the production system. And if the production tablespace containers are located in a different path, they too must have paths set up for them on the development machine.

3. db2 "set tablespace containers for 0 using (path "C:\tablespaces\tbspc0")" db2 "set tablespace containers for 1 using (path "C:\tablespaces\tbspc1")" db2 "set tablespace containers for 2 using (path "C:\tablespaces\tbspc2")"

   
   
   
   In order to automate the restore process, we will redirect all tablespaces associated with production database by setting new container paths on the development machine. To determine what tablespaces reside in the database, you must first connect to the production database and execute the following command at the DB2 command window:
   
   

   db2 "list tablespaces show detail"

   
   
   
   The output will look something like this:
   
   Sample 1: Tablespaces for Current Database
   
   

   Tablespace ID = 0 Name = SYSCATSPACE Type = System managed space Contents = Any data State = 0x0000 Detailed explanation: Normal Tablespace ID = 1 Name = TEMPSPACE1 Type = System managed space Contents = System Temporary data State = 0x0000 Detailed explanation: Normal Tablespace ID = 2 Name = USERSPACE1 Type = System managed space Contents = Any data State = 0x0000 Detailed explanation: Normal

   
   
   
   The Tablespace ID uniquely identifies the tablespace within the database and will be used to redirect the containers associated with it. Most likely, your list will be longer to include additional user-defined tablespaces.
   
   In the SET commands above (Step 3), the Tablespace IDs 0,1, and 2 are used to specify the tablespace that will be assigned the new container path. All three tablespaces are System Managed Spaces (SMS) with the containers pointing to C:\tablespaces directory. Note, you only need to create the C:\tablespaces directory. Executing the SET commands will create the directories tbspc1, tbspc2, and tbspc3. If you have Database Managed Spaces (DMS), the syntax is slightly different. Let's say there's a fourth tablespace that is a DMS tablespace. We would perform something like the following:
   
   

   db2 "set tablespace containers for 0 using (path "C:\tablespaces\tbspc0")" db2 "set tablespace containers for 1 using (path "C:\tablespaces\tbspc1")" db2 "set tablespace containers for 2 using (path "C:\tablespaces\tbspc2")" db2 "set tablespace containers for 3 using (file "C:\tablespaces\tbspc3" 50000)"

   
   
   
   Note: The path is changed to file and 50000 is the number of pages you are allocating for the container. Make sure the number of pages you assign is at least the same number of pages as the production database. You can also consolidate multiple containers into one during a redirected restore if your development machine uses fewer disks than the production server.
4. db2 "restore database proddb continue"
   
   This is the final process of the redirected database restore.
5. copy /y E:\DB2\NODE0000\SQL00010\SQLOGDIR\*.* C:\DB2\NODE0000\SQL00002\SQLOGDIR\*.*
   
   Above, we copy the log files from the production machine to the development machine. Continuing with the assumption that your production database uses the online backup method, it is a requirement that a recovered database rolls forward all log files to ensure database consistency. Because of this, we will need to copy all log files from the production machine to the development machine and place them in the directory path where the database manager can find them. The path to the log files can be determined by performing the following command:
   
   

   db2 "get database configuration for <database name>"

   
   
   
   Look for "path to log files" or "changed path to log files". You should execute the above command on both the production and development servers to determine the log path to specify.
6. db2 "rollforward database devdb to end of logs and stop"

   Finally, we can perform a rollforward operation after all log files are copied to the D:\DB2INST\NODE0000\SQL00002\SQLOGDIR on the development machine. Rolling forward the log files is necessary since the database backup is performed online. Remember that the log path will be based upon the path indicated in the database configuration file.

Back to top

Additional restore considerations

If you plan on moving a database from the production machine to a development machine and it does not have the default code page 1252 (for Windows), you will need to create your database on the development machine with the correct code page before you perform the restore. If you do not do this and your production code page is different, such as 1208, the restore utility will assume the default code page of 1252 and try to restore the code page 1208 database into a code page 1252 database. This will result in an SQL2548N error.

Back to top

Scheduling the job

Now that we have detailed the steps for writing the scripts, a scheduled job can be created to run the backup and restore operation at a specific time of the day, week, or month. Our strategy is to run the backup script on the production machine before running the restore script on the development machine. Depending on the size of your production database, it may be prudent to schedule your backup several hours before you run the restore operation. This will ensure the restore does not start before the backup has completed.

Using the development machine as our example, let us go through the steps of creating a scheduled using the Script Center GUI tool.

1. Open the Control Center. Left mouse click on Tools from the menu bar and select Script Center:
   Figure 1. Selecting Script Center
   
   
2. Script Center opens. Left mouse click on Script from the menu bar and select Import....
   Figure 2. Selecting Import
   
   
3. Select your \scripts directory where the scripts are located and select restore_devdb.cmd.
   Figure 3. Selecting restore_devdb.cmd
   
   
4. 1. In the Instance list box, select the DB2 instance where the database was created.
   2. Type a new script name for the script you selected. This is just a copy of the script you selected in step 3 and will be used as the executing script.
   3. Provide a description of the script in the Script Description text box.
   4. The Working Directory text box gives you the option of specifying where you would like the output from your scripts (i.e., error and warning messages).
   5. Make sure you select the OS command radio button since we are using a command file.
   
   Figure 4. Edit Command Script window
   
   
5. Now that the script is prepared, we can schedule it to run at a predefined date. Right mouse click on the script to be run and select Schedule....
   Figure 5. Selecting Schedule
   
   
6. You can specify the script to run once or many times using this form.
   Figure 6. Specifying script
   
   

Back to top

Determining whether the scripts are successful

After you run the scripts, it would be nice to know if they executed successfully. When executing scripts from the Script Center, it is a general practice to use the Journal to determine whether the script is successful. But since we are running OS scripts that execute other OS scripts, the results of the output will not be displayed in the Journal. To alleviate this problem, you can send the DB2 messages to output within your script. For instance, in the backup script above, we backup the database in the following manner:

 
db2cmd "db2 backup db proddb online to G:\backups" 

We could include the following to allow the results of the command to be sent to backup_results.msg:

 
db2cmd " "db2 backup db proddb online to g:\backups" > backup_results.msg" 

This will allow us to determine if the command is successful. The backup_results.msg file will then be written to the working directory you specify when creating the script (see Figure 4).

Note: It is not necessary to have two sets of double quotes if the script is not calling another command window session (i.e., db2cmd).

Back to top

Conclusion

Moving a production database to a development machine can be complicated and frustrating process. This article attempts to alleviate the potential pitfalls by providing a step by step guide for creating your own backup/restore scripts and automating them to run without any user intervention. Additionally, we show the advantages of the backup and restore commands versus the db2move and split mirroring utilities. Note that each DB2 utility has its place and you may find that the db2move or split mirror utilities serve your needs better.

As with any major movement of data, it is highly recommended to practice the backup and restore scripts using a test database for both the production and development machine before implementing a large scale backup and restore. And you don't have to just practice with the syntax provided here. There are many other OS and DB2 commands you can include in the scripts to tweak your specific backup and restore strategy.

Hopefully, this article's concepts will allow you to spend less time moving data and more time developing with it.

Resources

• Backup, Restore, and Rollforward
  
  
• Backup Database Command
  
  
• Restore Database Command
  
  
• Rollforward Database Command
  
  
• db2move tool
  
  
• Split Mirror
  
  
• Replication
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Advantages
• Environment
• LOGRETAIN and USEREXIT
• Before creating the scripts
• Creating the backup script (production machine)
• Creating the restore scripts (development machine)
• Additional restore considerations
• Scheduling the job
• Determining whether the scripts are successful
• Conclusion
• Resources
• 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

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers