What is the simplest way to migrate data from MySQL to DB2

I need to migrate data from MySQL to DB2. Both DBs are up and running.

I tried to mysqldump with --no-create-info --extended-insert=FALSE --complete-insert and with a few changes on the output (e.g. change ` to "), I get to a satisfactory result but sometimes I have weird exceptions, like does not have an ending string delimiter. SQLSTATE=42603

Ideally I would want to have a routine that is as general as possible, but as an example here, let's say I have a DB2 table that looks like:

db2 => describe table "mytable"

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
id                              SYSIBM    BIGINT                       8     0 No    
name                            SYSIBM    VARCHAR                    512     0 No    

  2 record(s) selected.

Its MySQL counterpart being

mysql> describe mytable;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name  | varchar(512) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Let's assume the DB2 and MySQL databases are called mydb.

Now, if I do

mysqldump -uroot mydb mytable --no-create-info --extended-insert=FALSE --complete-insert | # mysldump, with options (see below): # do not output table create statement # one insert statement per record# ouput table column names
sed -n -e '/^INSERT/p' |     # only keep lines beginning with "INSERT"
sed 's/`/"/g' |              # replace ` with "
sed 's/;$//g' |              # remove `;` at end of insert query
sed "s/\\\'/''/g"            # replace `\'` with `''` , see http://stackoverflow.com/questions/2442205/how-does-one-escape-an-apostrophe-in-db2-sql and  http://stackoverflow.com/questions/2369314/why-does-sed-require-3-backslashes-for-a-regular-backslash

, I get:

INSERT INTO "mytable" ("id", "name") VALUES (1,'record 1')
INSERT INTO "mytable" ("id", "name") VALUES (2,'record 2')
INSERT INTO "mytable" ("id", "name") VALUES (3,'record 3')
INSERT INTO "mytable" ("id", "name") VALUES (4,'record 4')
INSERT INTO "mytable" ("id", "name") VALUES (5,'" "" '' ''''  \"\"  ')

This ouput can be used as a DB2 query and it works well.

Any idea on how to solve this more efficiently/generally? Any other suggestions?

Solution

After having played around a bit I came with the following routine which I believe to be fairly general, robust and scalable.

1 Run the following command:

mysqldump -uroot mydb mytable --no-create-info --extended-insert=FALSE --complete-insert | # mysldump, with options (see below): # do not output table create statement # one insert statement per record# ouput table column names
sed -n -e '/^INSERT/p' |     # only keep lines beginning with "INSERT"
sed 's/`/"/g' |              # replace ` with "
sed -e 's/\\"/"/g' |         # replace `\"` with `#` (mysql escapes double quotes)
sed "s/\\\'/''/g" > out.sql  # replace `\'` with `''` , see http://stackoverflow.com/questions/2442205/how-does-one-escape-an-apostrophe-in-db2-sql and  http://stackoverflow.com/questions/2369314/why-does-sed-require-3-backslashes-for-a-regular-backslash

Note: here unlike in the question ; are not being removed.

2 upload the file to DB2 server scp out.sql user@myserver:out.sql

3 run queries from the file db2 -tvsf /path/to/query/file/out.sql