[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2517
  • Last Modified:

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?
0
martie
Asked:
martie
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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