Migrating from MaxDB(SapDB) to MySQL

I spent some time to migrate data from MaxDB to MySQL today. I googled a bit, but nothing seems working for me. So I have to do it in my way. I've been working with SapDB and MaxDB for a while, I am pretty happy with it. The performance is very good, and the maintenance is easy too. The reason I have to migrate is because I want to give DataMapper(Ruby) + Merb a spin. There is no adapter that are satisfying my need right now, and on the other hand, I am really too busy to write one by myself. Since I am pretty familiar with MySQL, I decided to migrate our MaxDB production database server to MySQL, just for testing for the first phase, we have too many lines of Java code to be migrate too.

Here are the steps, I am hoping this is helpful if you have the same situation like mine.

1. Export MaxDB schema(catalog) and data

a. Schema

/opt/sdb/programs/bin/loadercli -b export_catalog.cmd -d MYPRODUCTIONDB -u MYDBADMIN,MYPASSWD -E 20

the export_catalog.cmd looks like:

EXPORT DB

CATALOG OUTSTREAM 'MYPRODUCTIONDB.ddl'

b. Data

/opt/sdb/programs/bin/loadercli -b export_data.cmd -d MYPRODUCTIONDB -s PRODUCTION_SCHEMA -u USER,PASSWD -E 20 -cs \;

the export_data.cmd looks like:

EXPORT TABLE PRODUCTION_SCHEMA.PURCHASE_ORDERS

DATA OUTSTREAM 'PURCHASE_ORDERS.data';'

EXPORT TABLE PRODUCTION_SCHEMA.PURCHASE_ORDER_LINE_ITEMS

DATA OUTSTREAM 'PURCHASE_ORDER_LINE_ITEMS.data';

...

2. Modify the MaxDB schema to satisfy MySQL grammar, and import into MySQL server.

There are some different between MaxDB and MySQL, you have to modify before you can import the ddl file into MySQL. You have to be very careful about  DEFAULT SERIAL (1)/auto_increment, Integer/INT, fixed/decimal, timestamp...

a. Create db in MySQL

$mysql -h hostname -uroot -pPasswd

>create database MYPRODUCTIONDB character set utf8;

>quit

b. Create schema

$mysql -h hostname -uroot -pPasswd MYPRODUCTIONDB < MYPRODUCTIONDB.ddl

3. Format data before you import into mysql

Because I have too many tables(200+), I write a simple shell script to do that for me

format.sh

#!/bin/bash

for file in *.data; do

 vim -e -s $file < format.vim

done

format.vim

:%s/?/NULL/g

:%s/FALSE/0/g

:%s/TRUE/1/g

w

q

This script will format all the MaxDB data file(*.data) in current folder with the vim. It depends on your data, this could be very time consuming, on my 64-bit RH with 16G memory, it took about 4 hours to get this done. So grab a cup of coffee, chat with your favorite girl in office ...

4. Import data into mysql

Now it is time to get the data imported, for the same reason, I am using a shell script

import.sh

#!/bin/bash

for file in *.data; do

 mysqlimport  -h hostname -uroot -pPasswd --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --local MYPRODUCTIONDB $file

done

That is pretty much all of the steps. If you have some columns in your MaxDB with NULL value, you might have to dump the database from mysql again, substitute them all, then import back again.