Data movement from Informix to MySQL

Hi All, I have a requirement to transfer data from Informix table(A) to mySQL table(B). Table A column name are different from that of table B. Also selective columns are to extracted from table A and pushed into table B. i would like to know if: 1. such type of data movement is possible or not. 2. What are the utilities that will be required in informix and mysql to fullfil this requirement. 3. How this data movement has to be done? what will be the statements like? 4. What are the precautions to be taken for such movements. Will it be affecting the performance of the tables. Informix Dynamic Server Version 9.40.UC5 and mysql Ver 14.7 Distrib 4.1.10a. Thanks in advance. Regards, Sushant

Hi Sushant, I dont think there is an official utility to do this. It depends on your needs how the data should be transferred: - a complete dump once or e.g. every night - online for each record which is inserted/modified/deleted in Ifx Taking the first choice, I would make a dbaccess script run by a cron to export (unload) the data in delimited format and then load it in mysql using command line utility. For the second case, You would need to install a trigger, either inserting change notifications in a separate table, which will be base for synchronization and write a program to process the table (Java or C e.g), reading records and writing to mysql, marking as processed after operation succeeds. This would copy the data with a minimum time difference. Or trigger a Java/C stored procedure (not sure if Java was supported in 9.40), which will do the job directly using a JDBC connection to mysql. That solution would keep the tables identical all the time, but is more error prone, cause it assumes that always both instances are online. Hope this helps, Marcus ----- Ursprüngliche Mail ----- Von: "SUSHANT KODE" <ksushants@gmail.com> An: ids@iiug.org Gesendet: Mittwoch, 5. Dezember 2012 09:06:42 Betreff: Data movement from Informix to MySQL [29019] Hi All, I have a requirement to transfer data from Informix table(A) to mySQL table(B). Table A column name are different from that of table B. Also selective columns are to extracted from table A and pushed into table B. i would like to know if: 1. such type of data movement is possible or not. 2. What are the utilities that will be required in informix and mysql to fullfil this requirement. 3. How this data movement has to be done? what will be the statements like? 4. What are the precautions to be taken for such movements. Will it be affecting the performance of the tables. Informix Dynamic Server Version 9.40.UC5 and mysql Ver 14.7 Distrib 4.1.10a. Thanks in advance. Regards, Sushant ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

A continuous data movement or a one-shot copy? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 5, 2012 at 3:06 AM, SUSHANT KODE <ksushants@gmail.com> wrote: > Hi All, > > I have a requirement to transfer data from Informix table(A) to mySQL > table(B). Table A column name are different from that of table B. Also > selective columns are to extracted from table A and pushed into table B. i > would like to know if: > 1. such type of data movement is possible or not. > 2. What are the utilities that will be required in informix and mysql to > fullfil this requirement. > 3. How this data movement has to be done? what will be the statements like? > 4. What are the precautions to be taken for such movements. Will it be > affecting the performance of the tables. > > Informix Dynamic Server Version 9.40.UC5 and mysql Ver 14.7 Distrib > 4.1.10a. > > Thanks in advance. > > Regards, > Sushant > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec5171a479811c704d019b3e4

This is on time movement.

You can unload the data to flat files using the dbaccess unload verb if it is a single table: dbaccess mydatabase - <<EOF unload to somefile.unl delimiter "|" select * from sometable;EOF Then you will have to use some mysql tool to import the data. As far as the different data types, you may be able to cast the Informix column value to a compatible type in the SELECT statement you use to perform the unload using CASTs or Informix's built-in conversion functions if mysql cannot import the unloaded strings as is. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 5, 2012 at 7:00 PM, SUSHANT KODE <ksushants@gmail.com> wrote: > This is on time movement. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f23450766079204d023f63f