Re: 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