As I mentioned in my previous post DB2 Partitioning, Database partitioning feature (DPF) of DB2 enables DB2 to provide a very scalable database solution. The applications connecting to database do not have to be aware of database partitioning. However, the DBAs managing the multi-partition database have to deal with some complexities. One of the administration complexities of multi-partition database is Backup and Restore. Each database partition has to be backed up separately. Also, the catalog partition has to be backed up before any other partition. The db2_all command is used in such scenarios to run a command against one or more than one database partitions as shown in the example below. Following example shows the offline backup of a multi-partition database “test” with catalog partition 0.
db2_all ‘<<+0< db2 BACKUP DATABASE test to /backup_path’
db2_all ‘|<<-0< db2 BACKUP DATABASE test to /backup_path’
The first line in the above example will backup the partition 0 and the second line will backup the remaining partitions in parallel. With the above approach, the backup timestamp of all the partitions would not be the same. Hence, we have to separately identify the backup images for each partition while restoring the database.
In DB2 9.5, you don’t have to deal with the above mentioned complexity because of Single system view (SSV) backup. SSV backup allows to backup some or all the database partitions using ON DBPARTITIONNUMS or ON ALL DBPARTITIONNUMS clause of BACKUP DATABASE command. The backup images created by SSV backup have the same timestamp for each database partition. The SSV backup has to be run from catalog partition. In the following example, all the database partitions are backed up using SSV backup. The backup image of all the database partitions has the same timestamp. Hence, all the database partitions could be restored with a single command using db2_all.
db2 BACKUP DB test ON ALL DBPARTITIONNUMS ONLINE INCLUDE LOGS
db2_all “db2 RESTORE DB test TAKEN AT 2008-08-17-188.8.131.52237“
db2 ROLLFORWARD DB test TO END OF BACKUP ON ALL DBPARTITIONNUMS
Some more good news... In the above example, notice that the backup image would include the logs. Prior to version 9.5, logs can be included with the online backup image for single partition databases only. With SSV backup, it’s possible to include the logs with online backup image for multi-partition databases also. Finally, notice the use of TO END OF BACKUP clause with the ROLLFORWARD command. It really simplifies to rollforward all the database partitions to the minimum recovery time and saves the effort to determine minimum recovery time over all the database partitions.