Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2

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

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.

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 removed both and tried still same error.

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&gt;

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

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.

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][__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

[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 =

$ 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

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