The LIBNAME Statement for Relational Databases

READ_LOCK_TYPE= LIBNAME Option

Specifies how data in a DBMS table is locked during a READ transaction.

Default value:	DBMS-specific
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	DB2 under UNIX and PC Hosts, DB2 under z/OS, Microsoft SQL Server, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Example
See Also

Syntax

READ_LOCK_TYPE=ROW | PAGE | TABLE | NOLOCK |VIEW

Syntax Description

ROW [valid for DB2 under UNIX and PC Hosts, Microsoft SQL Server, ODBC, Oracle, Sybase IQ]: locks a row if any of its columns are accessed. If you are using the interface to ODBC or DB2 under UNIX and PC Hosts, READ_LOCK_TYPE=ROW indicates that locking is based on the READ_ISOLATION_LEVEL= LIBNAME option.
PAGE [valid for Sybase]: locks a page of data, which is a DBMS-specific number of bytes. (This value is valid in the Sybase interface.)
TABLE [valid for DB2 under UNIX and PC Hosts, DB2 under z/OS, Microsoft SQL Server, ODBC, Oracle, Sybase IQ, Teradata]: locks the entire DBMS table. If you specify READ_LOCK_TYPE=TABLE, you must also specify CONNECTION=UNIQUE, or you receive an error message. Setting CONNECTION=UNIQUE ensures that your table lock is not lost--for example, due to another table closing and committing rows in the same connection.
NOLOCK [valid for Microsoft SQL Server, ODBC with Microsoft SQL Server driver, OLE DB, Oracle, Sybase]: does not lock the DBMS table, pages, or rows during a read transaction.
VIEW [valid for Teradata]: locks the entire DBMS view.

Details

If you omit READ_LOCK_TYPE=, the default is the DBMS' default action. You can set a lock for one DBMS table by using the data set option or for a group of DBMS tables by using the LIBNAME option. See the locking topic for your interface in the DBMS-specific reference section for details.

Example

In this example, the libref MYDBLIB uses SAS/ACCESS Interface to Oracle to connect to an Oracle database. USER=, PASSWORD=, and PATH= are SAS/ACCESS connection options. The LIBNAME options specify that row-level locking is used when data is read or updated:

libname mydblib oracle user=testuser password=testpass
        path=myorapth read_lock_type=row update_lock_type=row;