Tutorial: 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.
- Choosing the Right ODBC Driver for SAS on Linux
- Installing DataDirect Connect for ODBC and Connecting to Oracle
- Configuring for Optimal Performance
- Installing SAS and Running a Query
Choosing the Right ODBC Driver for SAS on Linux
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.
Installing DataDirect Connect for ODBC and Connecting to Oracle
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: |
|
|
|
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:
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. |
Configuring for Optimal Performance
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.
- 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.
- Once the wizard has launched, you'll be asked to select your DataDirect driver:
- 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.
Installing SAS and Running a Query
- 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.
- 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.
- 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:
- This launches the SAS:Query dialog. Once this is running, choose the Tools -> Switch Access Mode -> ODBC menu option:
- 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:
- 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.
- 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.
Biography
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
