Moving Mountains with a Redirected Restore

With the development and adoption of automatic storage the art of the redirected restore is going the way of the dinosaur. For those with systems that are running the tried and true (and deprecated) DMS (Database Managed Storage) table spaces a redirected restore can be a lifesaver.

There is a neat trick to generate a very cool restore script that can easily be modified for a restore using automatic storage.

A redirected restore has the sole purpose of moving a database from one server to another (or one set of filesystems to another) via a backup. This method is faster and more efficient than other methods. Creating, moving, and restoring a single database file is much easier than exporting data, moving large files, and loading data on another server.

The complication is that a redirected restore actually has to stop and say “You specified that you wanted to change filesystems. What file systems should I assign these table spaces”? Once details are provided the restore proceeds merrily.

Back in the old days when I was walking to school uphill in the snow, we had to manually write a script to do this. It involved pulling DDLs, doing research, and writing a fancy SQL statement. Like many areas in DB2, it was very sensitive to proper syntax. A single ‘ in the wrong place, and you had to re-do a number of statements. Now DB2 does a lot of that work for you by generating a script.

For the purpose of this article, I am going focus on an older design that purely uses DMS and SMS table spaces and automatic storage is not involved. This article also assumes a backup image exists on the target server. This is a worst-case scenario and steps would be eliminated for a more up-to-date table space design.
Once a backup exists, you can generate a redirected restore script based off of information stored within the backup. This requires execution of the restore command.

Command:

RESTORE DATABASE <DBNAME> FROM <DIRECTORY> TAKEN AT <TIMESTAMP IN BACKUP FILE NAME> REDIRECT GENERATE SCRIPT <SCRIPTNAME.sql>

Your first thought may be “Ack! No, that is the restore command! I don’t want to over write things yet”. To be honest, I still get nervous with this command. First, nothing is overwritten as long as you have REDIRECT GENERATE SCRIPT in the command. Second, if my server already has a database installed and activated, I would be stopped and prompted to proceed which gives me a chance to panic and say “No”!

Once you have the redirected restore script, you will see it is divided into three parts:

• Initial RESTORE command
• A series of commands to assign existing table spaces to new file systems
• A final RESTORE DATABASE … CONTINUE command

INITIAL RESTORE COMMAND

A full sized restore script can be huge, so this section focuses on the first section and what to watch for when editing.

RESTORE DATABASE <Backup DB Name>
— USER <username>
— USING ‘<password>’
FROM ‘</filesystem/backup/is/in>‘
TAKEN AT <timestamp in backup filename>
— ON ‘<automatic storage filesystem of target database>‘
— DBPATH ON ‘<target-directory>’
— INTO <target database name>
— LOGTARGET ‘</directory/to/extract/log/to>‘
— NEWLOGPATH ‘<leave default and commented out>’
— WITH <num-buff> BUFFERS
— BUFFER <buffer-size>
— REPLACE HISTORY FILE
— REPLACE EXISTING
REDIRECT
— PARALLELISM <n>
— COMPRLIB ‘<lib-name>’
— COMPROPTS ‘<options-string>’
— WITHOUT ROLLING FORWARD
— WITHOUT PROMPTING
;

 Text in blue is the most common values to replace. Comment markers in red are what you will most likely uncomment for your initial restore.

Most of the blue comments are pre-filled in which acts as a guide on what should be replaced. If you are doing the restore to another server, and you planned properly, your new server is pretty similar to your old server so much of this may be the same.
ASSIGNING TABLESPACES TO NEW FILE SYSTEMS

DB2 knows what you want and what it has to work with once you have edited your SQL file past the first section. The next step tells DB2 how your DMS table spaces will be rearranged. When editing this section, you will be compensating for a different file system configuration or naming nomenclature.

— SET TABLESPACE CONTAINERS FOR X
— USING (FILE /filesystem/directory <no pages>,
— FILE /filesystem/directory2 <no pages>
— );
<repeat for each table space>

This is where you need to be detail oriented. For each table space that needs to be on a different path, uncomment the SET TABLESPACE section and fill in the new target file system and allocated pages.

Once these two sections are completed, the bulk of your work is done. The last section usually does not require any editing.

RESTORING THE DATABASE

Once complete, run your newly developed script. This isn’t a stored procedure, or use any oddball terminators, so it is as easy as:

db2 –tvf redirected_restore.sql | tee redirected_restore.out

Pay attention to the output as it passes by, depending on if a database existed previously or not you may be asked to confirm proceeding with the restore. Review the output file to ensure no statements failed or generated warnings.

AUTOMATIC STORAGE AND RESTORES

The beauty of automatic storage is the ease of restore, especially across servers. The key to a restore with automatic storage is the ON parameter. This parameter allows you to redirect your database into newly defined automatic storage, assuming you are using the single default storage group. If you are using multiple storage groups, you still need to do a redirected restore, but will use the SET STOGROUP PATHS command instead of the SET TABLESPACE CONTAINERS command detailed above.

I prefer to generate the script for most restores and use only the RESTORE DATABASE section if no redirection is required. This method acts as a sanity check on all the parameters that may need to be altered.

Michael Krafick is an IBM Champion and occasional contributor to db2commerce.com. He has been a DB2 LUW DBA for over 15 years in data warehousing, transactional OLTP environments, and e-commerce databases. He was recently inducted into the IDUG “Speaker Hall of Fame” and given the IBM “DB2 Community Leader Award” in 2015. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) icloud (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: @mkrafick