Using Open Database Connectivity (ODBC) to Access SPD Server Tables

Requirements and Considerations for Using ODBC

Install OBDC Drivers on the Server

Prepare Your Client Machine for ODBC Installation

Configure ODBC on the Client

Connecting to SPD Server Using an ODBC Connection

Primary and Secondary LIBNAME Domains

Configure an ODBC Data Source to Connect Directly to an SPD Server

Configure an ODBC Data Source for SPD SNET

Edit the Services File on Your Machine

Create a Query Using an ODBC-Compliant Program

Requirements and Considerations for Using ODBC

To use ODBC, you must have SPD Server tables available on your network, SPD Servers and SPD SNET servers running, or client machines that are in a Windows environment. If you are working in such an environment, you might want to use ODBC if one or more of the following criterion are true:

You do not have Base SAS software running on the Windows client, but you need to view or change SPD Server tables.
You need to view or change the SPD Server tables using a Microsoft spreadsheet, database, or word processor.
You need to view or change SPD Server tables in ways that cannot be predetermined or programmed into a Web page.
You need to view or change SPD Server tables using Windows tools you are familiar with.

Install OBDC Drivers on the Server

Instructions for installing the OBDC driver are included in the download package.

Prepare Your Client Machine for ODBC Installation

Before you create an OBDC data sources driver, obtain the following information from your network administrator:

a user name and password that is defined by an SPD Server administrator
the primary LIBNAME domain of the SPD Server (also called the DBQ)
the port number of the SPD name server (also called the SERV)
the machine name or IP address of the SPD Server name server (also called the HOST)
any secondary LIBNAME domains that you want to assign to the ODBC connection

Configure ODBC on the Client

There are two basic steps to configure ODBC on your client machine:

Configure an ODBC data source.
Make your query using a Windows program. For more information, see Edit the Services File on Your Machine.

You can connect directly to an SPD Server without going through the SPD SNET server. This configuration is the preferred one. However, you can configure connections through the SPD SNET Server. Both of these configurations are shown in the following figures.

Configure ODBC to Connect SPD Server Client to SPD Server Host

Configure ODBC to Connect SPD Server Client to SPD SNET Server

Connecting to SPD Server Using an ODBC Connection

Primary and Secondary LIBNAME Domains

When a connection to the SPD server is established, a primary LIBNAME domain is assigned. The primary LIBNAME domain is specified by the DBQ connection options parameter. Immediately after the connection is made, the SAS ODBC driver assigns the secondary LIBNAME domains. You configure these domains on the Libraries tab of the SAS ODBC Driver Configuration window.

To make an ODBC connection through the SPD SNET server, you must configure an odbc.parm file on the SPD SNET Server machine.

Configure an ODBC Data Source to Connect Directly to an SPD Server

After you install the SAS ODBC driver, configure your ODBC data source. When you open the ODBC manager, enter information that points the OBDC driver to the data on the SPD Server.

From the Windows Start button, select Start Settings Control Panel.
Locate the ODBC Data Sources icon and open the Microsoft ODBC Data Source Administrator. The exact location of this program depends on your version of Windows.
Select the Add button, and then select the SAS ODBC driver.
Enter a data source name. You can also enter a description if you want to.
Select the Servers panel and enter your two-part server name.
Click the Configure box. In the TCP Options window, enter the following information:

Server Address

Enter the network address of the machine on which the SPD Server is running.

Server User Name

Enter the user name as configured for a DBQ (an SPD Server primary LIBNAME domain) on the SPD Server to which you will connect.

Server User Password

Enter the user password as configured for a DBQ (an SPD Server primary LIBNAME domain) on the SPD Server host to which you will connect.

Connection Options

Enter values for the following options. Additional SPD Server LIBNAME options might be listed. For more information, see SPD Server LIBNAME Options in SAS Scalable Performance Data Server: User's Guide

DBQ='SPD-Server-primary-LIBNAME-domain'

the SPD Server LIBNAME domain

HOST='name-server-node-name'

the location of the host computer

SERV='name-server-port-number'

the port number of the SPD Server name server that is running on the host
Click OK. Click Add and select the Libraries panel.
Enter the DBQ name of a secondary LIBNAME domain in both the Name and Host File text fields.
Enter spdseng in the Engine text field.
Enter appropriate information in the Options text field. In this field. use SQL pass-through syntax rules for libref statements.

Configure an ODBC Data Source for SPD SNET

After you install the SAS ODBC driver, configure your ODBC data source. When you open the ODBC manager, enter information that points the OBDC driver to the data on the SPD Server.

From the Windows Start button, select Start Settings Control Panel.
Click the ODBC icon and select the Add button.
Select the SAS ODBC driver.
Enter a data source name. You can also enter a description if you want to.
Select the Servers panel and enter your two-part server name. The second part of the server name should match the entry in the services file. For more information, see Edit the Services File on Your Machine. In that example, the server name is spdssnet.
Click the Configure box. In the TCP Options window, enter the following information:

Server Address

Enter the network address of the machine on which the SPD Server is running.

Server User Name

Enter the user name as configured for a DBQ (an SPD Server primary LIBNAME domain) on the SPD Server to which you will connect.

Server User Password

Enter the user password as configured for a DBQ (an SPD Server primary LIBNAME domain) on the SPD Server host to which you will connect.

Connection Options

Enter values for the following options. Additional SPD Server LIBNAME options might be listed. For more information, see SPD Server LIBNAME Options in SAS Scalable Performance Data Server: User's Guide.

DBQ='SPD Server primary LIBNAME domain'

the SPD Server LIBNAME domain

HOST='name server node name'

the location of the host computer

SERV='name server port number'

the port number of the SPD Server name server that is running on the host
Click OK, and then click Add.

Edit the Services File on Your Machine

Editing the Services file is required only for ODBC connections through the SPD SNET Server.

Find the Services file on your Windows machine. On Windows, the Services file is usually located in c:\windows\services.
Open the Services file using a text editor.
The Services file contains four columns. The rows of information can be sorted in port number order. Find the port number that is closest to the SPD Server port number (you obtained the SPD Server port number from the network administrator. See Prepare Your Client Machine for ODBC Installation.
Use the following syntax to add an entry to the Services file. Add the entry on its own line, in correct numeric order.

How to Add Service Name and Port Number to the Services File

column1 <service name>	column2 <port number & protocol>	column3 <aliases>	column4 <comment>
spdssnet	nnnn/tcp	not required	not required
spdssnet=name	nnnn=port number
assigned to server	protocol is always /tcp

Remember: The service name, spdssnet must match the server name that you used in step 5 of the section “Configure an ODBC Data Source for SPD SNET”. The port number must match the port number on which the SPD SNET server is running.

Create a Query Using an ODBC-Compliant Program

The following instructions create a query using Microsoft Access.

Start the SPD SNET server.
Start Microsoft Access.
From the Microsoft Access main menu, select File Get External Table.
Select Link Table.
Select Files of Type.
Select ODBC Databases.
Select the data source.