Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2
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'); 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', postgres(# odbc_schema 'db2inst1', postgres(# sql_query 'select x from `db2inst1`.`TESTT`', 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.
Fwd: 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 ''); " 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.
Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2
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 <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: 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
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 > <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?: > > 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 <mailto:adrian.kla...@aklaver.com> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Fwd: Carto ODBC_FDW issue "ERROR: Connecting to driver" from PG to DB2
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
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
>> > 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.
Insert into on conflict, data size upto 3 billion records
Hi, I'm looking for suggestions on how I can improve the performance of the below merge statement, we have a batch process that batch load the data into the _batch tables using Postgres and the task is to update the main target tables if the record exists else into it, sometime these batch table could go up to 5 billion records. Here is the current scenario target_table_main has 700,070,247 records and is hash partitioned into 50 chunks, it has an index on logical_ts and the batch table has 2,715,020,546 close to 3 billion records, so I'm dealing with a huge set of data so looking of doing this in the most efficient way. Thank you
