Introduction

MySQL-to-DB2 is a program to migrate MySQL databases to DB2 server.

Features

  • All MySQL data types and attributes are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Converts individual tables (see Select Tables article for related information)
  • Merges MySQL data into an existing DB2 tables
  • Filter data to convert via SELECT-queries (see Using Queries article for related information)
  • Stores configuration into profile (see Profile article for related information)
  • Command line support (see Command Line article for related information)
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • DB2 environment (server or client components)
  • Necessary privileges to write data on target MySQL server


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be migrated to DB2 server as if it would be a regular table. Select name of table where you wish to store query results in the desintation DB2 database. Enter query in the corresponding field. Also, you can test query on source database by pushing "Test Query" button. If query is not correct please check that it is a correct SQL SELECT-statement and all table and field names are spelled properly. You can always check query by running it from MySQL client.

See Using Queries article for the further information.


Command Line

To perform batch conversion or call conversion procedure from an automation script you can use console version of MySQL-to-DB2 converter called S2BAGENT.EXE. Find this file in the installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--db2pswd=... | DB2 user password
--db2schema=... | DB2 schema name
--db2user=... | DB2 user name
--dest=... | DB2 database name
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process tables that already exist in DB2 database (0 - overwrite, 1 - merge, 2 - skip)
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--port=... | MySQL port
--profile=... | path to the file to load conversion settings from
--silent | use this option to disable program output
--skip_idx | skip converting indexes
--src=... | MySQL database name
--tab_def | convert table definitions only
--tab_file=... | file containing table names to convert (one table name per line)

In the following example the program converts all tables from MySQL database db1 into tablespace "SYSTOOLS" of DB2 database db2_db1:

S2BAGENT.EXE --src=db1 --db2schema=SYSTOOLS --db2user=scott --db2pswd=tiger
--dest=db2_db1 --mysqlh=mysqlhost --mysqlu=administrator --mysqlp=the_passsword

Notes:

  1. If you specified a profile, there is no need to specify any other parameters
  2. The default mode is 'overwrite'. You can change it using '--mode' parameter
  3. You can omit 'mysqlh' parameter to connect local MySQL server
  4. It is necessary to specify 'port' parameter only if it deffers from the default MySQL port 3306
  5. If you omit 'tab_file' parameter, all tables from the specified schema will be converted
  6. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")


Connect to DB2 Server

On this wizard screen you should enter the necessary settings to establish connection with DB2 server.

  • Database - name of the data source as you can see it in standard DB2 tools
  • Username - your DB2 username for the database to which you are connecting
  • Password - your DB2 password for the database to which you are connecting


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. MySQL-to-DB2 is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run MySQL-to-DB2 from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-DB2. See Troubleshooting article to learn how to resolve this problem.


Conversion Settings

On this wizard screen you should select source MySQL database and schema name in the destination DB2 database. Also, you can customize the conversion process using the following settings:

  • Convert table definitions only - select this checkbox to convert only table definitions of the original MySQL database
  • Skip converting indexes - select this checkbox if you don't want to convert indexes

MySQL-to-DB2 allows you to choose how to process MySQL tables that already exist in the destination DB2 database: overwrite, merge or skip converting existing tables.

  • Select 'Overwrite' option to replace the contents of existing DB2 tables with MySQL data.
  • Select 'Merge' option to merge MySQL data into the destination DB2 database. MySQL-to-DB2 implements quite simple merging algorithm. If a table has primary key or an unique index, the program inserts into DB2 database only those records whose key/index value does not exist in the destination DB2 table. Otherwise, MySQL-to-DB2 appends the whole table contents to the corresponding DB2 table. Note: the structures of source and destination tables must be identical.
  • Select 'Skip' option to not convert existing tables.


Profile

Profile is a set of parameters that entirely defines the program session. It includes DB2 connection characteristics (database, schema, user name, password), MySQL database name and other conversion settings. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Select Tables

On this wizard screen you can manage the list of MySQL tables to convert. In the left part of the screen you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new MySQL table for converting into IBM DB2 format you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available MySQL tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.


Troubleshooting

While working with MySQL-to-DB2 converter you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the correct conversion results. This summary is organized as list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: The application failed to start because DB2CLI.dll was not found.
R: DB2CLI.DLL is a part of DB2 server or client components that is required for using MySQL-to-DB2. Check that you have DB2 client or server components installed on the same machine where you run the application from. Also please make sure that the path to DB2 components folder is in $PATH system variable.

P: Error message "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-DB2. You can use OLD_PASSWORD function to make the certain account compatible with MySQL-to-DB2. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with MySQL-to-DB2.

P: Error message "Can't connect to MySQL server on <host name or IP>".
R: Make sure MySQL server is launched on the target machine. Also please check the port number field to verify that you have specified the correct number.

P: I cannot uninstall MySQL-to-DB2.
R: If you need to remove MySQL-to-DB2 product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE sql2db2.log

If you came across some other kind of problem while using MySQL-to-DB2 converter, please contact our support department at service@convert-in.com


Using Queries

Queries give you the ability to extract partial data for migrating to MySQL server. The following examples are provided to illustrate how to use queries for particular purposes. Assume there is table "TABLE1" defined as follows:

   TABLE1(
    ID INTEGER,
    FNAME VARCHAR(50),
    LNAME VARCHAR(50),
    BIRTHDAY DATE
   );

Example 1. Convert certain records.

    SELECT * FROM TABLE1 WHERE ID > 1000;
Example 2. Choose and rename columns.

    SELECT FNAME as FIRSTNAME, LNAME as LASTNAME FROM TABLE1;

Example 3. Skip records containing NULL values.

    SELECT * FROM TABLE1 WHERE LNAME IS NOT NULL;

You can add queries on "Select Tables" wizard screen. See Select Tables article for related information.


Contents

Add Query

Command Line

Connect to DB2 Server

Connect to MySQL Server

Conversion Settings

Introduction

Profile

Select Tables

Troubleshooting

Using Queries