Superior ODBC Access from SAS on Linux

Progress DataDirect Connect for ODBC Drivers are highly flexible - you can use our drivers to connect to any and all major databases. In this particular example, we will use Oracle.

This article explains how quick and easy it is to connect to an Oracle 11g server from SAS running on a Linux client using the Progress DataDirect Connect for ODBC Oracle Wire Protocol driver.

SAS is a leading provider of business analytics software and services. It offers an integrated collection of modular software for delivering sophisticated information across an entire organization.

DataDirect's wire protocol architecture makes it an ideal choice to use with SAS. Traditionally, ODBC has required installing specialized database client libraries on any computers that need to interact with a database server. This necessity introduced all sorts of deployment, maintenance, performance, and support headaches for IT administrators. However, with its wire protocol ODBC drivers, DataDirect has eliminated the need to install and configure client-side software, as shown below.

In this simple example, we'll illustrate how to use the SAS query tool to examine information from the EMP table in the 'SCOTT' schema hosted on a remote Oracle database, using the well-known scott/tiger login. To keep things simple, we'll use only the most basic SAS capabilities.

1. Install the DataDirect Connect for ODBC Oracle Wire Protocol driver (available in both 32 and 64-bit versions) on your Linux client.

   Please keep these important requirements in mind:

   • This product has been certified on the Red Hat Enterprise Linux and SUSE Linux Enterprise Server distributions.
   • Make sure that you have sufficient privileges on this computer to perform a software installation.
   • You'll need to run the installer from within the Korn (ksh) shell.
   • For the 32-bit version, the default installation directory is /opt/odbc32v53.
   • Although these instructions were created using the 5.3 release of the DataDirect ODBC drivers, they can also be easily adapted to earlier or later versions of the drivers.

2. Set your environment variables.

   To help you perform this task, DataDirect provides shell scripts for both the Bourne (odbc.sh) and C (odbc.csh) shells. You’ll find these scripts in your installation directory.

   These scripts configure the following environment variables:

   LD_LIBRARY_PATH

   PATH

   ODBCINI

   ODBCINST

    

3. Use the DataDirect-provided ivtestlib utility to test load the driver.

   You'll find this utility in the bin sub-directory of your installation. You'll need to provide it with the path to the Oracle driver as follows:

   ivtestlib /opt/odbc32v53/lib/ivora23.so
   
   

   If your installation was successful and your environment variables are properly set, you should receive a message similar to the following:

   Load of /opt/odbc32v53/lib/ivora23.so successful, qehandle is 0x944F030

   File version: 05.30.0157 (B0108, U0075)
   
   

   If you receive an error message at this point, there's a good chance that your environment variables aren't properly set.




4. Configure a connection to Oracle.

   You may either edit the odbc.ini file (found in your installation directory) or use the graphical odbcadmin utility, which you'll find in the tools subdirectory. Note that to run odbcadmin, you’ll need to have configured Motif support on your computer.

   Select the Oracle Wire Protocol driver, and click the Configure button:

   

   Enter details about your connection to Oracle, including:

   • The computer (i.e., host) where Oracle is running
   • Oracle's port number (the default is 1521, but check with your Oracle administrator to be sure)
   • The Oracle system identifier

   When you've finished entering these details, it's a good idea to test the connection. Click on the Test Connect button, and you'll receive a login dialog as shown below. After filling in the necessary information, click OK and you should receive a message that the connection was successful.

   

   If you're unable to successfully connect, double check your login settings with your database administrator.

Given all of Oracle's capabilities, it can be difficult to come up with optimal configuration settings. To help you in this important task, DataDirect offers a helpful Performance Tuning Wizard.

1. On Linux, you launch this wizard by pointing your browser at the index.html page found in the wizards subdirectory of your installation.

   Note: Make sure that you have Java runtime support enabled on your computer, or the wizard applet won't run properly.

2. Once the wizard has launched, you'll be asked to select your DataDirect driver:

   

3. Once the driver is chosen, you'll be shown a series of questions about how you interact with Oracle. Each question includes some additional guidance to help you provide the most accurate answer:

   

   Once you've finished, the wizard will update your Oracle data source with optimal performance settings.

1. Install the required SAS products.
   Now that you have a working connection to Oracle through ODBC, it's time to install the necessary SAS software to use this connection. For the purposes of this example, we'll install the SAS Foundation along with the SAS/ACCESS interface to ODBC.
2. Launch SAS Foundation.
   When you install SAS Foundation, you're prompted to provide an installation directory. We chose /usr/local/SAS. SAS then created a subdirectory called SASFoundation/9.2, which is where you'll find the sas command that launches the product.
   If you're unable to locate the SAS installation, contact your SAS administrator for more details.

3. Run a basic SAS query.

   Once SAS Foundation is launched, you can access its query capabilities by switching into the SAS Log window and choosing Tools -> Query:

   

4. This launches the SAS:Query dialog. Once this is running, choose the Tools -> Switch Access Mode -> ODBC menu option:

   

5. SAS now displays a dialog box where you can enter in the data source, username, and password details that you configured earlier.

   

   Once you press OK, SAS will attempt to connect, via the SAS/ACCESS interface to ODBC and the DataDirect Oracle Wire Protocol driver, to the Oracle data source. If everything is configured correctly, you should see a list of all available tables as follows:

   

6. At this point, all that's left to do is select the EMP table from the SCOTT tablespace, followed by any columns that you want to include in your query. Remember to click on the OK button to finalize your choices.

7. Once you've made these selections, choose the Tools -> Run Query -> Run Immediate menu option to launch your query:

   

   This will return results into the SAS output window:

   

   You're now ready to use all of the power of SAS by using the DataDirect Connect for ODBC Oracle Wire Protocol driver to interact with your Oracle-based information.

Robert Schneider is a Silicon Valley-based technology consultant. He has written five books and numerous articles on advanced technical topics such as Service Oriented Architecture (SOA), open source, and relational database design/optimization. He can be reached at Robert.Schneider@think88.com