A few weeks ago I watched an excellent presentation by Melanie Stopfer from IBM on the IDUG website. I have just tried to find it again and currently you can still watch it again, click here and click on the link about two thirds of the way down the page and you too can still watch the excellent presentation. Even better you can download the slides as well, once that page has loaded up if you do not have time to watch the hour long presentation on upgrading your DB2. I can’t stress enough the usefulness of this webinar and the materials and if IDUG was not so altruistic then I imagine you would be paying a lot of money, only the DB2 Night Shows have impressed me this much in the level of educational value so far.
Upgrading to DB2 LUW V10
In this post I am going to look at what I have learned from some testing that has been done at work so far upgrading a DB2 LUW V9.7 database to DB2 LUW V10.1 database. Identify some of the pitfalls that we have found and and tell you some good features that maybe were not extolled enough from the presentation.
First off we upgraded the production installations and instances to the latest version fixpack of DB2 V9.7, fixpack 6 at time of writing. Then took either OFFLINE backups of a smaller database that no one would notice were missing, or ONLINE backups and logs for larger ones. As there is no test system where I work (part of the reason for dangerous DBA) these tests were carried out on the new servers that we will be moving too in the hopefully not distant future therefore we are not going to upgrade the current production instances per say. We tried three ways of upgrading DB2 V9.7 to V10, one of them is a complete non starter fail but we thought we would give it a go any case just to see what would happen:
- Restore a ONLINE V9.7 backup into a V10 instance – FAIL
- Restore a ONLINE V9.7 backup into a V9.7 instance rollforward on the logs and then upgrade to V10 – SUCCESS
- Restore an OFFLINE V9.7 backup into a V10 instance – SUCCESS
After the RESTORE’s and upgrades had happened testing the applications that we connect into DB2 was next on the agenda. Again as we have no test systems for our applications then we could only carry out testing in kind. We did find though that most applications with the DB2 9.7 driver in them or connections using ODBC V9.7 driver still worked fine, but after the DB2 upgrade then revisiting these will be a top priority.
Restore a ONLINE V9.7 backup into a V10 instance – FAIL
This will not work as an online backup always needs to rollforward on logs. When you upgrade to V10 the log numbers get reset. Therefore the online backup into V10 will not work. Now there is not a lot of / no literature out on the web or in the IBM Info center into why, so there we go you have it now. As for output the RESTORE command bombs out with the backup is not compatible and fails gracefully.
Restore a ONLINE V9.7 backup into a V9.7 instance rollforward on the logs and then upgrade to V10 – SUCCESS
This I think will represent the most alignment with the traditional upgrade path for most. In our case then we did a restore and then an upgrade, but I guess most of you will be upgrading your production instance. The ONLINE backup and RESTORE were used as we wanted to simulate as close as possible without the ability to actually set up (lack of kit, lack of time to bring down prod instances) how we are going to get our databases from current production to the new servers by using HADR. Melanie in the webinar and slides covers most of a high level plan to do this and from what was tested seems to work perfectly fine, apart from some issues that we had rebinding packages after the installation, but dropping and recreating them worked perfectly fine. Below is a list of GOTCHA’s and Notes from the experience:
GOTCHA’s
- When you upgrade the instance your old sqllib directory will get renamed which is very handy. This is because when you issue the db2iupgrade command then the instance is moved to the new DB2 10.1 installation
- Log numbering will restart at 1!
- If you instance / database upgrade goes wrong, make sure you know where your all your old installation, fixpack and backup media are as you will be needing them, plus possibly lots of coffee or favorite caffeine based drink. This is where I think our upgrade situation will be a little less stressful as the upgrade will be on a non production database.
- You will still have V9.7 and V10 installed until you clean up your install dir make sure you and your shop make instances using the commands from the right dirs, so make sure you update those automated scripts!
- db2prereqcheck command is your friend and will make sure everything is there that will be needed and should avoid anything that is in point 3 hopefully
Notes:
- HADR while it works across different fixpack version of DB2 (e.g. V9.7 Fixpack 4 to V9.7 Fixpack 6) for short periods, will not work between the major versions due to renumbering of the log files once the database is upgraded!
Restore an OFFLINE V9.7 backup into a V10 instance – SUCCESS
This did unexpectedly good things, that might be documented somewhere, but not easy to find and was not talked about in the webinar, in that when you RESTORE a V9.7 OFFLINE backup into V10 instance then it does (it seems too) do all of the upgrading for you! This was less of an issue to set up the the one using the ONLINE backup as it did all the database upgrading and rebinding of packages etc, with non of the errors that were encountered previously. This might be a very good upgrade method if you are also moving to new kit and can afford the downtime of the OFFLINE backup of the original database.
GOTCHA
- Time taken to take the OFFLINE back could be greater than you are willing to accept, but it seems from the limited testing done the least hassle after that point.
What are we going to do?
Well we are going to be going to new servers, and SAN and version of DB2 all at the same time. In a future post I will cover the different methods that we could use to get there more fully but we will be using the “Restore a ONLINE V9.7 backup into a V9.7 instance rollforward on the logs and then upgrade to V10” method. This will mean we have a production database to use while the new servers database is upgraded, then connections can be changed to point at the new server.
Future Posts
In a future post I will look at the potential behind different methods that could be used to get your database from A to B and upgrading at the same time.