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.
Easily migrate data from various sources to SQL Server with Hevo’s robust ETL platform. Automate and streamline your data workflows effortlessly, with seamless integrations and real-time processing.
Focus on what matters most—data insights—while Hevo handles the heavy lifting. Experience hassle-free migrations and optimized data management.
Automate your SQL Server Migration using Hevo
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.
Image Source: Microsoft Docs
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;
Migrate from MariaDB to MS SQL Server
Migrate from MySQL to MS SQL Server
Migrate from PostgreSQL to MS SQL Server
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.
Load your Data from Source to Destination within minutes
No credit card required
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:
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
1. How to transfer data from DB2 to SQL Server?
To transfer data from DB2 to SQL Server, export data from DB2 to CSV files and import them into SQL Server using bulk import tools like SQL Server Integration Services (SSIS).
2. What should you use to export from DB2 and import the data to SQL Server?
Using DB2 Export and SQL Server Import
3. How to migrate data from DB2 to MySQL?
a) Using MySQL Workbench and DB2 Export
b) Using MySQL Migration Toolkit
Divyansh is a Marketing Research Analyst at Hevo who specializes in data analysis. He is a BITS Pilani Alumnus and has collaborated with thought leaders in the data industry to write articles on diverse data-related topics, such as data integration and infrastructure. The contributions he makes through his content are instrumental in advancing the data industry.
