title: What is the simplest way to migrate data from MySQL to DB2
body: <p>I need to migrate data from MySQL to DB2. Both DBs are up and running.</p>
  
  <p>I tried to <code>mysqldump</code> with <code>--no-create-info --extended-insert=FALSE --complete-insert</code> and with a few changes on the output (e.g. change ` to <code>"</code>), I get to a satisfactory result but sometimes I have weird exceptions, like 
  <code>does not have an 
  ending string delimiter.  SQLSTATE=42603
  </code></p>
  
  <p>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: </p>
  
  <pre><code>db2 =&gt; 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.
  </code></pre>
  
  <p>Its MySQL counterpart being</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>Let's assume the DB2 and MySQL databases are called <code>mydb</code>.</p>
  
  <p>Now, if I do </p>
  
  <pre><code>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
  </code></pre>
  
  <p>, I get:</p>
  
  <pre><code>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,'" "" '' ''''  \"\"  ')
  </code></pre>
  
  <p>This ouput can be used as a DB2 query and it works well.</p>
  
  <p>Any idea on how to solve this more efficiently/generally? Any other suggestions?</p>