Error while loading data via odbc_loader_package

oleg_lll

I want load data into Vertica via odbc_loader_package but have error

dbadmin=> COPY stage.apx_ACTIONCLASSdbadmin-> WITH SOURCE ODBCSource() PARSER ODBCLoader(connect='DSN=apex-ora', query='SELECT * FROM cic.ACTIONCLASS');
ERROR 3399:  Failure in UDx RPC call InvokeSetupUDL(): Error calling setupUDL() in User Defined Object [ODBCLoader] at [ODBCLoader.cpp:213], error code: 0, 
message: ODBC Error: SQLDriverConnect() failed with error code 01000, native code 0 
[[unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient/libsqora.so.11.1' : file not found]
dbadmin=> \q


I've checked library, it exists

[dbadmin@DWH-DEV tmp]$ ll /usr/local/instantclient/libsqora.so.11.1-rwxr-xr-x 1 root root 1003582 Aug 24  2013 /usr/local/instantclient/libsqora.so.11.1

What i should do, any solution ?

Prasanta_Pal

If you have multiple nodes, can you check if the file is same and available on all the nodes? Or may try changing the permission to 777 or owner to dbadmin

oleg_lll

I made changes but have the same error

[root@DWH-DEV ~]# chmod 777 /usr/local/instantclient/libsqora.so.11.1
[root@DWH-DEV ~]#
[root@DWH-DEV ~]#
[root@DWH-DEV ~]# ll /usr/local/instantclient/libsqora.so.11.1
-rwxrwxrwx 1 root root 1003582 Aug 24  2013 /usr/local/instantclient/libsqora.so.11.1

dbadmin=> COPY stage.apx_ACTIONCLASSdbadmin-> WITH SOURCE ODBCSource() PARSER ODBCLoader(connect='DSN=apex-ora', query='SELECT * FROM cic.ACTIONCLASS');
ERROR 3399:  Failure in UDx RPC call InvokeSetupUDL(): Error calling setupUDL() in User Defined Object [ODBCLoader] at [ODBCLoader.cpp:213], error code: 0, message: ODBC Error: SQLDriverConnect() failed with error code 01000, native code 0 [[unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient/libsqora.so.11.1' : file not found]

Navin_C

Hi Oleg,

Vertica tries to find all the directories in lib64 directory. Check if you have the missing library in lib64 directory and not in lib directory.

Hope this helps
NC

oleg_lll

Hi.

I copy library into /lib64 folder but have same error

dbadmin=> COPY stage.apx_ACTIONCLASSdbadmin-> WITH SOURCE ODBCSource() PARSER ODBCLoader(connect='DSN=apex-ora', query='SELECT * FROM cic.ACTIONCLASS');
ERROR 3399:  Failure in UDx RPC call InvokeSetupUDL(): Error calling setupUDL() in User Defined Object [ODBCLoader] at [ODBCLoader.cpp:213], error code: 0, message: ODBC Error: SQLDriverConnect() failed with error code 01000, native code 0 [[unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient/libsqora.so.11.1' : file not found]
dbadmin=> \q
[dbadmin@DWH-DEV lib64]$
[dbadmin@DWH-DEV lib64]$ pwd
/lib64
[dbadmin@DWH-DEV lib64]$
[dbadmin@DWH-DEV lib64]$ ll libsqora.so.11.1
-rwxrwxrwx 1 root root 1003582 Aug 11 14:57 libsqora.so.11.1
[dbadmin@DWH-DEV lib64]$
[dbadmin@DWH-DEV lib64]$ ll /usr/local/instantclient/libsqora.so.11.1
-rwxrwxrwx 1 root root 1003582 Aug 24  2013 /usr/local/instantclient/libsqora.so.11.1

Oleg.

[Deleted User]

Hi Oleg, Just to reiterate Prasanta's question -- how many nodes are there in your Vertica cluster? (You can see by doing "SELECT * FROM nodes;") Thanks, Adam

oleg_lll

Hi Adam!

I have only one node.

dbadmin=> SELECT * FROM nodes;    node_name     |      node_id      | node_state | node_address | export_address |                  catalog_path                   | is_ephemeral
------------------+-------------------+------------+--------------+----------------+-------------------------------------------------+--------------
 v_dwhdb_node0001 | 45035996273704980 | UP         | 127.0.0.1    | 127.0.0.1      | /catalog/dwhdb/v_dwhdb_node0001_catalog/Catalog | f
(1 row)

Oleg.

oleg_lll

Hello.
Any solution for my issue ?

[Deleted User]

Hm...  Another thing to try, could you try running?:

$ file -L /usr/local/instantclient/libsqora.so.11.1

It should display a bunch of information, including something like "ELF 64-bit".  If it displays something else, then you probably have the wrong driver for your platform.

You could also try to connect to the remote database using something other than Vertica.  For example, the "isql" command-line client.  Once you have that working, then try Vertica.

This appears to be an issue with the ODBC driver that you are trying to use.  I believe "libsqora" is part of Oracle's driver?  You might have better luck asking Oracle folks; we can try to help, but they are likely more familiar with the driver than we are.

Adam

id10t

Hello!

Any solution for my issue ?

And did you check your connection to Oracle via isql?

At first: show us that you able to connect to Oracle from command line with isql.
If you didn't configured connection properly so no reason to talk about Vertica odbc_loader.

oleg_lll

Hello Adam.

From my vertica machine

[dbadmin@DWH-DEV ~]$ file -L /usr/local/instantclient/libsqora.so.11.1/usr/local/instantclient/libsqora.so.11.1: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, not stripped

Oleg.

oleg_lll

Hello.

Yes, i can connect to Oracle via isql utility.

[dbadmin@DWH-DEV ~]$ which isql/usr/local/bin/isql
[dbadmin@DWH-DEV ~]$ isql apex-ora -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

From /usr/local/etc/odbc.ini

[apex-ora]
Driver = OracleODBC-11g
DSN = OracleODBC-11g
ServerName = APEXLINE
SID = APEXLINE
UserID = CICTEST
Password = fffff

[dbadmin@DWH-DEV ~]$ cat /usr/local/etc/odbcinst.ini

[OracleODBC-11g]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /usr/local/instantclient/libsqora.so.11.1
FileUsage       = 1
Driver Logging  = 7


Oleg.

id10t

Hi!

Fine. Ok, lets continue...

1. Tracing
Lets define a tracing and will see what tracing will say.

# To enable unixODBC tracing, add this section to odbcinst.ini. # Include the [ODBC] section heading. [ODBC] Trace = yes TraceFile = trace_file_path

I see you defined "Driver Logging = 7". Does driver log has any errors?
Native error 0 - means Communication link failure.

Generally speaking this means the OOB Client has lost contact with the OOB Server unexpectedly. This usually only happens if the OOB Server process handling the client connection has terminated unexpectedly e.g. if the server was shut down, if the OOB Server timed out the client. It can also happen if there was a segmentation fault or bus error in the OOB Server, unixODBC or the ODBC driver you were connecting to.

Since unixODBC can connect a problem in client or in package.

2. ldd

Post please:

$ ldd /usr/local/instantclient/libsqora.so.11.1/usr/local/instantclient/libsqora.so.11.1

3. LD_LIBRARY_PATH
May be Oracle client libraries/binaries are not in path. Make sure oracle directory(where you have installed the oracle client, is added to LD_LIBRARY_PATH(LD_LIBRARY_PATH_64 for 64 bit).
http://itbdc.wordpress.com/2008/07/08/how-to-install-oracle-odbc-client-for-cent-os-fedora-or-other-...

4. /usr/lib
Try a standard location - /usr/lib/oracle/11.1/client/lib not of instantclient.
If its not exists, so create (maximum delete after).

5. some hack (when debug mode is enabled)
Define in odbc.ini some dummy parameter(that not exists - foo = bar, whatever). Will see an error in this case.

PS
All from repos or did you some manual install?

oleg_lll

Hi.

[dbadmin@DWH-DEV ~]$ ldd /usr/local/instantclient/libsqora.so.11.1       
linux-vdso.so.1 =>  (0x00007fffcc3ff000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fa7c9b50000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fa7c98cb000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa7c96ae000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fa7c9495000)
        libclntsh.so.11.1 => /usr/local/instantclient/libclntsh.so.11.1 (0x00007fa7c6b25000)
        libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007fa7c690e000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fa7c657a000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fa7c9f22000)
        libnnz11.so => /usr/local/instantclient/libnnz11.so (0x00007fa7c61ac000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fa7c5fab000)


[dbadmin@DWH-DEV ~]$ env | grep LD_LIB
LD_LIBRARY_PATH=:/usr/local/instantclient

Other points info i'll send later.

Oleg.

id10t

Hi!

LD_LIBRARY_PATH should point on lib directory. 
Take a look here: https://help.ubuntu.com/community/Oracle%20Instant%20Client

oleg_lll

Hi.

I set LD_LIBRARY_PATH but have the same

[dbadmin@DWH-DEV ~]$ echo $LD_LIBRARY_PATH
/usr/local/instantclient:/lib64:/lib:/usr/lib

dbadmin=> COPY stage.apx_ACTIONCLASSdbadmin-> WITH SOURCE ODBCSource() PARSER ODBCLoader(connect='DSN=apex-ora', query='SELECT * FROM cic.ACTIONCLASS');
ERROR 3399:  Failure in UDx RPC call InvokeSetupUDL(): Error calling setupUDL() in User Defined Object [ODBCLoader] at [ODBCLoader.cpp:213], error code: 0, message: ODBC Error: SQLDriverConnect() failed with error code 01000, native code 0 [[unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient/libsqora.so.11.1' : file not found]

Oleg.

id10t

Hi!

1. Can you post: ls /usr/local/instantclient
2. Something in logs?

PS
After you set LD_LIBRARY_PATH, did you update ldconfig?

(root)#> ldconfig -vp
(root)#> ldconfig -vn /usr/local/instantclient
(root)#> for DIR in 'ls /usr/local/instantclient/*'; do [[ -d $DIR ]] && sudo ldconfig -vn $DIR; done

oleg_lll

Hi.

[dbadmin@DWH-DEV ~]$ ll /usr/local/instantclienttotal 187592
-rwxrwxr-x 1 root root     25420 Aug 24  2013 adrci
-rw-rw-r-- 1 root root       439 Aug 24  2013 BASIC_README
-rwxrwxr-x 1 root root     47860 Aug 24  2013 genezi
-rwxrwxrwx 1 root root       370 Jul 30 13:25 glogin.sql
-rwxrwxr-x 1 root root  53865194 Aug 24  2013 libclntsh.so.11.1
-r-xr-xr-x 1 root root   7996693 Aug 24  2013 libnnz11.so
-rwxrwxr-x 1 root root   1973074 Aug 24  2013 libocci.so.11.1
-rwxrwxr-x 1 root root 118738042 Aug 24  2013 libociei.so
-r-xr-xr-x 1 root root    164942 Aug 24  2013 libocijdbc11.so
-r-xr-xr-x 1 root root   1502287 Jul 30 12:51 libsqlplusic.so
-r-xr-xr-x 1 root root   1469542 Jul 30 12:51 libsqlplus.so
-rwxrwxrwx 1 root root   1003582 Aug 24  2013 libsqora.so.11.1
drwxr-xr-x 3 root root      4096 Jul 24 17:01 network
-r-xr-xr-x 1 root root     19217 Aug 24  2013 ODBC_IC_Readme_Unix.html
-r-xr-xr-x 1 root root     91881 Aug 24  2013 ODBCRelnotesJA.htm
-r-xr-xr-x 1 root root     44091 Aug 24  2013 ODBCRelnotesUS.htm
-r-xr-xr-x 1 root root      3732 Aug 24  2013 odbc_update_ini.sh
-r--r--r-- 1 root root   2091135 Aug 24  2013 ojdbc5.jar
-r--r--r-- 1 root root   2739616 Aug 24  2013 ojdbc6.jar
-r-xr-xr-x 1 root root      9320 Jul 30 12:51 sqlplus
-rw-r--r-- 1 root root       443 Jul 30 12:51 SQLPLUS_README
-rwxrwxr-x 1 root root    192365 Aug 24  2013 uidrvci
-rw-rw-r-- 1 root root     66779 Aug 24  2013 xstreams.jar

[dbadmin@DWH-DEV ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME:/lib64:/lib:/usr/lib

[dbadmin@DWH-DEV ~]$ ldconfig -vn /usr/local/instantclient
/usr/local/instantclient:
        libocijdbc11.so -> libocijdbc11.so
        libsqora.so.11.1 -> libsqora.so.11.1
        libsqlplusic.so -> libsqlplusic.so
        libociei.so -> libociei.so
        libclntsh.so.11.1 -> libclntsh.so.11.1
        libocci.so.11.1 -> libocci.so.11.1
        libsqlplus.so -> libsqlplus.so
        libnnz11.so -> libnnz11.so

After running above commands i have the same error.

oleg_lll

Hi.

I have such configuration

[dbadmin@DWH-DEV ~]$ odbcinst -junixODBC 2.3.0
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/dbadmin/.odbc.ini

cat /usr/local/etc/odbcinst.ini

[OracleODBC-11g]Description     = Oracle ODBC driver for Oracle 11g
Driver          = /usr/local/instantclient/libsqora.so.11.1
FileUsage       = 1
Trace = Yes
TraceFile = /tmp/odbctrace.log

run statement

COPY stage.apx_ACTIONCLASS WITH SOURCE ODBCSource() PARSER ODBCLoader(connect='DSN=apex-ora', query='SELECT * FROM cic.ACTIONCLASS');

but did not get trace file.

Oleg.

id10t

but did not get trace file.

Read my post one more time and carefully:

# To enable unixODBC tracing, add this section to odbcinst.ini. # Include the [ODBC] section heading. [ODBC] Trace = yes TraceFile = trace_file_path

(hint - section [ODBC]).


BTW: why we are trying to solve what you can't check? Your db is down and you can bring it up. So what we are talking about?

PS
Ok, no trace. And what about driver log file?

Driver log = 7

oleg_lll

Hi.

I make such section in file /usr/local/etc/odbcinst.ini

[ODBC]
Trace = Yes
TraceFile = /tmp/odbctrace.log

I run my statement and no trace file.
My dev db is up and test db is down, so i can check odbc loader.

Oleg.

ganna

Hi Oleg,

 

you got any solution for your issue, i am facing the same issue from my end. I read all your comments same with my side, i even can connect via isql it says connected and even pull data. 

 

but while i am doing from studio it is throwing me this error, if you can reply back that would be great thanks .

 

Thanks in advance. 

 

Thanks,

Gane