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.

Prerequisites For Connecting Db2 to SQL Server

Before getting started with the steps to connect Db2 to SQL Server, please ensure that:

• Your source environment is supported.
• You have installed SQL Server Migration Assistant (SSMA) for Db2.
  (If you are a first time user, refer to the following instructions for installing the SSMA client application on your computer – Installing SSMA for Db2 (Db2ToSQL))
• You have adequate connection permissions to access both the source and the target.

What is SQL Server?

MS SQL Server is a Relational Database Management System (RDBMS) from Microsoft. It is also an Object-Relational Database Management System (ORDBMS) which essentially represents a combination of RDBMS and Object-Oriented Database Model. 

MS SQL Server comprises the following components:

• Database Engine for creating and storing data in tables, 
• Analysis Services (SSAS) for querying data, decision support, and Business Analytics,
• Reporting Services (SSRS) to create, deploy and manage reports, and
• Integration Services (SSIS) for carrying out ETL operations, Data Integration, and Data Transformation tasks.

Hevo’s No-Code Data Pipeline Solution gives you the flexibility to either query the entire dataset, or only the data you need, as many times and in as many number of ways after it is loaded to the Destination.

MS SQL Server supports a wide variety of applications in corporate IT environments. Businesses use it in activities ranging from Transaction Processing (T-SQL) to Business Intelligence and Data Analytics. 

As the name suggests, MS SQL Server is built on top of SQL, a language that database administrators and IT professionals use to manage and search databases. SQL Server is platform-dependent and works exclusively on Windows and Linux (starting SQL Server 2017) environments. It’s one of the most popular Database Management Systems, next to Oracle Database and IBM’s Db2. 

What is IBM Db2?

IBM’s Database 2 or Db2 is a family of data management products. First released in 1983, the name Db2 was given to IBM’s Database Management System or DBMS. At that time, this database was specific to IBM’s MVS (Multiple Virtual Storage) mainframe platforms. Later, IBM extended Db2 to include other operating systems like OS/2, UNIX, MS Windows servers, Linux, and PDAs.

Db2 is designed to store, analyze and retrieve data from relational databases efficiently. Beyond relational databases, Db2 also supports object-oriented features and non-relational data like XML using Db2 pureXML. Db2 comes with a complete suite of  AI-empowered capabilities that help you explore data faster, and manage both structured and unstructured data on-premise as well as in the cloud.

As a user, you can define and manipulate your data in Db2 using Structured Query Language (SQL). SQL can help you define, modify, or drop data objects, such as tables in Db2, or authorize users to access specific resources just like in conventional RDBMS.

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. 

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 SQL Server Db2 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.

Recommended:

• PostgreSQL vs DB2: 5 Critical Differences
• MySQL vs IBM DB2: 6 Critical Differences [2022 Review]

Conclusion

For most organizations, MS SQL Server is a better choice compared to 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. In this guide, we 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.

For ETL newcomers, Data Migration is a never-ending battle they can’t seem to win. However, most of these processes have been simplified by today’s quick and reliable ETL solutions, such as Hevo Data.

Hevo Data, a No-code Data Pipeline Platform, provides you with a consistent and reliable solution to manage data transfer from 150+ Data Sources (40+ Free Sources) like MS SQL Server, and Azure SQL Database to your desired destination like a Data Warehouse or Database in a hassle-free format. 

Visit our Website to Explore Hevo

Hevo also allows the integration of data from Non-native Data Sources using Hevo’s in-built Webhooks Connector.

Why not take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the unbeatable pricing, which will assist you in selecting the best plan for your requirements.

Thank you for reading!

If you have any questions regarding Db2 to SQL Server Replication, please leave them in the comment section below. We’d be happy to assist.