Export informix data to mysql

I need to migrate the informix DB to mysql. However, the database schemas of the source/destination are slight different. (2 tables from informix are merged into 1 in mysql)
informix table1 informix2 table2 -> mysql table3

WHat i am trying to do is to export the informix data into insert script, execute the scrip to create the data of the 2 tables in mysql. The run a final scipt which joins the data of the 2 to insert the data to the final table3.
Can anyone let me know how i can achieve that?
martieAsked:
Who is Participating?
 
gheistConnect With a Mentor Commented:
ISAM is lightning fast in Informix, UNION is not.

SELECT ... INTO FILE /data.dump

works, but since asker tells he understands MySQL - he can join tables right inside that, and spend less time walking around unknown land...
0
 
artmsCommented:
If i'm not making mistake, Informix should support views, if that so, than simple create a view, which has all needed tables joined, create table in mysql (which will have all needed columns), export data from informix view and insert it into mysql. If it is not possible than you will have to write some script php/perl/another programming language which will help joining the data. I see the way how to do it is:
1. Import those two tables into mysql
2. Create third table with joined columns
3. Execute script which will take data from two tables joined and insert it into third table. Please also try this construction

insert into some_table(column1,column2) select a.*, b.* from table1a join table2 b on a.bid=b.id...

More about insert-select sentences who can find here: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
0
 
martieAuthor Commented:
Hi Artms, thanks for your suggestion. What i am concerned is how i can "export data from informix view and insert it into mysql."
i know dbexport can export the data into a file but i think export to an insert script is more portable. can you tell me the right way to achieve that? thanks.
0
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!

 
artmsCommented:
I will not be able to help with informix itself, please wait, maybe some informix expert will help you with your problem.
Mysql allows to export from view like from simple table, but I know no info about informix.
0
 
gheistCommented:
0
 
martieAuthor Commented:
I checked out the ontape but not able to use SQL text. Could you please provide some more examples? say, export a table called employee, of of the database test from server  dev? thanks.
 i am really new to Informix :(
0
 
gheistCommented:
Kind of you have the way to get SQL for whole informix. It has no option to handle tables this way. SELECT ... INTO can handle tables, but since you have all the world dominating MySQL at hands why do you need lowlife informix to do something extra...
0
 
martieAuthor Commented:
Sorry I do not understand. In the first place, how can I export the table from informix to mysql? what command/utility should I use? thanks.
0
 
gheistCommented:
You can export full database into text file containing SQL commands.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.mig.doc/mig136.htm

It does not filter tables nor does change SQL syntax to make MySQL happy. You have to use sed and awk to remove informix-specific SQL tags.

0
 
unique12uConnect With a Mentor Commented:
I would simply do an unload to <filname> select ..... from table1,table1 where ........

This will create a pipe delimited text file on your informix server that you can then ftp over to your MySQL and import in using and insert
0
 
martieAuthor Commented:
i am able to export the  data from informix to a file to a unl but then how can i import the data? thanks for your help.
0
 
gheistCommented:
You claimed MySQL expertise, not me.
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.

All Courses

From novice to tech pro — start learning today.