Connect remote Oracle database to HammerDB

[erlupo1927]

Hello all,

I'm using the docker.io/tpcorg/hammerdb:latest-cloudtk image via GUI. I am completely new to Oracle and want to connect HammerDB to a remote Oracle database. The HammerDB librarycheck returns following output:

hammerdb>librarycheck
Checking database library for Oracle
Oratcl_Init(): Failed to load /home/instantclient_21_5/libclntsh.so with error libnnz21.so: cannot open shared object file: No such file or directory
Oratcl_Init(): ORACLE_LIBRARY = /home/instantclient_21_5/libclntsh.so : file not found.
Error: failed to load Oratcl -
Ensure that Oracle client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for MSSQLServer
Error: failed to load tdbc::odbc - couldn't load file "libiodbc.so": libiodbc.so: cannot open shared object file: No such file or directory
Ensure that MSSQLServer client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for Db2
Error: failed to load db2tcl - couldn't load file "/home/HammerDB-4.10/lib/db2tcl2.0.1/libdb2tcl.so": libdb2.so.1: cannot open shared object file: No such file or directory
Ensure that Db2 client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for MySQL
Success ... loaded library mysqltcl for MySQL
Checking database library for PostgreSQL
Success ... loaded library Pgtcl for PostgreSQL
Checking database library for MariaDB
Success ... loaded library mariatcl for MariaDB

What exactly needs to be configured in order to connect to the Oracle database? Do I need to install an Oracle client on my HammerDB host and then change the $LD_LIBRARY_PATH variable? Since there are no host/port fields in the GUI, where do I specify for HammerDB to which database it should connect to?

The tnsnames.ora file on the Oracle database host is already configured.

Help is greatly appreciated. Thank you!

[sm-shaw]

It looks like the GUI is not picking up the environment variable for the Oracle instant client that should already be installed.
To change this connect to the container with a shell
docker exec -it hammerdb-cloudtk /bin/bash
Change to the HammerDB-CloudTk directory
cd /home/HammerDB-4.10/CloudTk-1.4.0-51/Tk/HammerDB-CloudTk
and edit the startup file
vi TkStartup.tcl
Add the line where we set the LD_LIBRARY_PATH for the Oracle instant client

global env
set ix [lsearch $argv -display]
if {$ix >= 0} {
   incr ix
   set env(DISPLAY) [lindex $argv $ix]
   set argc 0
   set argv {}
cd [ file join {*}[ lreplace [ file split [ pwd ]] end end ]]
set tempdir [ file join [ pwd ] TMP ]
if { ![file exists $tempdir] } { file mkdir $tempdir }
set ::env(TMP) $tempdir
set env(LD_LIBRARY_PATH) /home/instantclient_21_5
exec ./hammerdb
}

To edit the network configuration put your tnsnames.ora file in the following location:
/home/instantclient_21_5/network/admin/
or as above set env(TNS_ADMIN) to another location where the tnsnames.ora file is.
Start and stop the container for the changes to take effect:

docker stop hammerdb-cloudtk
docker start hammerdb-cloudtk

You can test in the console that the Oracle library is now found:

Also note that CloudTk for HammerDB is here https://github.com/sm-shaw/CloudTk and can be dropped into the HammerDB top directory for any HammerDB release for it to work.

[erlupo1927]

Thank you for your fast and detailed reply! I followed your instructions and added the line set env(LD_LIBRARY_PATH) /home/instantclient_21_5 to the TkStartup.ctl file. I also created a tnsnames.ora file under /home/instantclient_21_5/network/admin/, then stopped and restarted the container.
The driver script on the GUI looks exactly like in your screenshot, set library Oratcl ;# Oracle OCI Library is displayed. For some reason though, librarycheck returned the same output as before. After stopping and starting the container a couple of times while testing, it now successfully loaded the library for Oracle. This problem seems to be fixed now.

Now, when trying to build the schema, I receive the error Error in Virtual User 1: Error: ORA-12154: TNS:could not resolve the connect identifier specified.
I tried setting TNS_ADMIN to /home/instantclient_21_5/network/admin/. Unfortunately, it didn't get rid of the error. The path also seems to get reset whenever I stop the container.

Is there anything else I am missing?

[sm-shaw]

So it looks like the Oracle client is now loaded and an ORA-12154 is a general connectivity error meaning it can't reach the database.

Unfortunately, the instant client does not include TNSPING or SQLPLUS to test the connectivity and the only client we are allowed to ship with HammerDB is the instant client, so this is a restriction on the usefulness of the client.

Note that the underlying connectivity that HammerDB uses is exactly the same as SQLPLUS i.e. the native C library, Oracle OCI (Oracle Call interface).

Firstly, if you want to set TNS_ADMIN specifically and verify it is not this then you should do if in the startup file so eg. it looks like this:

global env
set ix [lsearch $argv -display]
if {$ix >= 0} {
   incr ix
   set env(DISPLAY) [lindex $argv $ix]
   set argc 0
   set argv {}
cd [ file join {*}[ lreplace [ file split [ pwd ]] end end ]]
set tempdir [ file join [ pwd ] TMP ]
if { ![file exists $tempdir] } { file mkdir $tempdir }
set ::env(TMP) $tempdir
set env(LD_LIBRARY_PATH) /home/instantclient_21_5
set env(TNS_ADMIN) /home/instantclient_21_5/network/admin
exec ./hammerdb
}

However, another option is for you to download and install a more functional client instead of the instant client and change the LD_LIBRARY_PATH, and then you can use TNSPING to test it. As noted, unfortunately, we are not allowed to that by default from Oracle.

[erlupo1927]

Yes, indeed, troubleshooting without TNSPING and SQLPLUS was a bit harder, but I managed to find a solution. I simply had to make the connection string more specific by adding more information like FQDN and port under "Oracle Service Name" on the GUI. Thank you again!