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

[karthik kumar <karthikkumar.db2dba@xxxxxxxxx>]

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.