Data Integrity and Security Controlling DBMS Connections 25
accessible to the TESTGROUP schema. Any reference to a table that uses the libref
MYORALIB is passed to the Oracle server as a qualified table name; for example, if the
SAS program reads a table by specifying the SAS data set MYORALIB.TESTTABLE,
the SAS/ACCESS engine passes the following query to the server:
select * from "testgroup.testtable"
Controlling DBMS Connections
Because the overhead of executing a connection to a DBMS server can be
resource-intensive, SAS/ACCESS supports the CONNECTION= and DEFER= options
to control when a DBMS connection is made, and how many connections are executed
within the context of your SAS/ACCESS application. For most SAS/ACCESS engines, a
connection to a DBMS begins one transaction, or work unit, and all statements issued
in the connection execute within the context of the active transaction.
The CONNECTION= LIBNAME option allows you to specify how many connections
are executed when the library is used and which operations on tables are shared within
a connection. By default, the value is CONNECTION=SHAREDREAD, which means
that a SAS/ACCESS engine executes a
shared read DBMS connection when the library
is assigned. Every time a table in the library is read, the read-only connection is used.
However, if an application attempts to update data using the libref, a separate
connection is issued, and the update occurs in the new connection. As a result, there is
one connection for read-only transactions and a separate connection for each update
transaction.
In the following example, the SAS/ACCESS engine issues a connection to the DBMS
when the libref is assigned. The PRINT procedure reads the table by using the first
connection. When the PROC SQL updates the table, the update is performed with a
second connection to the DBMS.
libname myoralib oracle user=testuser password=testpass
path=’myoraserver’;
proc print data=myoralib.mytable;
run;
proc sql;
update myoralib.mytable set acctnum=123
where acctnum=456;
quit;
The following example uses the SAS/ACCESS interface to DB2 under OS/390. The
LIBNAME statement executes a connection by way of the DB2 Call Attach Facility to
the DB2 DBMS server:
libname mydb2lib db2 authid=testuser;
If you want to assign more than one SAS libref to your DBMS server, and if you do
not plan to update the DBMS tables, SAS/ACCESS enables you to optimize the way in
which the engine performs connections. Your SAS librefs can share a single read-only
connection to the DBMS if you use the CONNECTION=GLOBALREAD option. The
following example shows you how to use the CONNECTION= option with the
ACCESS= option to control your connection and to specify read-only data access.
libname mydblib1 db2 authid=testuser
connection=globalread access=readonly;
