{"id":9957,"date":"2014-08-05T10:00:12","date_gmt":"2014-08-05T08:00:12","guid":{"rendered":"https:\/\/www.credativ.de\/blog\/credativ-inside\/informix-foreign-data-wrapper-for-postgresql\/"},"modified":"2014-08-05T10:00:12","modified_gmt":"2014-08-05T08:00:12","slug":"informix-foreign-data-wrapper-for-postgresql","status":"publish","type":"post","link":"https:\/\/www.credativ.de\/en\/blog\/postgresql-en\/informix-foreign-data-wrapper-for-postgresql\/","title":{"rendered":"Informix Foreign Data Wrapper for PostgreSQL\u00ae<\/sup>"},"content":{"rendered":"
\n
\n
\n

Since PostgreSQL\u00ae<\/sup> 9.1, PostgreSQL\u00ae<\/sup> has implemented an interface for accessing external data sources. The interface defined in the SQL Standard (SQL\/MED) allows transparent access to and manipulation of external data sources in the same manner as with PostgreSQL\u00ae<\/sup>-native tables. External data sources appear as tables in the respective database and can be used without restriction in SQL statements. Access is implemented via a so-called Foreign Data Wrapper (FDW), which forms the interface between PostgreSQL\u00ae<\/sup> and the external data source. The FDW is also responsible for mapping data types or non-relational data sources to the table structure accordingly. This also enables the connection of non-relational data sources such as Hadoop, Redis, among others. An overview of some available Foreign Data Wrappers can be found in the PostgreSQL\u00ae<\/sup> Wiki<\/a>. <\/p>\n

Informix FDW<\/h2>\n

In the context of many Informix migrations, credativ GmbH developed a Foreign Data Wrapper (FDW)<\/strong> for Informix databases. This supports migration, as well as the integration of PostgreSQL\u00ae<\/sup> into existing Informix installations, to facilitate data exchange and processing. The Informix FDW supports all PostgreSQL\u00ae<\/g> versions with SQL\/MED implementation, i.e., from 9.1 up to the current 9.3. Furthermore, with PostgreSQL\u00ae<\/sup> 9.3, the Informix FDW also supports write operations on Foreign Tables. <\/p>\n

Installation<\/h2>\n

The installation requires at least an existing IBM CSDK for Informix. The CSDK can be obtained directly via download<\/g>. In the following, we assume that a CSDK installation exists in \/opt\/IBM\/informix<\/em>. The path may vary depending on the installation. The Informix FDW sources can be cloned directly from the credativ GitHub repository<\/g>, or a release tarball<\/g> can be acquired (version 0.2.1 at the time of this article). The following installation description assumes a system with CentOS 6, but can be performed with variations on any other Linux system. <\/p>\n

\n
% wget 'https:\/\/github.com\/credativ\/informix_fdw\/archive\/REL0_2_1.tar.gz'\n% tar -xzf REL0_2_1.tar.gz<\/pre>\n<\/div>\n
 <\/p>\n
Subsequently, the FDW can be built by specifying the CSDK installation folder:<\/p>\n
\n
% export INFORMIXDIR=\/opt\/IBM\/informix\n% export PATH=$INFORMIXDIR\/bin:$PATH\n% PG_CONFIG=\/usr\/pgsql-9.3\/bin\/pg_config make<\/pre>\n<\/div>\n
 <\/p>\n
In the listing, the environment variable PG_CONFIG <\/em>was explicitly set to the PostgreSQL\u00ae<\/sup> 9.3 installation. This refers to an installation of PGDG-RPM packages on CentOS 6, where pg_config<\/g> is located outside the standard path. Furthermore, the postgresql93-devel <\/g>package must be installed. If all paths have been set correctly, the FDW can be installed.   <\/p>\n
\n
% PG_CONFIG=\/usr\/pgsql-9.3\/bin\/pg_config make install<\/pre>\n<\/div>\n
 <\/p>\n
This installs all necessary libraries. For these to function correctly and for the required Informix libraries to be found when loading into the PostgreSQL\u00ae<\/sup> instance, the latter must still be configured in the system’s dynamic linker. Based on the previously mentioned CentOS 6 platform, this is most easily done via an additional configuration file in \/etc\/ld.so.conf.d <\/em>(this requires root privileges in this case!):  <\/p>\n
\n
% vim \/etc\/ld.so.conf.d\/informix.conf<\/pre>\n<\/div>\n
 <\/p>\n
This file should contain the paths to the required Informix libraries:<\/p>\n
\n
\/opt\/IBM\/informix\/lib\n\/opt\/IBM\/informix\/lib\/esql<\/pre>\n<\/div>\n
 <\/p>\n
Subsequently, the dynamic linker’s cache must be refreshed:<\/p>\n
\n
% ldconfig<\/pre>\n<\/div>\n
 <\/p>\n
The Informix FDW installation should now be ready for use.<\/p>\n
Configuration<\/h2>\n
To use the Informix FDW in a database, the FDW must be loaded into the respective database. The FDW is a so-called EXTENSION<\/strong>, and these are loaded with the CREATE EXTENSION<\/a> command: <\/p>\n
\n
#= CREATE EXTENSION informix_fdw;\nCREATE EXTENSION<\/pre>\n<\/div>\n
 <\/p>\n
To connect an Informix database via the Informix FDW, one first needs a definition of how the external data source should be accessed. For this, a definition is created with the CREATE SERVER<\/a> command, containing the information required by the Informix FDW. It should be noted that the options passed to a SERVER directive depend on the respective FDW. For the Informix FDW, at least the following parameters are required:   <\/p>\n