Many of our Virtual-DBA clients have DB2 upgrade plans in the works. One of them wants to move from V9.1 to V10.1 on Windows. I’ll share some of my experience with this project. Right now I’ve completed the set up of the 10.1 instance and restored a copy of their database there. They are now kicking the tires. Here are some more details:
System Background:
This a client of ours that has a busy, 250 GB, OLTP database using DB2 version 9.1 on Windows. We have set up an HADR Standby and also a disaster recovery server using log shipping. The application is a home-grown java application.
My Mission:
Move the production database from DB2 version 9.1 to a new version 10.1 DB2 instance and set up multiple HADR standby systems and Read On Standby.
My Plan:
- Install DB2 v9.7 and v10.1 on a new Windows 2008 server.
- Create 2 instances on the new server. One with version 9.7 and one with version 10.1
- Take an offline backup of the production database and restore it onto the V9.7 instance.
- Take an offline backup of the database on the V9.7 instance and restore that into the V10.1 instance.
- Perform testing of the applications against the V10.1 database.
- When a cutover date is assigned, Take the current 9.1 production instance offline. Backup the database and restore onto 9.7 and then onto 10.1 instance in the same way as before.
- Then set up the two HADR standby servers from online backups of the 10.1 database.
My Progress and Lessons Learned.
When restoring a database between versions of DB2, you need to use an Offline backup of the database. This is because the transaction logs written during an Online backup aren’t usable on the new version. So, because we could not take the production database offline, I had to restore an Online backup of the database onto a development server which also uses version 9.1. I could then take that database offline for a backup.
Once I got an Offline backup copied to the new hardware, I restored it into the V9.7 instance. The restore also migrates the database to the new version. After the restore completed I got an error:
SQL1243W
Dropping SYSTOOLS.DB2LOOK_INFO db2look operation table failed in a DB2 Version 8 database.
Explanation
The UPGRADE DATABASE command checks whether the DB2 Version 8 database has a db2look operation table called SYSTOOLS.DB2LOOK_INFO, and drops this table if it exists.
After looking through the diagnostic log I found that the upgrade was successful so I dropped the DB2LOOK_INFO table and proceeded to test the database by connecting and selecting from a few tables. It looked good, so I took an Offline backup, and restored that into the 10.1 instance. That finished without error. All in all it was a pretty smooth process. I’ll keep you updated when we go live!