Postgres Pro
Downloads
Home   >   mailing lists

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

From Swapnil Vaze
Subject Re: [GENERAL] Accessing DB2 tables from postgresql
Date
Msg-id CABWf+yR+qtXoNmDARyo7X+8o3r6gKyovb=acmzT21M-j+ksscA@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Accessing DB2 tables from postgresql  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: [GENERAL] Accessing DB2 tables from postgresql
List pgsql-general
Tree view
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');

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

Does ODBC wrapper support DB2 access?

Thanks,
Swapnil Vaze

On Tue, Jun 27, 2017 at 6:38 PM, Adrian Klaver <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

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



--
Thanks & Regards,
Swapnil Vaze

pgsql-general by date:

Previous
From: Daniel Westermann
Date:
Subject: Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush?
Next
From: marcelo
Date:
Subject: [GENERAL] iso-8859-1 lvalue 'PIDFILE' in section 'Service'