Foreign Data Wrapper(s)
Answered: red (hollow confidence) — The question of getting postgres_fdw to pass an Informix server name was not answered; replies suggested linking SQL Server directly to Informix and advertised multi-DBMS 4GL products.
Advisory only.
Posted in 2020
Adam Reynolds, doing an ETL from SQL Server into Informix, wanted to use PostgreSQL foreign data wrappers but could not pass the Informix server name via postgres_fdw. Doug Lawry suggested linking SQL Server directly to Informix. Sebastien Flaesch and Eric Vercelletto suggested Genero BDL and Querix Lycia; Eric favoured a built-in engine feature. No FDW solution was given.
Topics: Connectivity: ESQL/C, 4GL & Embedded SQL
Throwing this out there for some help. I am doing an ETL from SQLServer into IFX (versions are irrelevant). Because IFX has no foreign data wrapper option (AFAIK), I need to use PostgreSQL as the intermediate engine to stick things together. With this, I can set up a FDW to SQLServer, and to IFX. However, the PostgreSQL FDW extension (postgres_fdw) pushes host, port, dbname, but I cannot find a way to push servername. Has anyone had any success with this? Any hints? Thanks in advance... ------------------------------ Adam Reynolds ------------------------------ #Informix
Hi Adam. Why not link SQL Server directly to Informix? https://www.oninitgroup.com/faq-items/making-microsoft-sql-linked-server-and-informix-work ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Hello Adam, I guess there will be data type compatibility issues and certainly some transformation rules to implement. Maybe use an easy to learn programming language with integrated SQL syntax? You may want to have a look at Genero BDL. Our solution allows you to connect to SQL Server and Informix from the same program, load rows one by one or into an array, write code to transform data, and then insert into Informix. You will also learn about SQL Server / Informix differences in our documentation. If you choose us we can also help on this migration process.. Check www.4js.com Seb ------------------------------ Sebastien FLAESCH ------------------------------
Since this forum is used for third party product advertising, I cannot help myself to mention Querix Lycia which also has the capacity to address multi DBMS vendor from the same program. Effectively addressing your issue using a language that can address multiple dbms can be one solution for a specific need, although I would also they that other languages can do it, such as Perl with DBD or JavaScript with the Informix driver: not forcedly needing to pay for that And just in case you don't want to spend N times more to have less functionality, performance and versatility, it would be an error to seriously evaluate that product that many of us fell in love with. But this is more about operating a real digital transformation of your 4GL application if you have one. I won't sell you anything, I am just suggesting for the sakes of enriching your knowledge You may want to pay a visit here https://querix.com/products/lycia But sincerely, it would be better to have a built-in functionality in IFMX engine that allows direct connection with other engines: I know this has been considered some time ago, and we should definitely push this request on https://ibm-data-and-ai.ideas.aha.io/?project=INFX ( and check if there is a RFE for that) Because it makes a lot of sense Best regards, Eric Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org