• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 81
  • Last Modified:

Multi language support while migrating from db2 to mysql

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
0
Himakshi Mangal
Asked:
Himakshi Mangal
  • 2
2 Solutions
 
Tomas Helgi JohannssonCommented:
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
1
 
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
0
 
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Poster never provided feedback + has stopped posting.

Accepting both good approaches to resolving this problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now