26 Locking, Transactions, and Currency Control Chapter 3
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, for example,
libname mydb2lib db2 authid=testuser defer=yes;
the SAS/ACCESS engine connects to the DBMS the first time a DBMS object is
referenced in a SAS program.
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 is faster because the
connections to the DBMS are not made every time SAS is invoked.
Locking, Transactions, and Currency Control
SAS/ACCESS provides options that allow you to control some of the row, page, or
table locking operations that are performed by the DBMS and the SAS/ACCESS engine
as your programs are executed. For example, by default, the SAS/ACCESS Oracle
engine does not lock any data when it reads rows from Oracle tables. However, you can
override this behavior by using the locking options that are supported in the SAS/
ACCESS interface to Oracle.
If you want to lock the data pages of a table while SAS is reading the data to prevent
other processes from updating the table, you can use the READLOCK_TYPE= option,
as in the following example:
libname myoralib oracle user=testuser pass=testpass
path=’myoraserver’ readlock_type=table;
data work.mydata;
set myoralib.mytable(where=(colnum > 123));
run;
In this example, the SAS/ACCESS Oracle engine obtains a TABLE SHARE lock on the
table so that the data cannot be updated by other processes while your SAS program is
reading it.
In the following example, Oracle acquires row-level locks on rows read for update in
the tables in the libref.
libname myoralib oracle user=testuser password=testpass
path=’myoraserver’ updatelock_type=row;
Note: Each SAS/ACCESS interface supports specific options; see the SAS/ACCESS
documentation for your DBMS to determine which options it supports.
Customizing DBMS Connect and Disconnect Exits
You can specify DBMS commands or stored procedures to be executed immediately
after a DBMS connection or before a DBMS disconnect by using the LIBNAME options
DBCONINIT= and DBCONTERM=, as in the following example:
libname myoralib oracle user=testuser password=testpass
path=’myoraserver’ dbconinit="EXEC MY_PROCEDURE";
proc sql;
update myoralib.mytable set acctnum=123
where acctnum=567;
