We’re smarter together. Learn from this collection of community knowledge and add your expertise.

How to configure SAS/ACCESS Interface to ODBC on UNIX Platforms

by SAS Employee KatT_sas on ‎03-09-2016 11:10 AM (2,307 Views)

Overview

This article is part of a series.  Read this one before continuing with any other articles. See Using SAS/ACCESS Interface to ODBC on UNIX Platforms for a listing of all articles in this series.

 

Requirements

SAS/ACCESS Interface to ODBC on UNIX platforms requires these client components.  SAS does not provide these.

  • an ODBC driver manager: If you use a unixODBC driver manager, SAS requires a current version (2.3.2 or later).
  • ODBC-compliant drivers for the ODBC data sources to which you need to connect


Compliance

The ODBC drivers that you use with SAS/ACCESS Interface to ODBC must be compliant with the ODBC 3.5 or later specification. For details about what constitutes an ODBC-compliant driver on UNIX, see "Key Considerations When Using ODBC Drivers and SAS on UNIX" in the ODBC section of SAS/ACCESS Relational Databases: Reference.

 

ODBC driver managers

SAS first looks for an ODBC driver manager when you submit SAS/ACCESS Interface to ODBC code on UNIX platforms.  ODBC driver managers always have these names, no matter who provides the driver.

 

ODBC Driver Manager Name

UNIX Platform

LIBODBC.A

AIX

LIBODBC.SO

HP Itanium, Linux, Solaris, Solaris X64

 

Environment Variables

SAS finds the ODBC driver manager by searching the directories that are specified in your particular environment variable settings on each UNIX platform.

 

Environment Variable

UNIX Platform

LIBPATH

AIX

SHLIB_PATH

HP Itanium

LD_LIBRARY_PATH

Linux, Solaris, Solaris X64

 

SAS attempts to use the first 64-bit ODBC driver manager that it finds.  Depending on your particular ODBC client configuration, the ODBC driver manager references either an ODBC.INI file or a combination of ODBC.INI and ODBCINST.INI files.  The ODBC driver manager references one or more INI files that are specified in the UNIX environment variables—such as ODBCINI, ODBCINST, ODBCSYSINI—or, by default, it can reference one or more .INI files in users' home directories.

 

Connections

After 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 can use only a single ODBC driver.  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 UNIX ODBC drivers.  See the unixODBC project home page to get the most current version of the unixODBC driver manager.

When you use SAS/ACCESS Interface to ODBC with a unixODBC driver manager, you must use a current version (2.3.2 or later).  Some UNIX environments—particularly Linux—often already have a unixODBC driver manager in place in a global location such as /usr/lib or /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 ODBC drivers from ODBC driver vendors such as Easysoft or Progress Software’s DataDirect.  These are typically more sound and better supported.

 

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.  It might also be possible to use a freeware unixODBC driver manager with both proprietary and freeware ODBC drivers.

 

Tools

ODBC client driver manager and driver components include query tools that you can use to test your ODBC connections. SAS recommends that you use these tools to verify how sound your ODBC client configuration is before you contact SAS Tech Support.

 

_______________

Quick Reference

 

Use the latest versions of the unixODBC driver manager

More often than not, Linux platforms already have a unixODBC driver manager in place in /usr/lib or /usr/lib64. These are often very old 2.2.xx versions of the unixODBC driver manager, where xx refers to a particular subrelease. Older versions might not work well with the latest versions of ODBC drivers that are available.

 

Reference only related ODBC client components, not unrelated ones already in place on Linux

  • Older versions of unixODBC might already exist on Linux, in addition to the current version of the unixODBC driver manager that you're using with SAS/ACCESS Interface to ODBC. To prevent SAS from accessing and trying to use these, do not include /usr/lib or /usr/lib64 at the beginning of your LD_LIBRARY_PATH setting when you submit SAS/ACCESS Interface to ODBC code.
  • Teradata ODBC client components can also interfere with using SAS/ACCESS Interface to ODBC. When you install a Teradata client, Teradata automatically creates LIBODBC.SO links in /usr/lib and /usr/lib64, which point ODBC driver managers that Teradata provides.  To prevent SAS from accessing and trying to use these components, do not include /usr/lib or /usr/lib64 at the beginning of your LD_LIBRARY_ PATH setting when you submit SAS/ACCESS Interface to ODBC code.
  • If you have any doubt as to how environment variables are set for the SAS session or what what ODBC driver manager SAS will use, 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");
%doit("ldd $SASROOT/sasexe/sasodb");

  

Tools

Here are some tools you can use to test and trouble-shoot ODBC issues outside of a SAS session. 

 

Tool

Type/Works with

Purpose

FILE

Linux command

Check the bitness of ODBC client components.  SAS is a 64-bit application, so SAS/ACCESS Interface to ODBC requires that 64-bit ODBC client components be in place.

ISQL

unixODBC query tool

Tests connections to ODBC data sources and query tables.

LDD

Linux command

Checks the components that ODBC drivers need to be able to reference to work properly.

ODBCINST

unixODBC

Verifies the release of unixODBC.  Ensures that the right ODBC.INI file is referenced.

PROC OPTIONS GROUP=LANGUAGE CONTROL

SAS code

Verifies the encoding of your SAS session.

 

 

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.