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