Re: [GENERAL] Accessing DB2 tables from postgresql - Mailing list pgsql-general

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