IBM DB2 LUW to MariaDB Online Presentation


Demo presentation text:

Hello!

This is the demo about the tools provided by Ispirer Systems for database or scripts migrations. All of them are graphical tools based on a command line tool called Ispirer Migration and Modernization Engine. This demo is particularly devoted to the database conversion from DB2 LUW to Maria DB.

For this conversion we will use the following tools:

• Ispirer Migration and Modernization Engine – a command line tool that can be run in batch mode and can perform the automatic conversion of a database or database and application code contained in scripts.

• Ispirer SQLWays Wizard – a GUI tool designed particularly for the database migration. Works with a source database using the ODBC connection.

• Ispirer Migration Studio – a GUI tool oriented specifically to the conversion of a separate application or database script. Works with one script at a time.

• Ispirer Migration Commander – a GUI tool designed for the conversion of multiple files containing application or database code. Works with several scripts at a time.

For this demo, we have prepared a DB2 LUW database. You can see its contents open in Data Studio. For the migration purposes we have also prepared an empty Maria DB database called DEMO.

Let’s convert this database using Ispirer MnMTK 2015.

Let’s start with Ispirer SQLWays Wizard which you can see on your screen. This tool works with a source database using the ODBC connection. And for the target MariaDB database it creates commands for native tools. In this case the native tool is mysql.exe. When you start Ispirer SQLWays Wizard you see Ispirer SQLWays Wizard Welcome page. Here you need to specify a license that you have for this product and a project directory where a set of files required for the project is stored.

Well, I’ve got it selected here, we can click next.

On «Choose a source database» page you need to specify a user or system DSN name for ODBC connection for DB2 database, a user name and a password. Also I’d like to mention that it is recommended to choose a user that has appropriate rights to work with system tables in DB2 because a lot of additional information is extracted from system tables for a conversion of business logic from DB2 to MariaDB.

On «Choose a target database» page, you need to specify MariaDB as a target from the list of the supported databases.

To make an import you need to specify:

Host

Port

Database

The user name and the password that you are going to use to load the objects to MariaDB database.

Also you need to specify a bin directory where mysql.exe utility is located.

On «Specify database objects or query» page you can see all the objects of the source database specified in a tree view: each object is specified in its own schema and in its own object type.

In the target pane you can see the tree view of the selected objects.

On «Specify DDL and data options» page there is a set of options which help you to customize DB2 LUW to MariaDB conversion. For example, an option that controls the schema name conversion, reserved words conversion, data types conversion and etc.

Also I’d like to show you the option that controls the way DDL and Data are converted. They can be run in separate processes. For example, I have a lot of data in DB2 LUW and I don’t want to convert the data in the first run. For this purpose you need to open «Data options» tab and uncheck «Export data» option, in this case data will not be converted. I’ve performed the DDL conversion and now I want to check the data conversion, so I open «DDL options» tab, select «Generation options» instead of «Global data type mapping» and uncheck «Generate DDL». Now only data is converted.

There are a lot of different options that help you to customize the migration in our tool. If you need some specific options, please contact us and we will be glad to assist you.

On «Specify export file options» page there is an option «Export directory» which is used as an intermediate repository for all the files which our tool generates during the conversion from DB2 LUW to MariaDB. There is also «Run in trace mode» option that allows us to see the commented source and the equivalent.

When the files are converted, the status is provided as well. The import process has started so you can see it here on the same «Migration status» page. When the import process ends, we can go to a «View Export» page.

There is also a SQLWays log file that depicts export process. Here you can see commands which are run by Ispirer SQLWays Wizard in batch mode. At the end of this file you can see summary information about the export process.

The same information about the export process can be reviewed in SQLWays_report.html file. There is an object summary page, the number of rows of data for tables, lines of code for business logic objects, total summary information. Information per each object type (stored procedures, functions, triggers, etc).

As to the import process, there is sqlways_imp.log file that depicts the import process into MariaDB system. This information is provided by MariaDB server to our tool so there is no control of the import process for SQLWays Wizard.

As to the files generated for this process, you can see that for tables an SQL file, a DDL file with a commented source and its equivalent in the same file are generated.

There is also a bat file with specification for maintaining the load of definitions and data into the target database.

As to stored procedures and business logic objects, you can see the SQL file that contains the source and its equivalent in one file and the bat file with commands which execute the SQL statement in the SQL file.

This is how Ispirer SQLWays Wizard works. You have seen the files it provides after the conversion process.

Now let’s check the conversion results. Let’s open MariaDB HeidiSQL utility and check whether the objects were converted to MariaDB.

In order to check the validity of the converted business logic we need to run one of the stored procedures in DB2 LUW and MariaDB and compare the results. Let’s choose a stored procedure for this purpose.

The procedure contains DML, cursor statements and control statements.

Let’s run it and check the result. Now let’s run the same stored procedure in the target database. As you can see, the results are the same in both databases. So the conversion has been successful.

Now let’s open Ispirer Migration Commander and Ispirer Migration Studio which are designed to help you with maintaining the conversion of scripts from DB2 to MariaDB.

For example, Ispirer Migration Studio works with one script at a time. Let’s add some SQL code in the left pane. We specify the source and the target and click run. You can see the command line in the raw which is provided here for the conversion from DB2 LUW to MariaDB.

The result of the conversion can be compared in both left and right panes for your convenience.

As to Ispirer Migration Commander, the idea is the same: it converts scripts, but the difference is that it can convert multiple scripts at a time. So for example we’ve got several scripts in our source.

So let’s convert them. You can specify a file or files or the whole directory. Now let’s run the conversion.

You can see that again the same command line is used for maintaining the conversion from one database to the other and you can see how the conversion is performed. Files are converted from DB2 to MariaDB one by one.

Now let’s see the results of the conversion. We can open the SQL file and compare the source and its equivalent.

This is all information about the tools which our company provides for the database migration from DB2 LUW to MariaDB.

It is just a review of the tools and the features that they support, so if you need additional information please contact us, and we will be glad to assist you in your migration projects.

Thank you very much for your attention. Bye.