Migrate Databases From IBM Db2 to SQL Server in 8 Easy Steps

Modern organizations need database vendors that provide easy and cost-effective solutions with support for mission-critical needs. Of the two most popular Database Management Systems, MS SQL Server is a superior alternative to IBM Db2. It has better security & manageability, plenty of enterprise-class tools, and a lower Total Cost of Ownership (TCO).

Besides supporting mission-critical needs, Microsoft SQL Server also includes a range of capabilities for Data Integration, Data Management, Data Warehouse, and end-to-end Business Analytics. This helps organizations to enable broad end-user insight & productivity through the use of self-service Business Intelligence Tools.

In this guide, we present you with eight easy steps to migrate your databases from IBM Db2 to SQL Server. Using Db2 to SQL Server Replication, you can now have a comprehensive, integrated solution from Microsoft to contain your costs and manage compliance requirements as your business grows.

Steps for Db2 to SQL Server Migration

Microsoft SQL Server Migration Assistant (SSMA) is a handy tool designed to manage Db2 to SQL Server Replication. This tool helps you to convert Db2 to SQL server in an automated style in 8 easy steps.

• Step 1: Create New SSMA Project
• Step 2: Connect to Db2 Database
• Step 3: Connect to SQL Server
• Step 4: Mapping Db2 Schemas to SQL Server Schemas
• Step 5 (Optional): Create Assessment Reports
• Step 6: Convert Db2 Database Objects to SQL Server Database Objects
• Step 7: Load the Converted Data Objects into SQL Server
• Step 8: Migrate Data to SQL Server

SQL Server Migration Assistant supports the following target versions:

• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• SQL Server 2017 on Windows and Linux
• SQL Server 2019 on Windows and Linux
• Azure SQL Database
• Azure SQL Managed Instance
• Azure Synapse Analytics (supported by SSMA for Oracle)

To migrate your databases from IBM Db2 to SQL Server editions, SSMA for Db2 first converts your Db2 database schemas (including stored procedures) into SQL Server database schemas. It then loads the resultant schemas to SQL Server and subsequently migrates data from Db2 to SQL Server or Azure SQL Database.

Note: For a successful Db2 to SQL Server Replication, make sure that your SSMA client program has access to both the Db2 database source and the target version of your SQL Server.

To migrate your Db2 objects and data from Db2 databases to your SQL Server destination, perform the following steps in order:

Step 1: Create New SSMA Project

The first step in Db2 SQL Server migration is to create a new project. To do so, open the SSMA interface for Db2, and select File > New Project.

Here you’ll be asked to provide details about your project. Enter the project Name, Location, and target database version under the dropdown menu Migrate To. Choose from one of the following target destinations:

• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• Azure SQL Database

Click OK to create a new project.

Step 2: Connect to Db2 Database

When you create a connection to the Db2 database, you enable SSMA to obtain metadata about all Db2 schemas and display it on your Db2 Metadata Explorer pane. 

Once the connection is established, it remains active until you close the project. If you wish to start an active connection to the database after closing a project, you must reconnect it.

To connect SSMA to your Db2 database, open the File menu and select Connect to Db2. Here you’ll be prompted to enter your Db2 connection details. 

Note: If you have connected to your Db2 database previously, the option to re-establish the connection can be availed using Reconnect to Db2.

The details of fields contained within this dialog box are as follows:

• Provider: Option to select Db2 access provider. Currently, only the OLE DB Provider is available.
• Mode: Your Db2 connection mode. Choose Standard mode if you wish to specify the server name and port, and Connection string mode if you wish to make a full-string connection. 
• Manager: Your Db2 connection manager. Available options are Db2 for zOs, Db2 for LUW, or Db2 for i.
• In the standard mode, you must enter the following details
  • Server Name: Name or IP address of the database server.
  • Server Port: TCP/IP port number.
  • Initial Catalog: Name of your database.
  • User name: Db2 account user name who has the required set of permissions.
  • Password: Db2 account user password.
• In the connection string mode, you must enter your connection string in the connection string box. The string would look like this:
  
  Provider=OraOLEDB.Db2;Data Source=MyDb2DB;User Id=myUsername;Password=myPassword;

Step 3: Connect to SQL Server

When you create a connection to your SQL Server, you enable SSMA to locate where objects and data will be migrated. This mapping is customizable at the Db2 schema level, and you can do it after connecting to your SQL Server.

Open File > Connect to SQL Server to create your connection. If you’ve created one previously, you can reconnect to your SQL Server using the option Reconnect to SQL Server.

In the dialog box, enter or select the name of your SQL Server instance. If you are connecting to a default instance on your local computer, you can enter localhost or a dot(.). If you are connecting to a default instance on another computer, you must enter the name of another computer. In case of connecting to a named instance, specify the same as follows: MyServerMyInstance.

Additionally, you can specify,

• Port number of your SQL Server Db2 connection in the field Server port. 
• Name of your target database in the Database box.
• Db2 SQL Server replication authentication in the Authentication box. 
• Secure connection controls using Encrypt Connection or TrustServerCertificate.

Once you’ve filled in the details, click Connect to link your SQL Server database.

Step 4: Mapping Db2 Schemas to SQL Server Schemas

By default, SSMA migrates objects in your Db2 schema to a SQL Server database named after the schema. You can, however, alter the mapping between Db2 schemas and SQL Server databases and map to any of the available SQL Server schemas. 

To change Db2 to SQL Server replication mapping, select Schemas in the Db2 Metadata Explorer. Click on the Schema Mapping tab on the right pane and select the row that contains the mapping you wish to modify. Click Modify to change the schema mapping. 

In case you wish to revert your changes, select any row and click Reset to Default under the Schema Mapping tab. 

Note: If the target database being mapped does not exist on your SQL Server, you will receive an error message “The Database and/or schema does not exist in target SQL Server metadata. It would be created during synchronization. Do you wish to continue?” Click Yes if you wish to proceed with the Db2 to SQL replication.

Step 5 (Optional): Create Assessment Reports

You can create assessment reports to examine Db2 database objects, estimate conversion time and understand any conversion statistics or errors during Db2 to SQL Server Replication.

To create an assessment report, right-click on the Db2 schema you wish to migrate and select Create report. Another option is to select the Db2 schema and click on Create report option from the top menu bar.

Upon clicking, SSMA will generate an HTML report to help you understand any conversion statistics or errors during Db2 to SQL Server Replication. This report can also be imported into Excel for further analysis and comprehension. You can find all your HTML reports in the following directory: 

drive:<username>DocumentsSSMAProjectsMyDb2Migrationreportreport_<date>

SSMA also gives you an option to validate your data types before converting Db2 database objects to SQL Server database objects.  You may do so by going to Tools > Project Settings > Type Settings. Here you can change type mapping for each table by selecting the table name on the Db2 Metadata explorer pane.

Step 6: Convert Db2 Database Objects to SQL Server Database Objects

During the Db2 to SQL Server database object conversion process, SSMA takes object definitions from Db2, converts them into similar SQL Server objects, and then stacks them into SSMA metadata. However, please note that this information isn’t loaded into your SQL Server instance. 

Using the SQL Server Metadata Explorer, you can view the objects and their properties and set conversion options for functions and global variables under the Project Settings tab.

To convert Db2 database objects, you must first select Db2 objects, following which SSMA will perform the conversion. You can view output messages during the conversion, using the View menu > Output.

To convert Db2 objects to SQL Server syntax, expand the Db2 server, then expand Schemas in Db2 Metadata Explorer.

Choose the objects you want to convert:

• Select the check box next to Schemas to convert all schemas.
• Select the check box next to the Schema name to convert or omit a database.
• Expand a schema, then pick or clear the check box next to a category of objects to convert or omit it.
• Expand the category folder, then pick or clear the check box next to the object to convert or omit particular objects.

After selecting objects, right-click Schemas and choose Convert Schema to convert all selected items. You can also convert individual items or groups of objects by right-clicking the object or its parent folder and selecting Convert Schema.

In some cases, you might encounter an error during the conversion process. The same can be checked using a summary report from Db2 Metadata Explorer > Schemas > Report tab. Select the Review results icon, and then check the Error list window for any issues.

Step 7: Load the Converted Data Objects into SQL Server

The next step in our Db2 to SQL Server Replication is to load the resultant database objects into SQL Server. There are two ways to do this:

• You can save a script and run it in SQL Server, or
• You can synchronize the database objects using SSMA.

When using SSMA, you can immediately generate or recreate the database objects if you wish to import the converted database objects into SQL Server without modifying them. This way is quick and straightforward, but it does not allow for customization of the Transact-SQL code.

Alternatively, you can use SSMA to create scripts when you want to have more control over object creation or when you wish to change the Transact-SQL. You can then alter those scripts, build each object separately, and even schedule the creation of those objects using SQL Server Agent.

If you are synchronizing objects with SQL Server, expand the top SQL Server node and Databases in the SQL Server Metadata Explorer. Select the objects you wish to process, then right-click Databases and select Synchronize with Database from the menu that appears. Individual items or categories of objects may also be synchronized by right-clicking the object or its parent folder and selecting Synchronize with Database from the context menu.

You can save the converted database object definitions as Transact-SQL scripts if you want to change the object definitions and execute scripts manually. Select the items, right-click on Databases, and then click Save as Script to save them as scripts.

Step 8: Migrate Data to SQL Server

The last step in Db2 to SQL Server Replication is to migrate your data from Db2 to SQL Server. Db2 to SQL Server Data Migration is a batch operation that transports rows of data from Db2 databases to SQL Server tables. The Project Settings, defined by the user, control the number of rows imported into the SQL Server in each transaction.

Setting Migration Options

Before performing Db2 to SQL Server Data Migration, you can review the project migration options in the Project Settings dialog box.

Under this settings pane, you can set options such as migration batch size, table locking, constraint checking, null value handling, and identity value handling. The Migration Engine option in the Project Settings dialogue box allows you to migrate data using one of two data migration engines:

• Client Side Data Migration Engine: Select this option if you wish to start the migration from the client side.
• Server Side Data Migration Engine: Select this option if you wish to start the migration from the server side.

Migrate from Db2 to SQL Server

To migrate your data from Db2 to SQL Server, select your schemas or individual tables in the DB2 Metadata Explorer. Check the boxes next to the objects you want to move.

Next, right-click on Schemas and then select Migrate Data. You may also transfer data for specific objects or groups of things: right-click the item or its parent folder and choose Migrate Data. Enter the connection credentials in to Connect to SQL Server dialogue box and click Connect to link to the destination database SQL Server.

You will receive messages in the Output window. The Data Migration Report shows once the migration is complete. If any data did not migrate, click the row that contains the errors, and then click Details to know about the issue. When you are finished with the report, click Close. 

If necessary, you can update the database applications. 

And this brings us to the end of this Db2 to SQL Server Replication tutorial. We hope you found this tutorial interesting and informative at the same time.

Post-migration

To make sure everything is running as smoothly and effectively as possible, you must do a number of post-migration procedures after the migration step has successfully finished.

Remediate applications

All the programs that were using the source environment before have to start using the target environment after the data has been migrated there. Modifications to the applications may be necessary in order to complete this phase.

Perform tests

The test approach to database migration consists of the following activities:

Develop validation tests 

You must perform SQL queries in order to test the database conversion. You must write the validation queries to execute against the source and destination databases. Your validation queries should cover the scope you specified.

Set up a test environment

Both the source and target databases should be copied into the test environment. Make sure the test environment is isolated.

Run validation tests

Validate the source and the target by running tests and then examine the outcomes.

Conduct performance evaluations 

Conduct performance tests between the source and the target, evaluate the outcomes, and make comparisons.

Recommended:

• MySQL vs IBM DB2: 6 Critical Differences [2022 Review]
• DB2 Change Data Capture Kafka Simplified

Conclusion

MS SQL Server is a better choice for most organizations than IBM Db2. You get enhanced security combined with built-in and easy-to-use tools. MS SQL Server also offers controlled data access to help your organization meet strict compliance policies. This guide discussed eight simple steps to migrate Db2 to SQL Server. You have also seen the Db2 to SQL server migration challenges and the steps to carry out the process.

FAQ on Migrate Databases From IBM DB2 to SQL Server