SAS/ACCESS Software for Relational Databases: Reference

Controlling DBMS Connections

Because the overhead of executing a connection to a DBMS server can be resource-intensive, SAS/ACCESS engines support 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 SQL procedure updates the table, the update is performed with a second connection to the DBMS.

libname myoralib 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 provides an option to allow you to optimize the way 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;

If you do not want the connection to occur when the library is assigned, you can delay the connection to the DBMS by using the DEFER= option. When you specify DEFER=YES on the LIBNAME statement, the SAS/ACCESS engine connects to the DBMS the first time a DBMS object is referenced in a SAS program, as in the following example:

libname mydb2lib db2 authid=testuser defer=yes;

Note: If you use DEFER=YES to assign librefs to your DBMS tables and views in an AUTOEXEC program, the processing of the AUTOEXEC file will be faster because the connections to the DBMS are not made every time SAS is invoked. [cautionend]

Chapter Contents
Previous
Next
Top of Page