Data migration tool - Part III: from DB2 to IRIS

This is the third part of the series of articles on migrating from the main databases on the market to InterSystems IRIS. In this part, the procedures for migrating from DB2 will be detailed.As described in previous articles, there are currently a few options to do the migration. However, the two most popular options include the usage of DBeaver (https://openexchange.intersystems.com/package/DBeaver) or SQLGateway. The first one will be demonstrated in this article, and the second one is presented in an excellent article by Robert Cemper, DB Migration using SQLgateway (https://community.intersystems.com/post/db-migration-using-sqlgateway).

Get the sample data for the migration process

In Github it is possible to download a docker-compose project to build and run 2 databases:

• Source Database: DB2 database Docker instance with a sample database.
• Target Database: InterSystems IRIS data platform Docker instance with a ready schema to receive the source database.

To get the sample and run it, follow these steps:

1. Go to the git repository: https://github.com/yurimarx/migration-db2-iris.
2. Clone the project: git clone https://github.com/yurimarx/migration-db2-iris.git.  
3. Go to the project folder migration-db2-iris.
4. Do the build: docker-compose build.
5. Execute the containers: docker-compose up -d.
6. Check in your docker desktop with the instances if everything is ok:

About the data to be migrated

In the first two parts, we worked with a sales database. However, the DB2, when installed, goes with a sample database (it is a more complete sales database), and we will use it in this article. The data to be migrated is presented here:

So, the migration process from DB2 to IRIS will include 22 tables.
The migration destination will be dc_test schema inside USER namespace in the InterSystems IRIS database.

Open-source tool to migrate from DB2 to IRIS: DBeaver

DBeaver is a database tool to connect, create, drop, select, update and delete data objects when work with the main database products in the market. Download it from: https://openexchange.intersystems.com/package/DBeaver. Now follow the installation instructions to get this fantastic product into your laptop or desktop.
DBeaver can be used to migrate data between database connections, even if they come from different manufacturers and versions.

Connecting the Source and Target Databases using DBeaver

Now we will set the database connections to be migrated.
To set DB2 connection to DBeaver:
1.    Before connecting DB2 for the first time, wait for 5 to 10 minutes. This is the time needed for the DB2 script to construct the sample database after the docker instance creation.
2.    In DBeaver Go to File > New.
3.    Select Database Connection and click Next:

4.    Choose SQL tab > DB2 LUW and click next:

5.    Fill the DB2 connection fields as shown in this picture:

To set InterSystems IRIS connection to DBeaver:
1.    In DBeaver Go to File > New.
2.    Select Database Connection and click Next:

3.    Choose SQL tab > InterSystems IRIS and click next:

4.    If DBeaver requests to download the InterSystems IRIS driver, press Yes or Ok.
5.    Set the InterSystems IRIS connection fields as demonstrated in this picture:

The connections (sample and user) are available in the Database Navigator:

Do the migration

To do the migration, follow these steps:
1.    Expand the sample connection (DB2 connection) > public and select all tables. Click on the selected tables with the right mouse button and choose Export Data, as displayed in this picture:

2.    Select Database, as indicated in this picture and click Next:

3.    Click the Choose button:

4.    Select dc_test and click Ok.

5.    Now it is necessary to change some data type configurations for the target database because the IRIS and DB2 use different data types to store XML values.
6.    Expand DB2INST1.CATALOG table, select the CATALOG field (it is an XML field type) and click Columns…

7.    Change the Target Type from LONGVARBINARY to VARCHAR(10000) and click Ok.  

8.    Repeat the process with the tables
a.    CUSTOMER, fields INFO and HISTORY.
b.    PRODUCT, field DESCRIPTION.
c.    SUPPLIERS, field ADDR.
d.    PURCHASEORDER, field PORDER.
9.    Now with the Target Data Types changed, click Next.

10.    Set Fetch size to 1000000 and click Next.

11.    Accept the default values in the Data load settings and click Next.

12.    In the Confirm click Proceed.

13.    Now you can see all DB2 Tables inside InterSystems IRIS dc_test schema in the Database Navigator.

The migration process was very simple for tables. However, for views, functions, triggers and stored procedures, you will need to rewrite the SQL source code using ObjectScript or SQL.