Обсуждение: [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
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 queryCan 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
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
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
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
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 Vazeadrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.
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 Klavercom>
--
Thanks & Regards,
Swapnil Vaze
--
Adrian Klaver
adrian.klaver@aklaver.com
Thanks & Regards,
Swapnil Vaze