Five Methods for Skip-Level DB2 Version Migrations in DB2 LUW

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:

  1. Have the application team stop all applications
  2. Disable incoming connections to the database(s)
  3. Collect all configuration information
  4. Backup databases
  5. Fully stop DB2
  6. Install the new version of DB2
  7. Update the instance(s) for the new version
  8. Start DB2
  9. Update the database(s) to the new version
  10. Update the database catalog to the new version
  11. Perform post-upgrade maintenance
  12. Collect all configuration information
  13. Re-enable incoming connections
  14. 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:

crooks-migration-comparison.png

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.

 

 

2 Likes
Recent Stories
Lessons Learned of Locking and Latches by Adrian Burke

Db2 for z/OS locking basics

A Beginners Guide to Locks, Latches, Claims and Drains in DB2 for z/OS by Steve Thomas