Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG 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(dot)klaver(at)aklaver(dot)com