Re: Accessing DB2 tables from postgresql

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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>
>
> --
> Thanks & Regards,
> Swapnil Vaze

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com