Porting data from DB2 to MySQL

It happens on job that creating/dropping tables on a database (even an instance for developing) could take a long time due to bureaucratic procedures: send email with required table to create, wait until approval, wait until creation. Populate the table do the test and if something is wrong send a mail for the table dropping and repeat the cycle.

In order to exit this hypnotic loop, I have a MySQL on my computer and I use it for every test. Then when I’m sure of the table structure send the email and wait.

But what when you have to create a table that you have to test in join with other tables? The main problem should be to get a script for creating the table and the data for populating on the MySQL.

Getting the create table script

In order to get the create table script from the DB2 I use the client I’m used to use for accessing database: eclipse + SQLExplorer. Select the table you want the create script to be created in the database structure window and with a right click use the create table script. This will open you a new SQL editor with the create table script for DB2. Now fix it in order to accomplish the MySQL syntax. Actually I had to remove the Db2 owner, the double quotes from the table name and replace the double single quotes with a single single quote (script adjustment). Now run the tuned script on the instance of MySQL (always from SQLExplorer).

Getting the data

Now it’s time to get the data from DB2 table in order to pass it to the MySQL. With a simple

select * from owner.table

ensuring to uncheck the limit rows option of SQLExplorer, you’ll get in the SQLResult window a table with all the data. Right click on a cell, and choose the export->csv. This will generate a text file with all the fields separated by a semicolon (;). Let’s fix the file in order to get it digestible to MySQL; from command line

$ sed 's/;/\t/g' exported.csv > exported.txt

This will replace all the semicolons to a tab character.

Import data into MySQL

Now with a mysql access via command line, with root user (or with enough privileges) use the following command

load data infile 'exported.txt' into table the_table_you_created;

That’s all folks.

Advertisements

3 thoughts on “Porting data from DB2 to MySQL

    • thanks Martin,

      however I have some problems as I have no rights for access production databases. So unfortunately I cannot use the ESF toolkit. I still have to work with Mb of txt files.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s