Overview
This article is part of a series. See Using SAS/ACCESS Interface to ODBC on UNIX Platforms for a listing of all articles in this series.
For important considerations and useful tools, see the “Quick Reference” section of the related article, How to configure SAS/ACCESS Interface to ODBC on UNIX Platforms.
See “Example” in this article for details about tool use.
Requirements
For this scenario, here is what you must already have before you can use SAS/ACCESS Interface to ODBC on Linux:
- Sybase IQ ODBC driver from Sybase
- the latest version of the unixODBC driver manager
ODBC driver manager
SAS first looks for an ODBC driver manager when you submit SAS/ACCESS Interface to ODBC code on Linux. The ODBC driver manager is always named LIBODBC.SO on Linux, no matter who provides the driver.
Environment variables
SAS finds the LIBODBC.SO ODBC driver manager by searching the directories that are specified in the LD_LIBRARY_PATH environment variable setting on Linux.
SAS uses the first 64-bit LIBODBC.SO component that it finds. Depending on your particular ODBC client configuration on Linux, the ODBC driver manager references either an ODBC.INI file or a combination of ODBC.INI and ODBCINST.INI files.
Connections
Once the ODBC driver manager finds the designated INI files, it references the stanza for the data source name (DSN) that you have specified in your SAS/ACCESS Interface to ODBC code to obtain connection specifics for the particular database to which you want to connect—for example, the ODBC driver to use, the server where the database resides, the port, and the database name.
You can also use a DSN-less connection. In this case, you provide all connection specifics for the database in your SAS/ACCESS Interface to ODBC code instead of relying on INI files for this information.
In a single SAS session on UNIX platforms, SAS on Linux can use only a single ODBC driver manager. You must therefore find a single ODBC driver manager that works with all ODBC drivers for the ODBC data sources to which you need to connect when using SAS/ACCESS Interface to ODBC.
unixODBC driver managers
When you use DBMS vendor-provided or freeware ODBC drivers, you typically pair them with a freeware unixODBC driver manager. A unixODBC driver manager works with most nonproprietary Linux ODBC drivers.
It is recommended that you use the most current version of the unixODBC driver manager. Download a zipped tar file, unixODBC-x.x.x.tar.gz, for unixODBC from the unixODBC project home page. (x.x refers to the most recent subrelease.)
When you use SAS/ACCESS Interface to ODBC with a unixODBC driver manager, you must use the most current version. Most Linux environments often already have a unixODBC driver manager in place in a global location such as /usr/lib64. However, often it is typically a very old version such as Version 2.2.14. SAS does not support using SAS/ACCESS Interface to ODBC with such old versions of the unixODBC driver manager.
Driver vendors and combinations
It is preferable to use an ODBC driver from a vendor such as Progress Software’s DataDirect because it includes a proprietary ODBC driver manager with one or more of the ODBC drivers.
It is possible to have a combination of proprietary and freeware ODBC drivers in place. In such cases, it is sometimes necessary to use a proprietary ODBC driver manager with the proprietary ODBC driver with which it came and also use it with freeware ODBC drivers. You can also use a freeware unixODBC driver manager with both proprietary and freeware ODBC drivers.
Specific Sybase IQ information
- When using SAS/ACCESS Interface to ODBC with a Sybase IQ ODBC driver from Sybase, in addition to a unixODBC ODBC driver (LIBODBC.SO), the Sybase IQ client also includes a LIBODBC.SO in the Sybase IQ /lib64 This is only a link, not a fully functional driver. So you must set the LD_LIBRARY_PATH environment variable so that the unixODBC LIBODBC.SO is listed first.
- Set your LD_LIBRARY_PATH setting to include the Sybase IQ /lib64 directory, but be sure that it is not listed first.
- SAS/ACCESS Interface to ODBC works with the Sybase IQ LIBODBC.SO link as long as it references a Sybase IQ DSN and uses the Sybase IQ ODBC driver. However, this Sybase IQ libodbc.so link does not work with SAS/ACCESS Interface to ODBC code that references other ODBC drivers.
Example
Here are the specific considerations for this example.
- When you use a unixODBC driver manager, it is best to place a global ODBC.INI file in the unixODBC etc
- Set Sybase IQ and ODBC environment variables in the SASENV_LOCAL file in the !SASROOT/bin
- A Linux startup script is used to set the needed Sybase IQ and ODBC environment variables. Normally, the best place to set these in the SASENV_LOCAL file in the !SASROOT/bin directory because it ensures that they are set for whoever starts SAS, no matter how it is started.
To be sure all needed ODBC environment variables are set for the SAS session, submit this SAS code.
%macro doit(command);
filename p pipe &command lrecl=32767;
data _null_;
infile p;
input;
put _infile_;
run;
%mend;
option LS=256;
%doit("set");
The example uses these tools:
Here is the log that shows how to get all of this working.
Linux cumulus.unx.sas.com 2.6.32-131.6.1.el6.x86_64 #1 SMP Mon Jun 20 14:15:38 EDT 2011
x86_64 x86_64 x86_64 GNU/Linux
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ pwd
/saswork/sasswl/7611424753_acc_odbc_unixodbc_sybase_iq
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ ls
odbc.ini sas startup_script
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ cat startup_script
export LD_LIBRARY_PATH=/saswork/sasswl/unixODBC2.3.2/lib:/TECHDBI/sybase/1540iq/IQ-15_4/lib64
export ODBCINI=/saswork/sasswl/7611424753_acc_odbc_unixodbc_sybase_iq/odbc.ini
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ . ./startup_script
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ echo $LD_LIBRARY_PATH
/saswork/sasswl/unixODBC2.3.2/lib:/TECHDBI/sybase/1540iq/IQ-15_4/lib64
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ echo $ODBCINI
/saswork/sasswl/7611424753_acc_odbc_unixodbc_sybase_iq/odbc.ini
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ cat $ODBCINI
[ODBC DataSources]
SybaseIQ=Sybase-provided ODBC driver for Sybase IQ
[SybaseIQ]
Driver=/TECHDBI/sybase/1540iq/IQ-15_4/lib64/libdbodbc12.so
Database=myDB
CommLinks=tcpip(host=iq154.unx.sas.com;port=5000)
Userid=myuser
Password=mypwd
Autostop=no
EngineName=iq154_test
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ cd /TECHDBI/sybase/1540iq/IQ-15_4/lib64
[sastac1@cumulus lib64]$ ls -l libdbodbc*
-rwxr-xr-x 1 root root 7990788 Feb 24 2012 libdbodbc12_n.so
-rwxr-xr-x 1 root root 8158431 Feb 24 2012 libdbodbc12_r.so
-rwxr-xr-x 1 root root 348749 Feb 24 2012 libdbodbc12.so
-rwxr-xr-x 1 root root 8158447 Feb 24 2012 libdbodbcansi12_r.so
-rwxr-xr-x 1 root root 1724242 Feb 24 2012 libdbodbcinst12_r.so
[sastac1@cumulus lib64]$ file libdbodbc12.so
libdbodbc12.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, not stripped
[sastac1@cumulus lib64]$ ldd libdbodbc12.so
linux-vdso.so.1 => (0x00007fff38f93000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007ffb8ff16000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007ffb8fd11000)
libm.so.6 => /lib64/libm.so.6 (0x00007ffb8fa8d000)
libc.so.6 => /lib64/libc.so.6 (0x00007ffb8f6f9000)
/lib64/ld-linux-x86-64.so.2 (0x0000003408c00000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007ffb8f4e2000)
[sastac1@cumulus lib64]$ cd /saswork/sasswl/unixODBC2.3.2
[sastac1@cumulus unixODBC2.3.2]$ pwd
/saswork/sasswl/unixODBC2.3.2
[sastac1@cumulus unixODBC2.3.2]$ ls
bin etc include lib share
[sastac1@cumulus unixODBC2.3.2]$ cd lib
[sastac1@cumulus lib]$ ls -l
total 2836
-rwxr-xr-x 1 sastac1 techsup 985 Mar 31 2014 libodbccr.la
lrwxrwxrwx 1 sastac1 techsup 18 Mar 31 2014 libodbccr.so -> libodbccr.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup 18 Mar 31 2014 libodbccr.so.2 -> libodbccr.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup 518134 Mar 31 2014 libodbccr.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup 997 Mar 31 2014 libodbcinst.la
lrwxrwxrwx 1 sastac1 techsup 20 Mar 31 2014 libodbcinst.so -> libodbcinst.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup 20 Apr 7 2014 libodbcinst.so.1 -> libodbcinst.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup 20 Mar 31 2014 libodbcinst.so.2 -> libodbcinst.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup 502443 Mar 31 2014 libodbcinst.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup 973 Mar 31 2014 libodbc.la
lrwxrwxrwx 1 sastac1 techsup 16 Mar 31 2014 libodbc.so -> libodbc.so.2.0.0
lrwxrwxrwx 1 sastac1 techsup 16 Mar 31 2014 libodbc.so.2 -> libodbc.so.2.0.0
-rwxr-xr-x 1 sastac1 techsup 1866716 Mar 31 2014 libodbc.so.2.0.0
[sastac1@cumulus lib]$ file libodbc.so
libodbc.so: symbolic link to `libodbc.so.2.0.0'
[sastac1@cumulus lib]$ file libodbc.so.2.0.0
libodbc.so.2.0.0: ELF 64-bit LSB shared object, x86-64, version 1 (GNU/Linux), dynamically linked, not stripped
[sastac1@cumulus lib]$ ldd libodbc.so.2.0.0
linux-vdso.so.1 => (0x00007fff840fd000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fa5e7315000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa5e70f7000)
libc.so.6 => /lib64/libc.so.6 (0x00007fa5e6d63000)
/lib64/ld-linux-x86-64.so.2 (0x0000003408c00000)
[sastac1@cumulus lib]$ cd /saswork/sasswl/7611424753_acc_odbc_unixodbc_sybase_iq
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ pwd
/saswork/sasswl/7611424753_acc_odbc_unixodbc_sybase_iq
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ ls -l
total 8
-rw-r--r-- 1 sastac1 techsup 272 Feb 15 16:16 odbc.ini
lrwxrwxrwx 1 sastac1 techsup 64 Feb 15 16:17 sas -> /TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sas
-rw-r--r-- 1 sastac1 techsup 176 Feb 15 16:11 startup_script
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ ldd /TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sasexe/sasodb
/TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sasexe/sasodb: /saswork/sasswl/unixODBC2.3.2/lib/libodbc.so: no version information available (required by /TECH/GOLDEN/RC/LAX.V9.4.M2.09FY6M/install/SASFoundation/9.4/sasexe/sasodb)
linux-vdso.so.1 => (0x00007fff9f3ff000)
libodbc.so => /saswork/sasswl/unixODBC2.3.2/lib/libodbc.so (0x00007f68a7e8c000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f68a7c60000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f68a7a29000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f68a7825000)
libm.so.6 => /lib64/libm.so.6 (0x00007f68a75a0000)
libc.so.6 => /lib64/libc.so.6 (0x00007f68a720c000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f68a6ff6000)
/lib64/ld-linux-x86-64.so.2 (0x0000003408c00000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007f68a6d93000)
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$ ./sas -nodms
NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M2)
Licensed to 9.4 M2 14w32 - SAS Studio, Site 70068118.
NOTE: This session is executing on the Linux 2.6.32-131.6.1.el6.x86_64 (LIN
X64) platform.
.
. (skipping some output)
.
NOTE: SAS initialization used:
real time 0.10 seconds
cpu time 0.02 seconds
1? libname sybtest1 odbc user=myuser pw=mypwd1 dsn="SybaseIQ";
NOTE: Libref SYBTEST1 was successfully assigned as follows:
Engine: ODBC
Physical Name: SybaseIQ
2? proc datasets library=mysybaseiq;
Directory
Libref SYBTEST1
Engine ODBC
Physical Name SybaseIQ
Schema/Owner dbitest
DBMS
Member Member
# Name Type Type
1 ONE DATA TABLE
2 class DATA TABLE
3? quit;
NOTE: PROCEDURE DATASETS used (Total process time):
real time 4.03 seconds
cpu time 0.03 seconds
4? data work.test1;
5? set sybtest1.class;
6? run;
NOTE: There were 19 observations read from the data set SYBTEST1.class.
NOTE: The data set WORK.TEST1 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 8.72 seconds
cpu time 0.01 seconds
7? endsas;
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
real time 1:53.00
cpu time 0.07 seconds
[sastac1@cumulus 7611424753_acc_odbc_unixodbc_sybase_iq]$
