IBM Support

75 ways to demystify DB2: #1: Expert Advice: DB2 Database Backup and Restore in DPF using Single System View

Technical Blog Post


Abstract

75 ways to demystify DB2: #1: Expert Advice: DB2 Database Backup and Restore in DPF using Single System View

Body

Have you tried taking DB2 database backup and restoring it in DPF using Single System View?

DB2 Version 9.5 (or later) provides the Single System View (SSV) function which allows backing up multiple database partitions at once. In earlier releases, partitioned databases needed to be backed up one partition at a time which can be time consuming and prone to errors. The backup timestamp associated with all database partitions is the same. I am listing down the commands with minimum clauses to illustrate how SSV can be used.
                             
1. The "Backup" Command:
                                                             
 db2 "backup db test ON ALL DBPARTITIONNUMS"                             


-- This single command backed up all 4 partitions and created 4 backup imaged with SAME timestamp as follows:                                   
TEST.0.ygawali.NODE0003.CATN0000.20150108003841.001                      
TEST.0.ygawali.NODE0002.CATN0000.20150108003841.001                      
TEST.0.ygawali.NODE0001.CATN0000.20150108003841.001                      
TEST.0.ygawali.NODE0000.CATN0000.20150108003841.001                      
                                                                         
2. The "Restore" Command:  
                                                           
 db2_all ' db2 restore database test from /home/ygawali/backup taken at 20150108003841 without prompting '                                       
-- This command will perform RESTORE on ALL partitions (including catalog partition) in one go.         

OR                                                   
                             
You may restore the same backup images as follows (catalog partition first and then non-catalog partitions in parallel):

 db2_all "<<+0< db2 restore db test from /home/ygawali/backup taken at 20150108003841 without prompting"                                             
 db2_all "<<-0< db2 restore db test from /home/ygawali/backup taken at 20150108003841 without prompting"    

Thanks!                                         
 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141276