A general rule: Get the database connection working using 'isql' before trying to get it working in an SLC libname.
First, check your version of unixODBC:
rpm -qa | grep -i unixodbc
Check your database client version:
For example (for Oracle):
rpm -qa | grep -i oracle-instantclient
Version of SLC:
/opt/altair/slc/2024/bin/wps
Version and flavour of linux:
cat /etc/system-release
Check that the unixODBC shared object libraries are present and correct:
ls -l /usr/lib64/*odbc*
Note that the Teradata TTU package installer overrides the default odbc library soft links in /usr/lib64/. See the instructions here on how to undo this change:
https://docs.teradata.com/r/Enterprise_IntelliFlex_Lake_VMware/ODBC-Driver-for-Teradata-User-Guide-20.00/MultiVersion-Support/UNIX-Operating-System/Teradata-ODBC-Driver-Installation
Certain environment variables may affect the unixODBC system and the drivers installed alongside it. There are many places where you might set environment variables.
For SLC Hub, we recommend the use of an Execution Profile for setting any of these env vars that will be needed by SLC to access databases.
SLC looks for altairslcenv.sh in the user's home directory [except when launched from hub] - this can be useful for testing but obviously applies only to one user.
SLC also looks in /opt/altair/slc/<version> for altairslcenv.sh - this one will be utilised by all users of SLC server.
These env vars can of course be specified in the bash shell initialisation files for one user (in home directory dot files) or for all users (in /etc files.
ODBCSYSINI – tells unixODBC where to find the odbc.ini and odbcinst.ini files (default is /etc).
ODBCINI – tells unixODBC where to find the user DSN’s (default ~/.odbc.ini)
Other environment variables that you probably don't need and which may affect your unixODBC behaviour: ODBCINST, ODBCINSTINI, DBCSEARCH.
It may be necessary to configure LD_LIBRARY_PATH rather precisely.
Some drivers require an additional 'lib' or 'lib64' directory to be added to the beginning - or to the end - of LD_LIBRARY_PATH.
Your linux machine may already be configured (see /etc/ld.so.conf) to consistently add certain library directories to LD_LIBRARY_PATH.
You can avoid using LD_LIBRARY_PATH by putting the unixODBC library directory into ldconfig. Check whether this has been done using "ldconfig -Nv".
User's dot file, and certain shell initialisation files in /etc, are often configured to modify LD_LIBRARY_PATH.
The PATH environment is not usually needed for unixODBC drivers, but you may want to add the unixODBC 'bin' directory to your PATH so that the 'odbcinst' and 'isql' commands are available as you type.
Happily, in the simple case where unixODBC is installed into Linux using RPM, then the utilities are found on PATH and the libraries are found on LD_LIBRARY_PATH by default (because PATH contains /bin and LD_LIBRARY_PATH implicitly contains /usr/lib64). If you install unixODBC anywhere else then you need to set PATH and LD_LIBRARY_PATH accordingly.
Example of environment variable settings which work for me:
export ODBCSYSINI=/home/nico/unixodbc-2.3.11/etc
export PATH=${PATH}:/home/nico/unixodbc-2.3.11/usr/bin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/home/nico/unixodbc-2.3.11/usr/lib64
To check your environment variables are configured correctly to find the expected ODBC ini files use odbcinst -j:
$ odbcinst -j
unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/nico/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
The odbcinst.ini file contains "driver definitions" and the odbc.ini file contains "data source names" (DSNs). You can also put single-user DSN's into .odbc.ini in your home directory but if you are setting up a linux server for multiple users then it is best to avoid confusion by NOT using .odbc.ini files.
Check your odbc.ini file:
Is your DSN listed at the top under [ODBC Data Sources] ?
Is your DSN present and correct further down in the file?
Does the Driver= entry point to the driver .so file or is it a cross-reference to an entry in the odbcinst.ini file?
Check your odbcinst.ini file:
Is your driver definition listed at the top under [ODBC Drivers] ?
Is your driver definition present and correct further down in the file?
Look at the Driver= line ... and check that this .so file is present on the computer.
For example if your driver= line looks like this...
Driver=/usr/lib/libmyodbc5.so
...then run this command to check the .so file truly exists...
ls -l /usr/lib/libmyodbc5.so
And if it turns out to be a symbolic link, make sure it ultimately points to a real file.
Before trying to connect from SLC you can use the ‘isql’ utility of unixODBC to test your database connection:
isql [-v] dsn user password
Once configured and working in ‘isql’ it can probably be used successfully from SLC!
n.b. ‘iusql’ is the same tool as ‘isql’ but with built-in Unicode support. Some datasources only work with iusql.
If you are having trouble connecting, it might be worth tryng SQLDriverConnect…
By default, isql uses an ODBC internal method called ‘SQLConnect’, which accepts a data source name and a username and password. If you include -k on the command line, isql uses ‘SQLDriverConnect’ instead. Examples:
Connects to a data source:
isql -v -k DSN=MyDSN
Connects to a different database to the one specified in the data source:
isql -v -k DSN=MyDSN;Database=NewDB
Uses a DSN-less connection:
isql -v -k "DRIVER={Easysoft ODBC-SQL Server SSL};Server=machine\sqlserver_instance; UID=user;PWD=password;Database=database_with_always_encrypted_data;ColumnEncryption=No"
You can put the DSN in a ‘connection string’ and augment it with addition connection settings, e.g.:
isql ";DSN=WebDB;Driver=PostgreSQL;UID=MyID;PASSWORD=secret;Debug=1;CommLog=1" -v
A connection string may be provided in its entirety, with no DSN reference at all:
isql ";Driver=PostgreSQL;UID=MyID;PASSWORD=secret" -v
Once the isql or iusql command has been used to show that the SLC host machine is able to connect to the database using the unixODBC system, then you are ready to a LIBNAME step in SLC.
For example:
LIBNAME db ODBC dsn=hefty user=testuser password=Passwrod1;
The SLC log shows whether the LIBNAME has established a successful connection to the database.
You may need additional parameters, for example to select a schema:
LIBNAME db ODBC dsn=hefty user=testuser password=Passwrod1 schema=dbo;
Check that the expected environment variable values are being used by your SLC service:
%put ODBCSYSINI is "%sysget(ODBCSYSINI)";
%put ODBCINI is "%sysget(ODBCINI)";
%put PATH is "%sysget(PATH)";
%put LD_LIBRARY_PATH is "%sysget(LD_LIBRARY_PATH)";
By using LIBNAME NOPROMPT you can test the identical connection string with LIBNAME that has been shown to work with isql…
The ‘noprompt=’ parameter allows a connection string to be used from the LIBNAME step. Example:
libname db odbc noprompt="driver=OraClient11g_home1; uid=test; password=*****; database=test; server=oraclesrv13;";
ODBC-based database drivers each have their own method for logging additional information when running.
Some of them will recognise (as part of their entry in odbcinst.ini):
LogLevel=6
LogFile=/tmp/mydb.log
Others (e.g. SQLServer) will honour this entry in the odbcinst.ini file (e.g. after the initial [ODBC Drivers] list):
[ODBC]
Trace=Yes
TraceFile=/tmp/odbclog/trace.log
Run your problematic LIBNAME and, after it fails, look at the named log file for possible log messages that might reveal the cause of the problem.
n.b.:
Typically SLC is released with an ODBC engine and an older ODBCOLD engine so it may be worth testing with both libnames.
And many of our database engines have both a NATIVE engine and an ODBC engine so you may test both to find if you have a working database connection.
