How to migrate IBM DB2 to MySQL Database?

Alex Mihutski

Head of Database Migration Department, Ispirer Systems

The process of database migration can sometimes seem overwhelming and make companies delay this step. At the point when management of a company gets to know the potential risks and formidable time required for database transition they face a dilemma - migrate and take all the risks or do nothing until the system kicks the bucket?

In this article, the focus is on the migration from IBM DB2 to MySQL. We will look at the main facets of migrating from one database to another and observe the tools for optimizing such a transition. We’ll answer the following questions: what pitfalls complicate this transition? How to optimize the migration process to save time? Why move away from DB2 to open source MySQL?

What is IBM DB2?

DB2 today is a powerful suite of tools and services that are designed initially for their own platform. Since 1990, the company has been working on a Universal Database DB2 Server that can run on any major operating system, including Linux, UNIX, and Windows. DB2 is a relational database for transactional workloads that provides comprehensive Data Management and Analytics features. There are several versions of DB2, including DB2 LUW, DB2 AS400 and DB2 zOS, but we will focus mostly on DB2 LUW in this article. These databases are designed to ensure high performance, actionable insights, data availability, and dependability.

In-memory technology (IBM BLU Acceleration), powerful administration and development tools, storage optimization, workload management, actionable compression, and continuous data availability are all included in the IBM DB2 database software (IBM pureScale).

What is MySQL?

MySQL was developed by MySQL AB (which was later acquired by Sun Microsystems). MySQL is an open-source relational database management system that was first released in 1995 and is used to store, retrieve, update, and administer databases. There are several MySQL editions in the market today, including Enterprise, Classic, Standard, and other editions. Linux, Mac OS, Windows, Free BSD, Solaris, and more systems are all supported. MySQL is mostly based on C and C++.

MySQL RDBMS became popular because it was easy to use in web applications. Nowadays it is frequently used in Linux distributions in conjunction with Apache and PHP Web Server.

Migration from DB2 to MySQL

Before we delve into the challenges commonly associated with migration, let's first comprehend its primary stages:

• Database assessment. The foundation of any migration endeavor lies in a thorough assessment of the existing database. This initial stage involves a comprehensive analysis of the current state, aimed at identifying key elements that will shape subsequent decisions.
• Database schema migration. Once armed with a clear understanding of the initial setup, the focus shifts to the migration of the database schema. This step demands precision, as the structure forms the backbone of the database and influences its performance in the new environment. At Ispirer, this step is streamlined significantly as Ispirer Toolkit automates the entire database migration, including schema conversion. The equation is simple here: the higher the automation rate, the faster the migration project is. Considering that Ispirer Toolkit can be fine-tuned to provide 100% automation, the project's can be finished up to 5 times sooner, compared to manual migration.
• Data migration process design. Paying close attention to detail is crucial to avoid discrepancies in data, and a strong plan guarantees a successful transition without jeopardizing the integrity of the data. This is an essential step that includes configuring data types mapping, changing settings in source and target databases if the data migration speed is not sufficient. How is it usually implemented? A part of data is migrated to a MySQL database and if necessary the user changes the tool settings to ensure the ultimate data migration quality.
• Functional testing. Schema conversion can occasionally lead to errors or inconsistencies that affect the operation of the database. The purpose of testing is to detect all issues arising from the conversion.
• Testing database performance. An often underestimated yet critical phase involves assessing the performance of the migrated database in its new setting. Thorough testing is conducted to guarantee optimal efficiency and reliability, addressing any performance bottlenecks that may arise.
• Production data migration. This step includes migrating the data from IBM DB2 to MySQL databases.
• Cutover. The final and decisive moment arrives with the cutover phase. Careful planning is essential to minimize disruptions as the new system takes operational control. This marks the culmination of the migration process, and effective cutover strategies are key to a seamless transition.

Now let’s focus on the process of migration DB2 to MySQL in detail.

Migration from DB2 to MySQL with Ispirer Toolkit

Ispirer Toolkit is a comprehensive solution for migration and modernization projects. It includes SQLWays Wizard and nGLFly Wizard for database migration and application modernization accordingly.

Since the topic of our article is migration from DB2 to MySQL, we will look at the capabilities of the SQLWays Wizard and then describe in detail the migration process using this tool.

As it has been mentioned above, SQLWays Wizard is designed for database migration. Based on an intelligent core that reserves thousands of conversion rules, the tool is able to migrate databases with up to 100% automation.

Its smart core helps to automate the migration of a database schema, including data, tables, stored procedures, functions, triggers, and views. It supports a vast amount of migration directions between legacy and modern RDBMS, as well as being able to convert DB2 to MySQL.

One of the hallmarks of SQLWays Wizard is a flexible approach to customizing the tool with hundreds of options for any kind of migration projects. Next, we will walk through the process of migrating the database using SQLWays Wizard, assuming the toolkit has been installed already.

Step 1. Select a proper directory.
Once the toolkit is installed and registered with a valid license, specify a project directory. In this folder, SQLWays Wizard will create a project file that holds data about the ongoing migration process. Additionally, on this page, you must define the Export Directory. Also on this page you need to specify the Export Directory. The tool puts all the generated files to this directory, such as sql files with converted SQL objects, txt files with data. Bat or sh files with commands are used to import all the files to the target database. To proceed, press “Next”.

Step 2. Choose a source database.
Choose or establish a matching ODBC Data Source Name (DSN) that will enable you to link to the database targeted for migration. You must also provide the username and password for the connection, which will be verified upon clicking the "Next >" button.

Step 3. Specify a target database.
On this page, you're required to provide details for the MySQL database, including the server and the database name, along with the username and password. Additionally, you must establish a route to the Bin Directory. The directory houses the native utilities of the target database.

Step 4. Select objects.
Drag and drop all the objects that require conversion from the left tree to the right one. SQLWays Wizard seamlessly converts all kinds of SQL objects, such as tables, triggers, functions, views, stored procedures, sequences, and so on.

Step 5. Specify conversion options.
At this step, you can modify the conversion options of DDL and Data. You can define various settings that can enhance the conversion process. Within the objects tree, elements (database objects) of varying levels and categories possess distinct available settings (for instance, mappings for data types and column names, formats, files, schemas, and various kinds of database objects, and so on).

Step 6. Review the summary page.
Review all the settings of SQLWays Wizard specified for the current migration.

Step 7. Run the migration.
SQLWays Wizard executes the migration process in two steps: Conversion and Import. In the conversion stage, the utility pulls out details about the objects selected for this migration, transforms them, and produces the corresponding file set. In the import stage, the utility attempts to establish all the transformed objects and data in the destination database. Press “Start conversion” to run the conversion process. Details regarding the conversion procedure will be shown live in the "Conversion output" text box. Once the conversion process is complete, you can begin the import process to create the converted items in the target database. To start this, click on the "Start Import" button. If you want to view information about the import results, please go to the "Import output" tab.
SQLWays Wizard has an option of starting import automatically. It can be useful for your conversion projects, if you see that it may take a long time due to the large number of objects and their complexity. To enable the option, click the checkbox “Start Import Automatically”.

Step 8. Migration results.
On the final page of the SQLWays Wizard, you can check out the Conversion and Import reports and statistics on the relevant tabs. Once you hit the "Finish" button, the migration setup will be stored for subsequent uses, and the wizard will shut down.

This step allows users to review the reports, analyze conversion errors (if any), configure the tool and start conversion again. In case you have any issues at this point, you can get assistance from the Ispirer support team or choose SQLWays Wizard customization that ensures up to 100% automated migration from DB2 LUW to MySQL.

As you explore the complexities of the tool, you'll discover its user-friendliness, with a wide range of settings that accommodate both straightforward and complex projects. This remarkable blend of simplicity and flexibility makes the tool a powerful answer to database migration issues, transforming a formidable task into a well-coordinated process, and a complicated operation into a smooth, pleasant experience.

Why migrate DB2 to MySQL

Let's look at some typical situations when switching to MySQL would be a beneficial solution.

• Cost efficiency. According to some estimates, MySQL Total Cost of Ownership is 90% cheaper than other most popular commercial databases.
• Easy to use. MySQL is famous for user-friendliness. It is easy to manage and ensures simple setup, management and maintenance compared to DB2 LUW.
• Data Science and Machine Learning. MySQL allows users to create machine learning models for their data in the database. Users can also apply ML to the data for predictive analytics, customer segmentation, and more.
• Web Integration. MySQL is popular for being easily integrated with web applications. It ensures high performance in handling web-based data transactions which make it a perfect database for web apps.
• Scalability: MySQL can handle large volumes of data without affecting performance, providing a perfect solution for growing businesses.

Conclusion

In summary, transitioning from DB2 to MySQL database is a complex yet beneficial process that necessitates meticulous planning, accurate implementation, and a comprehensive knowledge of both database systems. The secret to a successful migration lies in detailed analysis, strategic tool selection, and wise utilization of automation. Although obstacles will inevitably occur, they can be overcome with the correct strategy.

MySQL, as a robust, open-source system with advanced capabilities, offers an appealing option for companies looking for cost-effectiveness, scalability, and flexibility in their data management systems. This shift is not merely a technical modification. It's a strategic step towards innovation, productivity, and ensuring your data infrastructure is future-ready.

If you would like to make an informed decision about upgrading your DB2 database using automated conversion tools, we recommend requesting a 30-day trial of Ispirer Toolkit.