Redirected restore of a Db2 LUW database — why and how?
Recently I have been working on database migration between servers with the Db2 upgrade at the same time. Namely, my client wanted to upgrade not only Db2 (from 11.1 to 11.5), but also the hardware, the operating system etc. When it comes to database migration, the first thing that comes to my mind is the redirected restore.
The redirected restore has the following structure:
- Run the “restore redirect” command:
db2 restore db <db-name> from <backup-image> taken at <timestamp> redirect
2. Modify what you need (containers, stogroup paths, database name, database path, log path etc.), for example:
db2 set stogroup paths for SG_HOT on '/sdd/fs1', '/sdd/fs2'
db2 set tablespace containers for 2 using (file '/datapath/c1' 50000, file '/datapath/c2' 50000);
3. Run the “restore continue” command:
db2 restore db <db-name> continue
Although, you can execute all the commands one by on in your shell, I highly recommend using a redirect restore script.
Performing a redirected restore using an automatically generated script
Creating a script has many advantages. The most valuable one is that it shows you the configuration of the source database (backup image) and you just have to alter the setup whenever needed. You don’t have to remember what to set and in which order. The command to generate the redirect script is the following:
db2 "restore db <db-name> from <backup-image> taken at <timestamp> redirect generate script <script-filename>"
The script generated during this operation contains three main sections:
- the initial restore command,
- commands to redefine table space containers or storage paths,
- and the final command to continue the restore.
After the script is generated, you can make all the required changes (just modify it). Usually you don’t have to write new statements. Just uncomment the needed ones and alter those to be changed. Below is an example of how such a file (after changes) may look:
-- ****************************************************************************
-- ** Automatically created redirect restore script
-- ****************************************************************************
-- Set client options
UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON;
SET CLIENT ATTACH_MEMBER 0;SET CLIENT CONNECT_MEMBER 0;
-- Initial RESTORE DATABASE command
RESTORE DATABASE SAMPLE
-- USER <username>
-- USING '<password>'
FROM '/backup'
TAKEN AT 20250503110628
ON '/db2inst1/sample'
DBPATH ON '/db2inst1/sample'
INTO NEW_SAMPLE
LOGTARGET '/db2inst1/sample/logs'
NEWLOGPATH '/db2inst1/sample/newlogs'
REDIRECT
WITHOUT PROMPTING;
-- Redefine table space containers (modify as needed)
SET TABLESPACE CONTAINERS FOR 2 USING (
FILE '/datapath/c1' 50000,
FILE '/datapath/c2' 50000
);
SET TABLESPACE CONTAINERS FOR 3 USING (
FILE '/indexpath/c1' 15000,
FILE '/indexpath/c2' 15000
);
-- Redefine storage group paths (if applicable)
SET STOGROUP PATHS FOR SG_HOT ON '/sdd/fs1', '/sdd/fs2';
SET STOGROUP PATHS FOR SG_COLD ON '/hdd/path1', '/hdd/path2';
-- Final RESTORE DATABASE command to continue the operation
RESTORE DATABASE SAMPLE CONTINUE;
As you can see, I changed the database name (from SAMPLE into NEW_SAMPLE).
Now, you just have to execute the script, e.g. like this:
db2 -tvf <script-filename>
Be prepared to see errors. It took me some time to learn how to properly modify the file. The redirected restore should take similar time as the standard one.
More about redirected restore (IBM Db2 Documentation) here
More about redirected restore using script (IBM Db2 Documentation) here
Database migration is not the only reason to perform the redirected restore. You may also use it on the same server you took a backup just to set up paths or containers (when it comes to SMS containers, this is the only way to reconfigure them).
