Migrating DB2 Databases to Azure

Yep, still doing a lot of database migrations. Just too many people wanting to migrate their other database platforms over to Azure…

I have two customers that have DB2 databases and I know how overwhelming it can be to take on a project like this, so I thought I would go over the high level steps to this project to demonstrate it’s a lot easier than many first may believe. The SQL Server Migration Assistant is your friend and can take a lot of the hardship out of migration projects.

The overall steps to the migration are as follows:

Questions

The first step is to ask the right questions coming into a project to determine the complexity of the migration. DB2 has a number of unique scenarios that you won’t find in other database platforms, such as PL/SQL integration to attract Oracle customers and a large set of supported Operating Systems. These are essential part of the initial questions that must be asked to prepare for in the project.

The following is a guideline of questions I would start with, but not limited to, in the first round of DB2 migration questions:

1. Is the desire to migrate the databases to Azure VMs, retaining the existing databases and simply bringing over the licenses?
   

If not…

1. What version of DB2, (or 2017 Db2) are the databases that will be migrated to Azure?
   
2. What OS version of DB2, including Mainframe, LUW, (Linux/Unix/Windows) or i/z
   
3. If the database is post 9.7, is there PL/SQL that’s been introduced to the code base?
   
4. How much data will be migrated, (as in size and # of schemas)?
   
5. Any unique, advanced data types?
   
6. Is there any SQL or database code built into the application tier, (or outside of the database, all outside what the migration assistant can access)?
   

 

Translations

As with any database platform migration, there is terms that must be translated and you also need to be aware of any terms that may have different definitions between the platforms. The term, “instance”, has a different meaning than it does in SQL Server/Azure. Where a SQL Server instance is the installation of the SQL Server “bin” files, the DB2 instance is the default parent database engine, named by default to DB2. Identifying any naming conventions that may have been used differently or confused when migrating is important to deter from mistakes in communication/tasks assigned.

Some important terms to know in DB2 are:

Routines: collection of stored procedures and functions that run inside a DB2 database.

PL/SQL Collection: Specific SQL language elements built into DB2 to attract Oracle customers. Originally built into Oracle as it’s procedural language extension of SQL.

Connection Concentrator: An agent based connector, similar to a listener, but is able to scale and build out 1000’s of web/client connections via applications to the database while only requiring a few physical threads to the actual database.

Materialized Query Tables: This is a reporting table that is based off of a query in its creation statement. They are often used in data mart and warehouse databases.

Multi-dimensional Clustering Tables: The ability to cluster a table along multiple dimensions, creating better performance upon querying, especially those queries that commonly use the same columns in the where clause.

Preparation

You’ll need the following software to perform the migration in most scenarios, (outside of a unique connector if you want to perform a pull from the SQL Server, etc.):

• SQL Server Migration Assistant, (SSMA)
  
• DB2 Extension Pack for SSMA
  
• OLEDB driver for SQL Server
  

 

High Level Steps:

The next steps are what happens during the migration. It’s a two step process, first migrating the metadata, schemas and verifying all data type conversions are done successfully before you approve and proceed to the second migration step of migrating the data.

DDL, Schema and Object Migration

• Install the OLEDB driver, SSMA and DB2 Extension Pack on the destination server.
  
• Connect the SSMA to the DB2 server and database.
  
• Create a new migration project in SSMA
  
• Run an assessment report on the migration of the DDL and schemas.
  
• Identify any failures in the DDL, data type conversions or object migration estimates.
  
• Identify each schema and map to a new user database, (or if SQL schema design desired, follow steps for creation.)
  
• Once resolved, rerun the report until no errors are present or a manual workaround is documented, (actually a rare requirement, see best practices section of post).
  
• Run SSMA migration of DDL and objects from DB2 to Azure.
  
• Review log to verify all successful.
  

 

Data Migration

• In same project as you ran the DDL migration, click on migrate data.
  
• The SSMA will identify each schema and map the data to each user database or to unique schemas, depending on configuration.
  
• Will perform another assessment report of data migration for review.
  
• If satisfied, (as in all data will migrate successfully with the data type format, etc.) then run the data migration.
  
• You have the option to remove any tables not deemed as good candidates for the migration assistant to move over, (see Tips below).
  
• The SSMA will migrate the data, table by table into the new database(s).
  
• Review the migration log post the completion.
  

Tips for DB2 Database Migrations to Azure

• Review the assessment report fully for any issues.
  
• Correct issues before bringing data over.
  
• The migration assistant will re-attempt a table migration three times before it fails. IF it does have to re-attempt, you should see more than one table in the new database. The naming convention is to follow the original table name with the ‘$’ and a number. You will note those that had to be re-attempted by seeing a <tablename>$1 and <tablename>$2, etc.
  
• If you have a very large table and don’t want the migration assistant to move the data, you can remove it from the list to be migrated and post the migration, use a DB2 dump to export the data to a flat file and then use Bulk Copy Protocol, (BCP) to dump the data in. BCP is a fast, effective command line process to load data into a SQL Server database, (other processes to do so are available, too.)