Data Set Options for Relational Databases

READ_LOCK_TYPE= Data Set Option

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

Default value:	DBMS-specific
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

READ_LOCK_TYPE=ROW | PAGE | TABLE | NOLOCK | VIEW

Syntax Description

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

ROW [valid only for DB2 under UNIX and PC Hosts, Microsoft SQL Server, ODBC, Oracle]: locks a row if any of its columns are accessed.
PAGE [valid only for Sybase]: locks a page of data, which is a DBMS-specific number of bytes.
TABLE [valid only for DB2 under UNIX and PC Hosts, DB2 under z/OS, ODBC, Oracle, Microsoft SQL Server, Teradata]: locks the entire DBMS table. If you specify READ_LOCK_TYPE=TABLE, you must also specify the 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 only for Microsoft SQL Server, Oracle, Sybase, and ODBC with the Microsoft SQL Server driver]: does not lock the DBMS table, pages, or any rows during a read transaction.
VIEW [valid only for Teradata]: locks the entire DBMS view.

Details

If you omit READ_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. See the locking topic for your interface in the DBMS-specific reference section for details.