Stuck with your redirected restore? These steps can prove a good road map for your data refresh activity using db2 redirected restore.

Please do not follow the below steps blindly, first read them, understand and map it to your environment, and use it as a guidance.

If you are using some 3rd party tool for example HIBACK, ask the backup team to restore full backup , and it's associated logs(plus-minus 2 extra logs), to a location which is accessible to both source and target servers.

Once the backup dump is restored, issue db2ckbkp on the backup file, to get the backup name as per DB2 requirement

Step 1: At source server, identify the database name and it's full backup file name and timestamp. To see the backup details, below command can be used,
       
        db2 list history backup all for db dbname.

        From the output of this command, make a note of (i) Timestamp of backup to be used;(ii) Type of backup-Offline/Online; (iii) path of backup file

        List the space used by all tablespaces
         db2pd -db dbname -tablespaces
       
        (sum of TotalPgs for all tablespaces * pagesize(4/8/16/32) Kb)

        Also capture backup of db cfg for source database
        db2 get db cfg for source dbname>dbcfg_backup_source.out

        Get buffer pool details at prod using command db2pd -db dbname -bufferpools
       

Step 2: Run below command to generate restore script at source

        db2 restore db source_dbname from source_dbname_backup_path taken at source_dbname_backup_timestamp redirect generate script redirect_restore_script.sql

        Now copy the restore script(redirect_restore_script.sql and backup file identified in Step 1 to some staging location for example /tmp.
        After copying to staging location,run below command to change file permisions,

         chmod 777 /tmp/backup-file-name

         chmod 777 /tmp/redirect_restore_script.sql

Step 3: Now copy these two files to target server. Below scp commands can be used to trasnfer files from source server to target server
       
        scp file-to-be-transfered your_username@remotehost.edu:/some/remote/directory


Step 4: At target server, perform following tasks,

        a) generate restore script of database at target server, which is to be restored.
           db2 restore db target_dbname from backup_path taken at backup_timestamp redirect generate script -o redirect_restore_script_target.sql
       
        b) Run db2look to capture grants and federation objects if any
           db2look -d target_dbname -a -x > target_dbname_grants.sql
           db2look -d target_dbname -a -e -fedonly > target_dbname_fed_objects.sql

           db2 get db cfg for target_dbname>dbcfg_backup_target.out
       
        c) Compare the restore script generated from source and target databases. Then modify the one genertated at source, by using the parameters from target
            sdiff source_restore_script target_restore_sript>diff_restore_script.out

        d) Run db2set -all|grep -e 'DB2_RESTORE_GRANT_ADMIN_AUTHORITIES',  on target server to check the status of registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES.
         
           If this registry variable is set to NO, the instance id(different from source instance) at target server will not be able to perform restore.
           In such a case, update this parameter to YES(db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=YES), and stop-start db2 instance.

       e) Get buffer pool details at prod using command db2pd -db dbname -bufferpools


Step 5: Make sure there is enough space(as identified in Step 1) on target server. Also recheck the NEWLOGPATH being used;If it has any old log files, remove them.
       
        List all the applications running on target database to be restored. Then force them, after confirming with application team using below command,

        db2 "force application (APPL_HDL)"

        After all the applications have been forced, move to next step

Step 6:
     (a) If restore is to be done using offline backup, use below command,

         db2 restore db dbname from path-of-backup on dbpath without rolling forward


      (b) For online backups, restore statement should look like,

          RESTORE DATABASE db-name FROM backup-location TAKEN AT backup-timestamp on path-for-db2-data LOGTARGET 'path for extracting logs from online backup'
          (make sure that log target path does not have existing logs)

          Once the restore is complete, we need to do roll forward recovery using below command(can be appended to the restore script),
         
 db2 "rollforward db dbname to end of backup and complete overflow log path('path for extracting logs from online backup')"        
         
         

Step 7: Once all the restore(and rollforward statements are ready), execute them all in same session. This can be done by putting all the statements in a script,
        and executing the script with nohup option eg. nohup db2 -tvf restore_script.sql

 

Step 8: Once the restore script is submitted, monitor the progress of restore using db2pd -target-dbname -util, or db2 list utilities

Step 9: Compare the db cfg, and update the restored db database cfg parameters if required
       
        Execute grants ddls.

        Create the federation objects(do remember to provide passwords)
       
        alter buffer pool size as per test environment available memory.

Step 10: make a test connection to restored database, and query any table for verification purpose.

Step 11: Send communication.

Notes:

If for some reason , restore utility is to be cancelled, use command db2 restore db yourdbname abort