Relational Database Sources

SAS/ACCESS

Data can also be stored in third-party hierarchical and relational databases such as DB2, Oracle, SQL Server, and Teradata. SAS/ACCESS interfaces provide fast, efficient reading and writing of data to these facilities.

Several of the SAS/ACCESS engines support threaded Reads. This enables you to read entire blocks of data on multiple threads instead of reading data just one record at a time. This feature can reduce I/O bottlenecks and enables thread-enabled procedures to read data quickly. These engines and DB2 on z/OS also have the ability to access database management system (DBMS) data in parallel by using multiple threads to the parallel DBMS server.

The following SAS/ACCESS engines support this functionality:

Oracle
Sybase
DB2 (UNIX and PC)
SQL Server
Teradata

For more information about using the SAS/ACCESS interfaces, see SAS/ACCESS for Relational Databases: Reference. The following figure shows how connectivity to Oracle databases is configured:

Establishing Connectivity to Oracle Databases

For a detailed example of an Oracle connection, see Establishing Connectivity to an Oracle Database.

ODBC Sources

Open database connectivity (ODBC) standards provide a common interface to a variety of databases such as DB2, Microsoft Access, Oracle, and Microsoft SQL Server databases. Specifically, ODBC standards define application programming interfaces (APIs) that enable an application to access a database if the ODBC driver complies with the specification.

Tip

If a SAS/ACCESS engine is available for a database, then performance is better with the SAS/ACCESS engine rather than with the ODBC interface.

The basic components and features of ODBC include the following:

ODBC functionality is provided by three components: the client interface, the ODBC driver manager, and the ODBC driver. SAS provides the SAS/ACCESS interface to ODBC, which is the client interface. For PC platforms, Microsoft developed the ODBC Administrator, which is used from the Windows Control Panel to perform software administration and maintenance activities. The ODBC driver manager also manages the interaction between the client interface and the ODBC driver. On UNIX platforms, a default ODBC driver manager does not exist and SAS does not provide a driver manager with SAS/ACCESS to ODBC. For UNIX platforms, you should obtain an ODBC driver manager from your ODBC driver vendor.
The ODBC administrator defines a data source as the data that is used in an application and the operating system and network that are used to access the data. You create a data source by using the ODBC Administrator in the Windows Control Panel and then selecting an ODBC driver. You then provide the information (for example, data source name, user ID, password, description, and server name) that is required by the driver to make a connection to the desired data. The driver displays dialog boxes in which you enter this information. During operation, a client application usually requests a connection to a named data source, not just to a specific ODBC driver.
An ODBC Administrator tool is not available in a UNIX environment such as HP-UX, AIX, or Solaris. During an install, the driver creates a generic .odbc.ini file that can be edited to define your own data sources.

The following figure shows how ODBC is used to establish connectivity to Oracle databases:

Establishing Connectivity to Oracle Databases By Using ODBC

For a detailed example of an ODBC-based Oracle connection, see Establishing Connectivity to an Oracle Database By Using ODBC. The following figure shows how ODBC is used to establish connectivity to Access databases:

Establishing Connectivity to Access Databases By Using ODBC

For a detailed example of an ODBC-based Access connection, see Establishing Connectivity to a Microsoft Access Database By Using ODBC.