Re: Writing into Files from Stored Procedures

Oh, ER will indeed work also, but it is only freely available if you are using an Enterprise Edition (though Workgroup Edition can be a node in an ER cluster without additional licensing IB, just not an ER master). If you are running Workgroup Edition you can purchase the ER option and if this is for a short term migration to another platform or machine, you MAY be able to negotiate a low cost temporary license with IBM. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, May 5, 2009 at 2:01 PM, Art Kagel <art.kagel@gmail.com> wrote: > What version of IDS are your running? (It is ALWAYS a good idea to post > your IDS and tools versions and your platform architecture and OS so we can > give you specific advice.) > > If you are running IDS r 11.50 you can take advantage of the new Data > Capture API to basically hook a "C" or Java UDR function into the IDS > logical log subsystem and capture any or all update activity on specific > tables and do whatever you want to with the data - like write it to a file > using "C" or Java features. > > If you are running any 9.xx, 10.00, or 11.xx release you can define an > EXTERNAL table as an OS flat file and have the triggers you propose insert > the changes into the external table. > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Oninit, the IIUG, nor any other > organization with which I am associated either explicitly or implicitly. > 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 Tue, May 5, 2009 at 11:32 AM, <marc.aurele.severus@gmail.com> wrote: > >> >> > >> > There isn't any particularly easy way to do that. >> > >> > As Enrique suggested, if you aren't dealing with a temporary table, >> > you can use the SYSTEM statement to run, say, dbaccess and unload the >> > data that way. It isn't very elegant; it won't be very fast. And are >> > you sure you want to trigger this every time someone selects from the >> > table? What do you envisage as the action that triggers your >> > triggered stored procedure? >> > >> >> >> I'd like to synchronise two very specific databases on two different >> servers. Network communication is not reliable, and network may >> sometimes be off. >> >> This synchronisation is a temporary solution (one or two weeks), so we >> cannot acquire an expensive software to do this. >> >> My idea : I put a trigger on INSERT, UPDATE, or DELETE statements in >> the first database. When someone inserts / updates / deletes data in >> the first database, the trigger exports the values into a file. When >> the network is available, the file is copied to the second server by >> FTP, and the second database is updated with the data. >> >> I guess there's probably a less dirty way to do this, but don't forget >> this is just a temporary solution. >> >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> > >

Tanks a lot for your answers. I'm running version 9.30 on AIX 5.1. I forgot to add a piece of information : the source database and the destination database don't have exactly the same format. A conversion will be necessary : some fields will be discarded ; some fields will be merged ; some other fields will be added. So I'm not sure to be able to use ER or MQ .....

Then you might want to look at Pentaho Kettle. Depending on how often the other database needs to sync, you could use it in a cron job. Thank you, Jim Goldrick Judson University 1151 North State Street Elgin, Illinois 60123 573-332-7739 http://www.judsonu.edu jgoldrick@judsonu.edu Tech Services on the Web -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of marc.aurele.severus@gmail.com Sent: Wednesday, May 06, 2009 4:04 AM To: informix-list@iiug.org Subject: Re: Writing into Files from Stored Procedures Tanks a lot for your answers. I'm running version 9.30 on AIX 5.1. I forgot to add a piece of information : the source database and the destination database don't have exactly the same format. A conversion will be necessary : some fields will be discarded ; some fields will be merged ; some other fields will be added. So I'm not sure to be able to use ER or MQ ..... _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

ER can definitely handle most ETL needs when you define the replicant. MQ is just a data queue for IPC. Whatever app you place at the other end of the queue will have to perform the data manipulation to insert the data in the target. Only problem is the MQ Datablade may not be certified for IDS 9.30, IB it first came out for 9.40. You are aware that 9.30 is very out-of-date and no longer supported by IBM? You should be upgrading to 11.50 (or at least 10.00) but maybe that's what this is all about. If so you could run the MQ datablade on the target server and write a C UDR for the triggers to call to place data onto the MQ for consumption by the target. More ideas.... Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, May 6, 2009 at 5:03 AM, <marc.aurele.severus@gmail.com> wrote: > Tanks a lot for your answers. > > I'm running version 9.30 on AIX 5.1. > > I forgot to add a piece of information : the source database and the > destination database don't have exactly the same format. A conversion > will be necessary : some fields will be discarded ; some fields will > be merged ; some other fields will be added. > > So I'm not sure to be able to use ER or MQ ..... > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >