Data Set Options for Relational Databases

UPDATE_LOCK_TYPE= Data Set Option

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

Default value:	LIBNAME setting
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
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
See Also

Syntax

UPDATE_LOCK_TYPE=ROW | PAGE | TABLE | NOLOCK | VIEW

Syntax Description

Not all values are valid for every interface. See the details in this section.

ROW: locks a row if any of its columns are going to be updated. (This value is valid in the DB2 under UNIX and PC Hosts, Microsoft SQL Server, ODBC, OLE DB, and Oracle interfaces.)
PAGE: locks a page of data, which is a DBMS-specific number of bytes. (This value is valid in the Sybase interface.)
TABLE: locks the entire DBMS table. (This value is valid in the DB2 under UNIX and PC Hosts, DB2 under z/OS, Microsoft SQL Server, ODBC, Oracle, and Teradata interfaces.)
NOLOCK: does not lock the DBMS table, page, or any rows when reading them for update. (This value is valid in the Microsoft SQL Server, ODBC, Oracle, and Sybase interfaces.)
VIEW: locks the entire DBMS view. (This value is valid in the Teradata interface.)

Details

If you omit UPDATE_LOCK_TYPE=, you get either the default action for the DBMS that you are using, or a lock for the DBMS that was set with the LIBNAME statement. 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 SAS/ACCESS documentation for your DBMS for additional, DBMS-specific details about locking.