cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed

How to configure SAS/ACCESS Interface to ODBC on Linux with a Sybase IQ ODBC driver from Sybase

Started ‎03-11-2016 by
Modified ‎03-11-2016 by
Views 7,485

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:

 

  • ISQL
  • FILE
  • LDD
  • ODBCINST

 

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]$

 

Comments
kaushalsolanki
‎07-30-2019 03:49 AM
‎07-30-2019 03:49 AM

Great Post, i wanted to which odbc driver can be used for connection with SAP ASE Sybase database over Linux server.

0 Likes
Version history
Last update:
‎03-11-2016 09:34 AM
Updated by:
Community Manager
Contributors

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags