A version-level upgrade is a detail-oriented task, but is usually not difficult on the pure DB2 side. The difficulties often arise on the application side - where even the scripts a DBA uses to administer DB2 can be called ‘applications’.
Some IT shops get behind on their software versions. However, the “If it ain’t broke, don't fix it” mentality should not apply when it comes to deciding whether to upgrade DB2. There's not much worse than having a problem that is clearly a software issue, and having IBM refuse to help due to out-of-support software.
One of the common complications is when the version being upgraded from is more than two full versions below the version being upgraded to. This is referred to as Skip-Level Migration. Full versions of DB2 include:
- 8.2
- 9.1
- 9.5
- 9.7 or 9.8
- 10.1
- 10.5
Amazingly, in 2015, I have completed multiple upgrades from 8.2 and even 8.1 (!) to 9.7, 10.1, and 10.5. The most common migration I have seen lately is clients who are on 8.2 and want to get to 10.1. Since 9.7 is currently the oldest supported version of DB2 (as of August, 2015), I'm fully in support of an aggressive approach to getting to a supported version.
Upgrading Two Versions
Upgrading one or two versions of DB2 is a relatively straightforward process, and in the simplest form looks like this:
- Have the application team stop all applications
- Disable incoming connections to the database(s)
- Collect all configuration information
- Backup databases
- Fully stop DB2
- Install the new version of DB2
- Update the instance(s) for the new version
- Start DB2
- Update the database(s) to the new version
- Update the database catalog to the new version
- Perform post-upgrade maintenance
- Collect all configuration information
- Re-enable incoming connections
- Have the application team start applications and fully test them
This often happens in one or more test environments before happening in production, and often takes the longest on the first system, as minor tweaks and additions may be needed for the above process. Always read the documentation for new/discontinued features and for any special upgrade steps needed. Upgrade documentation:
Be sure to explore the IBM DB2 Knowledge Center extensively on all upgrade topics before starting your upgrades.
I also recommend Melanie Stopfer's excellent presentations on upgrades – I generally spend an hour going through her slide decks from IDUG conferences as part of the planning for most upgrades.
Upgrading More than Two Versions
The process described above will not work when the starting and ending versions are more than two versions apart. When this is necessary, you must do more and come up with a more detailed plan. There are a number of decision points on which method(s) to use. There is a table at the end of this article that summarizes the methods presented here and compares them on various aspects.
Method 1: Upgrade-in-place
Description
Essentially this is just performing the steps listed for a one or two version upgrade above, but doing them twice. You can perform them twice in a row to jump straight to the target version, or you can perform them several weeks apart if your application can easily do full testing on the interim version.
Outage Time
The outage time here is significant, even with a small database. You must to issue the commands for upgrading the instance and the database twice.
Preparation Work
The main preparation work here consists of performing the upgrade on one or more test systems. The test system upgrade is critical because it allows you to fully define the process and come up with accurate timing estimates. In an ideal world, this would be performed with a full-sized copy of the production database to get accurate timings.
Testing
The opportunity for testing here is only on any test systems you may have in place.
Risks
Normal risks of any upgrade
Complexity
The complexity here is just in performing the same steps twice.
Additional Hardware/Software
No additional disk, hardware, or software is required for this method.
Fallback
The fallback plan is simple if the backout decision is made before any important data has been changed on the system. In this situation, the code on the server must be downgraded, and the pre-migration backup is restored. Backout after data change is very involved, as it is for all upgrades:
- Perform db2look to generate ddl to re-create all objects (if object definitions have changed)
- Export all data
- Downgrade the server to the original version
- Create a database and configure it with the saved settings
- Use db2look to create all objects
- Load data into down-level database
Method 2: Backup/Restore
Description
Some DBAs do not like using the MIGRATE DATABASE or UPGRADE DATABASE commands, and instead prefer to leverage the automatic migration that occurs when you restore a database from an older version of DB2 to a newer version. If you're planning on leaving the old version running in parallel for a quick backout plan, backup/restore makes more sense. The general steps look like this:
- Backup database on original version
- Upgrade the IBM DB2 Software to the interim version
- Restore the database
- Backup the database
- Upgrade the IBM DB2 software to the final version
- Restore the database
That's a lot of backing up and restoring, so may not work as well for larger databases or hardware configurations that are slower to backup.
Outage Time
The outage time may be significantly longer than the upgrade-in-place due to the duplicated backup/restore cycle, so timing the UPGRADE DATABASE versus the backup/restore will be a critical decision making point between the first two methods.
Preparation Work
The main preparation work here consists of performing the upgrade on one or more test systems. The test system upgrade is critical because it allows you to fully define the process and come up with accurate timing estimates. In an ideal world, this would be performed with a full-sized copy of the production database to get accurate timings.
Testing
The opportunity for testing here is only on any test systems you may have in place.
Risks
Normal risks of any upgrade
Complexity
Again, the complexity is relatively low, as it is just the complexity of performing the same steps twice.
Additional Hardware/Software
Additional space may be required to store the backups, if your system does not have space for 3 full database backups. Additional space may also be required if you're planning on leaving the old version in place for a quick backout strategy. Other than that no other additional hardware/software is required.
Fallback
If you have the space and choose to leave the old version in place, you can have an expedited backout plan of being able to simply point applications back at that down-level instance. Without that, the code on the server must be downgraded, and the pre-migration backup is restored. Backout after data change is very involved, as it is for all upgrades:
- Perform db2look to generate ddl to re-create all objects (if object definitions have changed)
- Export all data
- Downgrade the server to the original version
- Create a database and configure it with the saved settings
- Use db2look to create all objects
- Load data into down-level database
Method 3: Backup/Restore then Upgrade-in-place (new hardware)
Description
This method is presented as one that works well when you are also moving your DB2 database to new hardware. In the first two methods, we essentially performed the same steps twice in a row on one server. With this method, the high level approach is:
- Backup the database
- Install the IBM DB2 Software of the interim version on the new hardware
- Restore the database
- Backup the database
- Upgrade the IBM DB2 software to the final version
- Migrate the DB2 instance and databases
These simplified steps omit some of the details like configuration gathering.
Frequently, when doing a skip-level upgrade, a hardware refresh is needed too, so this methodology makes sense for those situations.
Outage Time
The outage time is comparable to the first two methods presented
Preparation Work
Installing and configuring DB2 on the new hardware is something that can be done before the cutover. Ideally it is done well ahead of time to allow some time for burn-in and for catching any hardware issues.
Performing the upgrade on one or more test systems is, as with all methods, critical. The test system upgrade is critical because it allows you to fully define the process and come up with accurate timing estimates. In an ideal world, this would be performed with a full-sized copy of the production database to get accurate timings.
Testing
The additional testing opportunity here is that testing can be done directly against the new production database server. This presents several advantages and gives the opportunity for testing things that might not be caught when testing against only test environments.
Risks
Normal risks of any upgrade
Complexity
Again, the complexity here is low.
Additional Hardware/Software
This method, as described, does require an additional database server to move to. This method is designed for situations where hardware is also being upgraded. Usually the old and new database servers are not being used at the same time, so the DB2 licenses from the old server can be used on the new server - though the new server may require additional licenses if it is more powerful.
Fallback
To talk about backout procedures here, we must define when the fallback decision is made. In this case, if the fallback is decided upon before any data has changed on the new version, the fallback is easy - applications can simply be pointed back at the old database server.
If, however, the fallback decision is made after data has changed, then the fallback plan is the same as the previous two methods.
Method 4: Using Replication to New Server
Description
There are multiple kinds of replication - SQL replication, CDC, and Q replication. Any of the three might be used in this scenario. Replication can be used between widely varying versions of DB2. Replication can be implemented between a database with the original version and a database with the final desired version of DB2. In this case, there is no interim version needed.
The cutover can also be accomplished in a nearly online method where applications stop sending traffic to the old database, the target database fully catches up, and applications are connected to the target database.
Outage Time
The lowest of the available methods - it may only be a few minutes.
Preparation Work
Installing the target version and setting up the target server are obviously required. The part of this that is difficult is setting up all that replication. Replication is set up on a table-by-table basis, and with a database with a large number of tables, that can be a huge amount of time. Additionally, the DBA must ensure that other objects from one database get moved to the new database - indexes, stored procedures, triggers, sequences, etc.
Testing
Testing must be extremely thorough to ensure that no objects are missed in the new database.
Testing may be able to go against the target database, but once you're keeping it in sync with your running production database, you won't want to change data in the target database.
Risks
One of the biggest risks with this method is simply that some object is missed in creating the database.
Complexity
This is one of the most complex methods because the DBA needs to move every object - manually or using tools such as the DCW (Database Conversion Workbench), db2look, and db2move. Setting up and verifying data replication for each table in the database is also detail-oriented and time consuming.
Additional Hardware/Software
Since both database servers are active for a while, it is likely that both will have to be fully licensed. You may be able to get a limited time exception from IBM, although there is no guarantee that you will be able to. Depending on the type of replication needed, you may also need to pay for the replication software.
Fallback
Fallback before the data has changed is easy. After data has changed, it can be extremely complicated. It may involve setting up replication in the opposite direction (which may be less complicated if scripts were developed for that during the upgrade process), or using the backout for methods #1-#3 above.
Method 5: Export/Load
Description
This method consists of creating a new database on the newer version, re-creating all objects in the target database using db2look or other tools, and then using db2move to export all of the data from the old database and load it into the new database. A general plan would consist of:
- Install newer version of DB2 (before cutover)
- Create a new instance on the newer version (before cutover)
- Create a new database on the newer version (before cutover)
- Copy all instance and database level settings (before cutover)
- Use db2look, DCW or other tools to create all objects on the new database (before cutover)
- Backup old database (during cutover/outage)
- Export data from old database and LOAD into new database, likely using db2move (during cutover/outage)
- Load data into new database, likely using db2move
This method is most appropriate for smaller amounts of data (as it would be prohibitively slow for larger databases) and for databases that are less complex - meaning they have smaller numbers of objects. If a database has a large number of stored procedures or other objects, especially if they're objects that require changes due to the upgrade, the time spent re-creating them may be prohibitive.
Outage Time
The outage time here is entirely dependent on the time it takes to do the export/load. Presumably object creation could be done ahead of time. The time for export/load should be tested to determine duration.
Preparation Work
This method may require considerable disk space - the space to hold the old database, the space to hold the data that has been exported, and the space to hold the new database.
Testing
Assuming space exists for the old and new database to exist side by side, and there is adequate hardware overhead, testing could be done against the new database. The same method could be used in test or development environments to test the upgrade methodology, and testing should also be done in those environments.
Risks
The biggest risk here is missing an object, so thorough testing is critical. Attention to detail in looking for error messages during the data export/load process is also critical. It is possible to miss a table or miss some data or miss a constraint or index. Special attention must be paid to objects created during the upgrade process - after the objects are created in the new database, but before the actual cutover is performed. The network component can also add slowness if for some reason it is very slow to transfer the files between servers - though this is a risk with any method that involves moving servers.
Complexity
This method is complex in the number of objects the DBA has to touch and pay attention to. I've seen it be very successful on databases with fewer than 200 tables and less than 50 GB of data. Note that the steps in this method are similar to the steps a viable back out method after data has changed for all methods of upgrade.
Additional Hardware/Software
Additional disk may be required if fully new servers are not involved. Space will generally be required for each of:
- Source database (old version)
- Source database backup
- IXF/DEL files
- Target database
- Target database backup (taken once all data is loaded)
Fallback
The recommendation on fallback would be to keep the old database around until the new one is fully tested. A backout plan when no data has changed remains easy this way - simply go back to using the old database. If the backout decision is made after data has been changed, then the backout method is the same as for the previous methods.
Timeline
We call it a "skip-level" upgrade when we're upgrading DB2 by more than two versions. I have seen clients decide to simply break up the upgrade into two upgrades to reduce the outage windows and to reduce the complexity. While this can be a good strategy, it is also easy to get stuck on the interim version and have the priority of the subsequent upgrade reduced.
Comparison
Upgrading more than two versions can be more complicated than a straight upgrade, and it may take some careful consideration to decide on the best method for a given environment. Comparing the methods presented here on several factors looks like this:
Are the methods presented here the only ones available? Probably not. Combining different techniques, and using tricks at the application level may give you other options.