50 How the SAS/ACCESS LIBNAME Statement Works Chapter 7
Figure 7.1 How SAS Connects to the DBMS
Vendor
client
libraries
Vendor
relational
DBMS
DBMS
communication
module
ACCESS
procedure*
DBLOAD
procedure*
Pass-Through
Facility
Interface
view engine
Engine
SAS/ACCESS software
* The ACCESS procedure and the DBLOAD procedure are not supported by all SAS/ACCESS interfaces.
** In some cases, both client and server software can reside on the same machine.
Client**Client** Server**
You can invoke a SAS/ACCESS relational DBMS interface by using either a LIBNAME
statement or a PROC SQL statement. (You can also use the ACCESS and DBLOAD
procedures with some of the SAS/ACCESS relational interfaces; however, these
procedures are no longer the recommended way to access relational database data.)
How the SAS/ACCESS LIBNAME Statement Works
SAS/ACCESS enables you to read, update, insert, and delete data from a DBMS
object as if it were a SAS data set. The process is as follows:
1 You invoke a SAS/ACCESS interface by specifying a DBMS engine name and the
appropriate connection options in a LIBNAME statement.
2 You enter SAS requests as you would when accessing a SAS data set.
3 SAS/ACCESS generates DBMS-specific SQL statements that are equivalent to the
SAS requests that you enter.
4 SAS/ACCESS submits the generated SQL to the DBMS.
The SAS/ACCESS engine defines which operations are supported on a table and calls
code that translates database operations such as open, get, put, or delete into
DBMS-specific SQL syntax. SAS/ACCESS engines use an established set of routines
with calls that are tailored to each DBMS.
To enhance performance, SAS/ACCESS can also transparently pass a growing
number of queries, joins, and data functions to the DBMS for processing (instead of
retrieving the data from the DBMS and then doing the processing in SAS). For
example, an important use of this feature is the handling of PROC SQL queries that
access DBMS data. Here’s how it works:
1 PROC SQL examines each query to determine whether it might be profitable to
send all or part of the query to the DBMS for processing.
2 A special query textualizer in PROC SQL translates queries (or query fragments)
into DBMS-specific SQL syntax.
3 The query textualizer submits the translated query to the SAS/ACCESS engine for
approval.
