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

[Adrian Klaver]

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

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 = 5
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.kla...@aklaver.com

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

[Adrian Klaver]

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

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 = 5
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.kla...@aklaver.com

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

[karthik kumar]

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

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

[Steven Hirsch]

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.



--

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

[karthik kumar]

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

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

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

[Adrian Klaver]

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.kla...@aklaver.com

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

[Steven Hirsch]

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.



--

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

[karthik kumar]

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.

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

[karthik kumar]

On Mon, Apr 9, 2018 at 7: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 > > 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: 
>>
>
> 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.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Adrian Klaver]

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 
mailto:adrian.kla...@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: 


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.kla...@aklaver.com 








--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Adrian Klaver]

On 04/09/2018 02:37 PM, karthik kumar wrote:



On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver > 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: 


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.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

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

[karthik kumar]

On Mon, Apr 9, 2018 at 5:13 PM, Adrian Klaver 
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 > 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: 

postgres=# CREATE USER MAPPING FOR pg SERVER driver_db5 OPTIONS (odbc_UID
'db2inst1', odbc_PWD 'db2inst1');
CREATE USER MAPPING

>
>
>> Thanks,
>> Karthik.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Adrian Klaver]

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 



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.kla...@aklaver.com

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

[karthik kumar]

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=5
>
>
> Thanks,
> Karthik.
>

Thanks,
Karthik.