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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
unique12uCommented:
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
gheistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.