Error while loading data via odbc_loader_package

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

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]

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

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.

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

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.

Hello.
Any solution for my issue ?

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

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.

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.

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.

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.

Hi!

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

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.

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

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.

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.

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

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.