Thread: [GENERAL] Accessing DB2 tables from postgresql

Hello,

I am trying to access few table present in DB2 LUW from postgres9.5 database.

I have installed unixODBC driver and connection to DB2 is working fine.

I have installed CartoDB/odbc_fdw foreign data wrappers.

I have user below commands to create foreign table:

$ create extension odbc_fdw;

$ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2 options(odbc_UID '<username>', odbc_PWD '<password>');

$ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS (database 'TESTV9', schema 'schema1', table 'table1' );

All commands work fine, however when I try to select data from table it throws error:

$ select * from odbc_table;

ERROR:  Executing ODBC query

Can anyone help me here? 

How can I access DB2 LUW or zOS database tables from postgres?

--

Thanks & Regards,

Swapnil Vaze

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 06/27/2017 03:11 AM, Swapnil Vaze wrote:
> Hello,
>
> I am trying to access few table present in DB2 LUW from postgres9.5
> database.
>
> I have installed unixODBC driver and connection to DB2 is working fine.
> I have installed CartoDB/odbc_fdw foreign data wrappers.
> I have user below commands to create foreign table:
> $ create extension odbc_fdw;
> $ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2
> options(odbc_UID '<username>', odbc_PWD '<password>');
> $ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD
> VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS (database
> 'TESTV9', schema 'schema1', table 'table1' );
>
> All commands work fine, however when I try to select data from table it
> throws error:
> $ select * from odbc_table;
> ERROR:  Executing ODBC query
>
> Can anyone help me here?

Might want to take a look at:

https://github.com/CartoDB/odbc_fdw

 From  the examples above you need to prefix some settings with odbc_.
In your example that would be odbc_database 'TESTV9' instead of database
'TESTV9'.

> How can I access DB2 LUW or zOS database tables from postgres?
>
> --
> Thanks & Regards,
> Swapnil Vaze


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/28/2017 01:28 AM, Swapnil Vaze wrote:
> Hello,
>
> I dropped and recreated foreign table with odbc_database option. Also
> tried to use import foreign schema object still getting same error.
>
> CREATE FOREIGN TABLE
>    odbc_table (
> CTGRY_ID INTEGER,
> CTGRY_CD VARCHAR(10),
> UPDT_TS TIMESTAMP
>    )
>    SERVER odbc_server
>    OPTIONS (
>      odbc_database 'TESTV9',
>      schema 'u90nmqd',
>      sql_query 'select CTGRY_ID,CTGRY_CD,UPDT_TS from `schema1`.`table1`',
>      sql_count 'select count(CTGRY_ID) from `schema1`.`table1`'
>    );
>
> I have created server with below definition:
> CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn
> 'TESTV9');

So do you have a DSN named 'TESTV9' on the system you are connecting from?

If not you will need to use a driver name instead of DSN.

>
> To test for other DBMS, I created foreign object for postgres with
> odbc_fdw, however it is throwing too long encoding error.

What is the exact error mesage?

>
> Does ODBC wrapper support DB2 access?

That would seem to depend on whether you have a DB2 ODBC driver present
on your machine:

https://github.com/CartoDB/odbc_fdw

"To make use of the extension ODBC drivers for the data sources to be
used must be installed in the system and reflected in the
/etc/odbcinst.ini file."

See also:

https://github.com/CartoDB/odbc_fdw/issues/45

I would ask there, by either responding to existing issue or starting a
new issue.


>
> Thanks,
> Swapnil Vaze
>
> On Tue, Jun 27, 2017 at 6:38 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 06/27/2017 03:11 AM, Swapnil Vaze wrote:
>
>         Hello,
>
>         I am trying to access few table present in DB2 LUW from
>         postgres9.5 database.
>
>         I have installed unixODBC driver and connection to DB2 is
>         working fine.
>         I have installed CartoDB/odbc_fdw foreign data wrappers.
>         I have user below commands to create foreign table:
>         $ create extension odbc_fdw;
>         $ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2
>         options(odbc_UID '<username>', odbc_PWD '<password>');
>         $ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD
>         VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS
>         (database 'TESTV9', schema 'schema1', table 'table1' );
>
>         All commands work fine, however when I try to select data from
>         table it throws error:
>         $ select * from odbc_table;
>         ERROR:  Executing ODBC query
>
>         Can anyone help me here?
>
>
>     Might want to take a look at:
>
>     https://github.com/CartoDB/odbc_fdw
>     <https://github.com/CartoDB/odbc_fdw>
>
>     From  the examples above you need to prefix some settings with
>     odbc_. In your example that would be odbc_database 'TESTV9' instead
>     of database 'TESTV9'.
>
>
>         How can I access DB2 LUW or zOS database tables from postgres?
>
>         --
>         Thanks & Regards,
>         Swapnil Vaze
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> Thanks & Regards,
> Swapnil Vaze


--
Adrian Klaver
adrian.klaver@aklaver.com