The MySQL and DB2 database management systems offer a range of useful features such as multiplatform support, performance optimization, and reliability. However, as the complexity of the database increases, the cost of database administration can exceed the cost of the hardware and software. This is why migrating from MySQL to IBM DB2 RDBSM can be a viable solution. IBM DB2 automates administration tasks, enhances storage efficiency, and simplifies virtual appliance deployment, resulting in a reduction in data management costs and decreased TCO.
The process of migrating a database from MySQL to DB2 typically involves the following basic steps: porting the database structure, migrating data, and loading the resulting data into the DB2 tables. Porting the database structure requires exporting the DDL from MySQL, converting it to DB2 syntax, and creating the DB2 database with all tables. Migrating data involves exporting MySQL data into CSV files, converting it into a format that can be imported into DB2, and loading the resulting data into the DB2 tables.
To export DDL from MySQL, the mysqldump utility can be used:
mysqldump –host {IP address or network name of MySQL server} –user {MySQL user name} –password –no-data {database_name} > {database_name.sql}
The DDL SQL statement must then be modified to conform to DB2 syntax, and appropriate words must be substituted to ensure it is accepted by the destination DBMS. The modified DDL statement can be loaded onto the destination server to create database objects using the IBM command line processor (CLP).
To export MySQL data into CSV files, the appropriate statement must be executed for each database table:
SELECT {column1}, {column2}, …
UNION ALL
SELECT *
FROM {tablename}
INTO OUTFILE “{path to CSV file}”
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n
Attention must be paid to specific data types such as DATETIME and TIMESTAMP when converting MySQL data into a format that can be imported into DB2. The resulting CSV files can be imported into DB2 using the DB2 import statement:
db2 LOAD from {csv file name} |
of DEL
modified by
coldel0x09
timestampformat=\” YYYY-MM-DD HH:MM:SS\”
insert into {schema name}.{table name}
Migrating a database from MySQL to DB2 is a complex and tedious task that should not be done manually. There are many tools available to automate the process, such as Intelligent Converters, a company specializing in database conversion, migration, and synchronization since 2001.
Their MySQL to IBM DB2 converter provides the following advantages:
- All versions of IBM DB and MySQL are supported including DBaaS could variations
- High performance of the migration since the tool does not use ODBC or any other middleware software
- MySQL schemas, data, indexes, constraints and foreign keys are migrated
- Migrated data can be filtered via SELECT-queries
- Migration settings are stored into profile to simplify next use
- Command line support allows scripting and scheduling the database migration
