Accessing DB2 tables from postgresql

Started by Swapnil Vazealmost 9 years ago6 messagesgeneral

swapvaze28@gmail.com

almost 9 years ago

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

achill@matrix.gatewaynet.com

almost 9 years ago

In reply to: Swapnil Vaze (#1)

Re: Accessing DB2 tables from postgresql

On 27/06/2017 13:11, 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' );

You have omitted the CREATE SERVER command. Can you query the mainframe using isql ?

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?

I used to work with MVS many years ago. Good luck with your project.
For better diagnosis open all logs in both machines (postgresql, odbc, MVS, DB2) and have a detailed view on them.

--
Thanks & Regards,
Swapnil Vaze

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

Adrian Klaver

adrian.klaver@aklaver.com

almost 9 years ago

In reply to: Swapnil Vaze (#1)

Re: Accessing DB2 tables from postgresql

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Swapnil Vaze

swapvaze28@gmail.com

almost 9 years ago

In reply to: Adrian Klaver (#3)

Re: Accessing DB2 tables from postgresql

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

Adrian Klaver

adrian.klaver@aklaver.com

almost 9 years ago

In reply to: Swapnil Vaze (#4)

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."

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Swapnil Vaze

swapvaze28@gmail.com

almost 9 years ago

In reply to: Adrian Klaver (#5)

Re: Accessing DB2 tables from postgresql

Hello,

Thanks for help!!

I am following up on that existing thread.

Thanks,
Swapnil Vaze

On Wed, Jun 28, 2017 at 7:01 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

--
Thanks & Regards,
Swapnil Vaze