Multi language support while migrating from db2 to mysql

Himakshi Mangal
Himakshi Mangal used Ask the Experts™
on
I have a database in db2 that needs to be migrated to mysql. I ma expoting data from db2 to csv and then csv to msql. the data is in multiple langugaes like Chinese, French, Latin, Hebrew, etc. Due to character set issues, the data is converted into symbols. How shall I ensure that the import is done correctly without any changes in the data
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator / Software Engineer
Commented:
Hi!

Is each language in separate tables in DB2 or is the data mixed in the tables ?
If the former then you have each table exported into individual files each with their language.
Then you could use the LOAD command in the MySQL with the correct CHARACTER SET that belongs to the file/data.
Also make sure that the tables in MySQL are properly configured to the same CHARACTER SET that belongs to the data as well.
https://dev.mysql.com/doc/refman/5.7/en/load-data.html
https://dev.mysql.com/doc/refman/5.7/en/charset-table.html
Here is an example
LOAD DATA LOCAL INFILE '/path/to/file.csv'
  INTO TABLE imported_table
  CHARACTER SET 'latin1'
  COLUMNS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  ESCAPED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

Open in new window


https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html

Regards,
    Tomas Helgi
David FavorFractional CTO
Distinguished Expert 2018
Commented:
Tomas asked my first question too.

If each language lives in a separate table, then you'd load each table into MySQL (or far better MariaDB) using fixed/forced/explicit charsets.

If all languages live in the same table, whew... This will require a very different approach, which depends on how data will be searched for after it's loaded into MySQL.

Seriously, before you do this, best look at MariaDB, as there are many language processing fixes in MariaDB.

Many Distros (Debian/Ubuntu/etc) have set MariaDB to be installed anytime a MySQL package target is specified now.

If your language data is commingled in one table, this might work for you...

pv your.sql | mysql --defaults-extra-file=/etc/mysql/debian.cnf --default-character-set=utf8 your-empty-database

Open in new window


Again, this all depends on incoming data format (how your.sql file is dumped) + how data will be used in MySQL.

If your data is commingled, might be best to open a Gig + look for someone who works with multi-lingual + commingled data on a daily basis.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Poster never provided feedback + has stopped posting.

Accepting both good approaches to resolving this problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial