Wednesday, August 20, 2008

SSV Backup

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-23.44.56.125237“

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.

New Poll- Backup Target

Thanks everyone for participating in my previous poll regarding DB2 autonomic features. Here is the final poll result


Self-tuning memory manager – 14 (51%)

Automatic storage – 11 (40%)

Automatic maintenance – 8 (29%)

Configuration advisor – 7 (25%)

Health monitor – 9 (33%)

Other – 1 (3%)

None – 6 (22%)


Interestingly, the use of the above features is almost in the same order as their positions in the listing. It’s evident that the top two autonomic features, self-tuning memory manager and automatic storage are the most popular autonomic features as per the poll result.


I have added a new poll ... No, this is not about Democratic vs. Republican J This is related to your database backup target. Please indicate the backup target as you specify in your backup command. For example, if your backup target is TSM, choose TSM only even though TSM will store the backup image on tape. If you select “Other”, it would be nice if you can explicitly mention the exact target through a comment to this post. I hope there will be more participation this time and thus the poll result will be more realistic. So, please don’t forget to provide your input in this poll !!!