Thread: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2
Hi Guru's,
I am trying to access few table present in DB2 LUW from postgres database.
All commands work fine, however when I try to select data from table it throws error:
pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres
psql (10.1)
Type "help" for help.
postgres=# create extension odbc_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'SAMPLE');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID 'db2inst1', odbc_PWD 'db2inst1');
CREATE USER MAPPING
postgres=#
postgres=# CREATE FOREIGN TABLE
postgres-# odbc_testt (
postgres(# id integer
postgres(# )
postgres-# SERVER odbc_db2
postgres-# OPTIONS (
postgres(# odbc_database 'SAMPLE',
postgres(# odbc_schema 'db2inst1',
postgres(# sql_query 'select x from `db2inst1`.`TESTT`',
postgres(# sql_count 'select count(id) from `db2inst1`.`dblist`'
postgres(# );
CREATE FOREIGN TABLE
postgres=# select * from odbc_testt;
ERROR: Connecting to driver
pg@a92a3741d40e:~/odbc_fdw$ isql -v SAMPLE db2inst1 db2inst1
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from testt;
+------------+
| X |
+------------+
| 1 |
+------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit
root@a92a3741d40e:/home/pg# cat /etc/odbc.ini
[SAMPLE]
Description = DB2 remote SAMPLE Database
Driver=/home/db2inst1/sqllib/lib64/libdb2.so
SERVERNAME=MYDB2
UID=db2inst1
PWD=db2inst1
port=50000
Thanks,
Karthik.
On 04/09/2018 01:01 PM, karthik kumar wrote: > Hi Guru's, > > I am trying to access few table present in DB2 LUW from postgres database. > All commands work fine, however when I try to select data from table it > throws error: > > pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres > psql (10.1) > Type "help" for help. > > postgres=# create extension odbc_fdw; > CREATE EXTENSION > > postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw OPTIONS > (dsn 'SAMPLE'); > CREATE SERVER > > > > postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID > 'db2inst1', odbc_PWD 'db2inst1'); Assuming using this: https://github.com/ZhengYang/odbc_fdw I believe that should be username and password per above link: "CREATE USER MAPPING FOR postgres SERVER odbc_server OPTIONS (username 'root', password ''); " > CREATE USER MAPPING > > postgres=# > postgres=# CREATE FOREIGN TABLE > postgres-# odbc_testt ( > postgres(# id integer > postgres(# ) > postgres-# SERVER odbc_db2 > postgres-# OPTIONS ( > postgres(# odbc_database 'SAMPLE', Is the above the database name as well as the DSN name? > postgres(# odbc_schema 'db2inst1', > postgres(# sql_query 'select x from `db2inst1`.`TESTT`', Is the column name case sensitive, because below it shows up as X? I would try without the sql_query and sql_count queries as they are optional. > postgres(# sql_count 'select count(id) from `db2inst1`.`dblist`' > postgres(# ); > CREATE FOREIGN TABLE > > postgres=# select * from odbc_testt; > ERROR: Connecting to driver > > > > pg@a92a3741d40e:~/odbc_fdw$ isql -v SAMPLE db2inst1 db2inst1 > +---------------------------------------+ > | Connected! | > | | > | sql-statement | > | help [tablename] | > | quit | > | | > +---------------------------------------+ > SQL> select * from testt; > +------------+ > | X | > +------------+ > | 1 | > +------------+ > SQLRowCount returns -1 > 1 rows fetched > SQL> quit > > > root@a92a3741d40e:/home/pg# cat /etc/odbc.ini > [SAMPLE] > Description = DB2 remote SAMPLE Database > Driver=/home/db2inst1/sqllib/lib64/libdb2.so > SERVERNAME=MYDB2 > UID=db2inst1 > PWD=db2inst1 > port=50000 > > > Thanks, > Karthik. -- Adrian Klaver adrian.klaver@aklaver.com
On 04/09/2018 01:01 PM, karthik kumar wrote:
Hi Guru's,
I am trying to access few table present in DB2 LUW from postgres database.
All commands work fine, however when I try to select data from table it throws error:
pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres
psql (10.1)
Type "help" for help.
postgres=# create extension odbc_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'SAMPLE');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID 'db2inst1', odbc_PWD 'db2inst1');
Assuming using this:
https://github.com/ZhengYang/o
I believe that should be username and password per above link:
"CREATE USER MAPPING FOR postgres
SERVER odbc_server
OPTIONS (username 'root', password '');
"
Yes.
CREATE USER MAPPING
postgres=#
postgres=# CREATE FOREIGN TABLE
postgres-# odbc_testt (
postgres(# id integer
postgres(# )
postgres-# SERVER odbc_db2
postgres-# OPTIONS (
postgres(# odbc_database 'SAMPLE',
Is the above the database name as well as the DSN name?
Yes. It is.
Is the column name case sensitive, because below it shows up as X?
postgres(# odbc_schema 'db2inst1',
postgres(# sql_query 'select x from `db2inst1`.`TESTT`',
Is the column name case sensitive, because below it shows up as X?
No. But i tried both.
I would try without the sql_query and sql_count queries as they are optional.
I would try without the sql_query and sql_count queries as they are optional.
I removed both and tried still same error.
Thanks,
postgres(# sql_count 'select count(id) from `db2inst1`.`dblist`'
postgres(# );
CREATE FOREIGN TABLE
postgres=# select * from odbc_testt;
ERROR: Connecting to driver
pg@a92a3741d40e:~/odbc_fdw$ isql -v SAMPLE db2inst1 db2inst1
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from testt;
+------------+
| X |
+------------+
| 1 |
+------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit
root@a92a3741d40e:/home/pg# cat /etc/odbc.ini
[SAMPLE]
Description = DB2 remote SAMPLE Database
Driver=/home/db2inst1/sqllib/lib64/libdb2.so
SERVERNAME=MYDB2
UID=db2inst1
PWD=db2inst1
port=50000
Thanks,
Karthik.
Thanks,
Karthik.
On 04/09/2018 02:10 PM, karthik kumar wrote: > > > > > On 04/09/2018 01:01 PM, karthik kumar wrote: > > Hi Guru's, > > I am trying to access few table present in DB2 LUW from postgres > database. > All commands work fine, however when I try to select data from table > it throws error: > > pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres > psql (10.1) > Type "help" for help. > > postgres=# create extension odbc_fdw; > CREATE EXTENSION > > postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw > OPTIONS (dsn 'SAMPLE'); > CREATE SERVER > > > > postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS > (odbc_UID 'db2inst1', odbc_PWD 'db2inst1'); > > > Assuming using this: > > https://github.com/ZhengYang/odbc_fdw > <https://github.com/ZhengYang/odbc_fdw> > > I believe that should be username and password per above link: > > "CREATE USER MAPPING FOR postgres > SERVER odbc_server > OPTIONS (username 'root', password ''); > " > > Yes. Does 'yes' mean you changed this: CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID 'db2inst1', odbc_PWD 'db2inst1'); to this?: CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username 'db2inst1', password 'db2inst1'); > > Thanks, > Karthik. > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 04/09/2018 02:10 PM, karthik kumar wrote:
On 04/09/2018 01:01 PM, karthik kumar wrote:
Hi Guru's,
I am trying to access few table present in DB2 LUW from postgres
database.
All commands work fine, however when I try to select data from table
it throws error:
pg@a92a3741d40e:~/odbc_fdw$ psql -d postgres
psql (10.1)
Type "help" for help.
postgres=# create extension odbc_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER odbc_db2 FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (dsn 'SAMPLE');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS
(odbc_UID 'db2inst1', odbc_PWD 'db2inst1');
Assuming using this:
https://github.com/ZhengYang/odbc_fdw <https://github.com/ZhengYang/ odbc_fdw>
I believe that should be username and password per above link:
"CREATE USER MAPPING FOR postgres
SERVER odbc_server
OPTIONS (username 'root', password '');
"
Yes.
Does 'yes' mean you changed this:
CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID 'db2inst1', odbc_PWD 'db2inst1');
to this?:
CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username 'db2inst1', password 'db2inst1');
If i try to use username it throws error as invalid option
postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'sample');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS (username 'db2inst1', password 'db2inst1');
ERROR: invalid option "username"
HINT: Valid options in this context are: <none>
postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS (odbc_UID 'db2inst1', odbc_PWD 'db2inst1');
CREATE USER MAPPING
On 04/09/2018 02:37 PM, karthik kumar wrote: > > > On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 04/09/2018 02:10 PM, karthik kumar wrote: > Assuming using this: > > https://github.com/ZhengYang/odbc_fdw > > I believe that should be username and password per above link: > > "CREATE USER MAPPING FOR postgres > SERVER odbc_server > OPTIONS (username 'root', password ''); > " > > Yes. > > > Does 'yes' mean you changed this: > > CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID > 'db2inst1', odbc_PWD 'db2inst1'); > > to this?: > > CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username > 'db2inst1', password 'db2inst1'); > > > If i try to use username it throws error as invalid option > > postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw > OPTIONS (dsn 'sample'); > CREATE SERVER > > postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS > (username 'db2inst1', password 'db2inst1'); > ERROR: invalid option "username" > HINT: Valid options in this context are: <none> You are using the extension from below, correct?: https://github.com/ZhengYang/odbc_fdw In psql what does the below show?: \dx What happens if you do not include the OPTIONS? > > postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS > (odbc_UID 'db2inst1', odbc_PWD 'db2inst1'); > CREATE USER MAPPING > > > > Thanks, > Karthik. > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
On 04/09/2018 04:52 PM, Adrian Klaver wrote: > On 04/09/2018 02:37 PM, karthik kumar wrote: >> >> >> On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver >> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: >> >> On 04/09/2018 02:10 PM, karthik kumar wrote: > >> Assuming using this: >> >> https://github.com/ZhengYang/odbc_fdw > >> >> I believe that should be username and password per above link: >> >> "CREATE USER MAPPING FOR postgres >> SERVER odbc_server >> OPTIONS (username 'root', password ''); >> " >> >> Yes. >> >> >> Does 'yes' mean you changed this: >> >> CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID >> 'db2inst1', odbc_PWD 'db2inst1'); >> >> to this?: >> >> CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username >> 'db2inst1', password 'db2inst1'); >> >> >> If i try to use username it throws error as invalid option >> >> postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw >> OPTIONS (dsn 'sample'); >> CREATE SERVER >> >> postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS >> (username 'db2inst1', password 'db2inst1'); >> ERROR: invalid option "username" >> HINT: Valid options in this context are: <none> > > You are using the extension from below, correct?: Answering my own question after reading the subject line I realize now you are using: https://github.com/CartoDB/odbc_fdw Am not seeing anything wrong at this time. Did see this issue: Support PostreSQL 10 https://github.com/CartoDB/odbc_fdw/issues/60 Are you using the latest version of the extension? > > https://github.com/ZhengYang/odbc_fdw > > In psql what does the below show?: > > \dx > > What happens if you do not include the OPTIONS? > >> >> postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS >> (odbc_UID 'db2inst1', odbc_PWD 'db2inst1'); >> CREATE USER MAPPING >> >> >> >> Thanks, >> Karthik. >> >> >> >> -- Adrian Klaver >> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> >> >> > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Apr 9, 2018 at 7:52 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 04/09/2018 02:37 PM, karthik kumar wrote:
On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
On 04/09/2018 02:10 PM, karthik kumar wrote:Assuming using this:
https://github.com/ZhengYang/odbc_fdw
I believe that should be username and password per above link:
"CREATE USER MAPPING FOR postgres
SERVER odbc_server
OPTIONS (username 'root', password '');
"
Yes.
Does 'yes' mean you changed this:
CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (odbc_UID
'db2inst1', odbc_PWD 'db2inst1');
to this?:
CREATE USER MAPPING FOR pg SERVER odbc_db2 OPTIONS (username
'db2inst1', password 'db2inst1');
If i try to use username it throws error as invalid option
postgres=# CREATE SERVER driver_db5 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'sample');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS (username 'db2inst1', password 'db2inst1');
ERROR: invalid option "username"
HINT: Valid options in this context are: <none>
You are using the extension from below, correct?:
https://github.com/ZhengYang/odbc_fdw
In psql what does the below show?:
\dx
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+----------------------------------------------------------------
odbc_fdw | 0.3.0 | public | Foreign data wrapper for accessing remote databases using ODBC
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
What happens if you do not include the OPTIONS?
postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS (odbc_UID 'db2inst1', odbc_PWD 'db2inst1');
CREATE USER MAPPING
postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5;
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE
postgres-# driver_db5 (
postgres(# id integer
postgres(# )
postgres-# SERVER driver_db5
postgres-# OPTIONS (
postgres(# odbc_DATABASE 'sample',odbc_SCHEMA 'db2inst1'
postgres(# );
CREATE FOREIGN TABLE
postgres=# select * from driver_db5;
ERROR: Connecting to driver
Thanks,
Karthik.
-- Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hello Adrian
I built fdw with debug option and ran it with debug option. Here is the output
I built fdw with debug option and ran it with debug option. Here is the output
postgres=# select * from odbc_testt;
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG: odbcGetForeignRelSize
DEBUG: odbcGetTableOptions
DEBUG: odbcGetOptions
DEBUG: extract_odbcFdwOptions
DEBUG: CONN STR: database=SAMPLE;DRIVER=SAMPLE;UID=db2inst1;PWD=db2inst1
DEBUG: Error result (-1): Connecting to driver
ERROR: Connecting to driver
Looking at the code this is the place where the error is coming from:
Looking at the code this is the place where the error is coming from:
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, *env, dbc);
/* Connect to the DSN */
ret = SQLDriverConnect(*dbc, NULL, (SQLCHAR *) conn_str.data, SQL_NTS,
OutConnStr, 1024, &OutConnStrLen, SQL_DRIVER_COMPLETE);
check_return(ret, "Connecting to driver", dbc, SQL_HANDLE_DBC);
}
Trouble is, I can't get any unixodbc manual to indicate the meaning of -1.
On Wed, 11 Apr 2018, karthik kumar wrote: > Hello Adrian > > I built fdw with debug option and ran it with debug option. Here is the output > > postgres=# select * from odbc_testt; > DEBUG: StartTransactionCommand > DEBUG: StartTransaction > DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: > DEBUG: odbcGetForeignRelSize > DEBUG: odbcGetTableOptions > DEBUG: odbcGetOptions > DEBUG: extract_odbcFdwOptions > DEBUG: CONN STR: database=SAMPLE;DRIVER=SAMPLE;UID=db2inst1;PWD=db2inst1 > DEBUG: Error result (-1): Connecting to driver > ERROR: Connecting to driver > > Looking at the code this is the place where the error is coming from: > > /* Allocate a connection handle */ > SQLAllocHandle(SQL_HANDLE_DBC, *env, dbc); > /* Connect to the DSN */ > ret = SQLDriverConnect(*dbc, NULL, (SQLCHAR *) conn_str.data, SQL_NTS, > OutConnStr, 1024, &OutConnStrLen, SQL_DRIVER_COMPLETE); > check_return(ret, "Connecting to driver", dbc, SQL_HANDLE_DBC); > } > > Trouble is, I can't get any unixodbc manual to indicate the meaning of -1. Doesn't the DB2 driver have a debug/trace option you can turn on with an environment variable? That may tell you more. I'd also suggest you try connecting to the database from the same host using the db2clp. That gets the FDW out of the picture and may give better diagnostics. --
On 04/11/2018 11:22 AM, karthik kumar wrote: > Hello Adrian > > I built fdw with debug option and ran it with debug option. Here is the > output > > postgres=# select * from odbc_testt; > DEBUG: StartTransactionCommand > DEBUG: StartTransaction > DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, > xid/subid/cid: 0/1/0, nestlvl: 1, children: > DEBUG: odbcGetForeignRelSize > DEBUG: odbcGetTableOptions > DEBUG: odbcGetOptions > DEBUG: extract_odbcFdwOptions > DEBUG: CONN STR: database=SAMPLE;DRIVER=SAMPLE;UID=db2inst1;PWD=db2inst1 > DEBUG: Error result (-1): Connecting to driver > ERROR: Connecting to driver > > Looking at the code this is the place where the error is coming from: > > /* Allocate a connection handle */ > SQLAllocHandle(SQL_HANDLE_DBC, *env, dbc); > /* Connect to the DSN */ > ret = SQLDriverConnect(*dbc, NULL, (SQLCHAR *) conn_str.data, SQL_NTS, > OutConnStr, 1024, &OutConnStrLen, > SQL_DRIVER_COMPLETE); > check_return(ret, "Connecting to driver", dbc, SQL_HANDLE_DBC); > } > > Trouble is, I can't get any unixodbc manual to indicate the meaning of -1. > Per Steven's suggestion: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/data-access-tracing-with-the-odbc-driver-on-linux -- Adrian Klaver adrian.klaver@aklaver.com
This is the error we see in the ODBC trace log
ODBC][586][1523477070.240690][
Exit:[SQL_SUCCESS]
Environment = 0x556f874716a0
[ODBC][586][1523477070.240741]
Entry:
Environment = 0x556f874716a0
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][586][1523477070.240762]
Exit:[SQL_SUCCESS]
[ODBC][586][1523477070.240779]
Entry:
Handle Type = 2
Input Handle = 0x556f874716a0
[ODBC][586][1523477070.240804]
Exit:[SQL_SUCCESS]
Output Handle = 0x556f874f2000
[ODBC][586][1523477070.240837]
Entry:
Connection = 0x556f874f2000
Window Hdl = (nil)
Str In = [database=SAMPLE;DRIVER=
Str Out = 0x7ffd31cf8b60
Str Out Max = 1024
Str Out Ptr = 0x7ffd31cf8b3c
Completion = 1
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
DIAG [08001] [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "::1". Communication function detecting the error: "connect". Protocol specific error code(s): "99", "*", "*". SQLSTATE=08001
[ODBC][586][1523477070.331477]
Exit:[SQL_ERROR]
The trouble is, we are not convinced of the root cause, as using isql we can connect to db2 using the same account.
Env details
Db2 server on a docker container on my laptop: IP address 172.17.0.4
Db2 client where postgres server is installed is on a different container. IP address: 172.17.0.3
Output of db2 list node directory
Node 2 entry:
Node name = MYDB3
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = 172.17.0.4
Service name = 50000
output of db2 list db directory
Database alias = SAMPLE
Database name = SAMPLE
Node name = MYDB3
Database release level = 14.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
$ cat /etc/odbc.ini
[SAMPLE]
Description = DB2 remote SAMPLE Database
Driver = SAMPLE
servername = 172.17.0.4
system = MYDB3
username = db2inst1
password = db2inst1
port = 50000
PROTOCOL=TCPIP
Debug = 1
Commlog = 1
$ cat /etc/odbcinst.ini
[SAMPLE]
Instance = MYDB3
Description = DB2 ODBC Driver
Driver = /home/db2inst1/sqllib/lib64/
CommLog = 1
FileUsage = 1
#DontDLClose = 1
On Wed, 11 Apr 2018, karthik kumar wrote:
> This is the error we see in the ODBC trace log
>
> DIAG [08001] [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication
> protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was
> detected: "::1". Communication function detecting the error: "connect". Protocol specific error code(s):
^^^^^ Bzzzt!!
> "99", "*", "*". SQLSTATE=08001
I'm not a networking guru, but it sure looks like something is trying to
connect with an IPv6 loopback address. To me, this is the smoking gun.
--
I'm not a networking guru, but it sure looks like something is trying to connect with an IPv6 loopback address. To me, this is the smoking gun.
yes that much we figured it out here. Question is, why is isql able to connect, but not fdw via psql. At the end both isql and fdw should be using the same underlying mechanism to talk to db2.
On 04/11/2018 01:20 PM, karthik kumar wrote: > This is the error we see in the ODBC trace log > > ODBC][586][1523477070.240690][__handles.c][450] > Exit:[SQL_SUCCESS] > Environment = 0x556f874716a0 > [ODBC][586][1523477070.240741][SQLSetEnvAttr.c][182] > Entry: > Environment = 0x556f874716a0 > Attribute = SQL_ATTR_ODBC_VERSION > Value = 0x3 > StrLen = 0 > [ODBC][586][1523477070.240762][SQLSetEnvAttr.c][349] > Exit:[SQL_SUCCESS] > [ODBC][586][1523477070.240779][SQLAllocHandle.c][364] > Entry: > Handle Type = 2 > Input Handle = 0x556f874716a0 > [ODBC][586][1523477070.240804][SQLAllocHandle.c][482] > Exit:[SQL_SUCCESS] > Output Handle = 0x556f874f2000 > [ODBC][586][1523477070.240837][SQLDriverConnect.c][687] > Entry: > Connection = 0x556f874f2000 > Window Hdl = (nil) > Str In = > [database=SAMPLE;DRIVER=SAMPLE;UID=db2inst1;PWD=********][length = 55 > (SQL_NTS)] > Str Out = 0x7ffd31cf8b60 > Str Out Max = 1024 > Str Out Ptr = 0x7ffd31cf8b3c > Completion = 1 > UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE' > > DIAG [08001] [IBM][CLI Driver] SQL30081N A communication error > has been detected. Communication protocol being used: "TCP/IP". > Communication API being used: "SOCKETS". Location where the error was > detected: "::1". Communication function detecting the error: > "connect". Protocol specific error code(s): "99", "*", "*". SQLSTATE=08001 Protocol specific error code(s): "99": https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.messages.doc/doc/r0052008.html#r0052008.dita__tcpcec EADDRNOTAVAIL 99 The specified hostname or IP address is not available from the local machine. Looks to me like it is not finding your server IP and is trying IPv6 localhost with port 5000 and not finding it. More comments below. > > > [ODBC][586][1523477070.331477][SQLDriverConnect.c][1273] > Exit:[SQL_ERROR] > > The trouble is, we are not convinced of the root cause, as using isql we > can connect to db2 using the same account. > > Env details > > Db2 server on a docker container on my laptop: IP address 172.17.0.4 > Db2 client where postgres server is installed is on a different > container. IP address: 172.17.0.3 > > Output of db2 list node directory > > Node 2 entry: > > Node name = MYDB3 > Comment = > Directory entry type = LOCAL > Protocol = TCPIP > Hostname = 172.17.0.4 > Service name = 50000 > > output of db2 list db directory > > Database alias = SAMPLE > Database name = SAMPLE > Node name = MYDB3 > Database release level = 14.00 > Comment = > Directory entry type = Remote > Catalog database partition number = -1 > Alternate server hostname = > Alternate server port number = > So you changed settings below. What happened to UID AND PWD? Also I would simplify and get rid of in odbc.ini: system PROTOCOL and in odbcinst.ini: Instance Also in odbcinst.init I would rename the section header from SAMPLE to DB2 and Driver in odbc.ini to DB2 Helps with identifying what you are actually using. Did you also change the FOREIGN SERVER/USER MAPPING settings and if so to what? > $ cat /etc/odbc.ini > [SAMPLE] > Description = DB2 remote SAMPLE Database > Driver = SAMPLE > servername = 172.17.0.4 > system = MYDB3 > username = db2inst1 > password = db2inst1 > port = 50000 > PROTOCOL=TCPIP > Debug = 1 > Commlog = 1 > > $ cat /etc/odbcinst.ini > [SAMPLE] > Instance = MYDB3 > Description = DB2 ODBC Driver > Driver = /home/db2inst1/sqllib/lib64/libdb2.so > CommLog = 1 > FileUsage = 1 > #DontDLClose = 1 > -- Adrian Klaver adrian.klaver@aklaver.com
On 04/11/2018 01:20 PM, karthik kumar wrote: > Db2 server on a docker container on my laptop: IP address 172.17.0.4 > Db2 client where postgres server is installed is on a different > container. IP address: 172.17.0.3 > > Output of db2 list node directory > > Node 2 entry: > > Node name = MYDB3 > Comment = > Directory entry type = LOCAL > Protocol = TCPIP > Hostname = 172.17.0.4 > Service name = 50000 > > output of db2 list db directory > > Database alias = SAMPLE > Database name = SAMPLE > Node name = MYDB3 > Database release level = 14.00 > Comment = > Directory entry type = Remote > Catalog database partition number = -1 > Alternate server hostname = > Alternate server port number = Are you using this DB2 ODBC driver?: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/t0024166.html If so check out the links below for conflict between specifying an Instance and Protocol =TCPIP: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0024132.html https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0008807.html > > $ cat /etc/odbc.ini > [SAMPLE] > Description = DB2 remote SAMPLE Database > Driver = SAMPLE > servername = 172.17.0.4 > system = MYDB3 > username = db2inst1 > password = db2inst1 > port = 50000 > PROTOCOL=TCPIP > Debug = 1 > Commlog = 1 > > $ cat /etc/odbcinst.ini > [SAMPLE] > Instance = MYDB3 > Description = DB2 ODBC Driver > Driver = /home/db2inst1/sqllib/lib64/libdb2.so > CommLog = 1 > FileUsage = 1 > #DontDLClose = 1 > -- Adrian Klaver adrian.klaver@aklaver.com