CONNECTION= LIBNAME Statement Option

Specifies whether operations on a single libref can share a connection to the DBMS.

Valid in: LIBNAME statement
Default: SHAREDREAD
Supports: DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

CONNECTION= SHARED | SHAREDREAD | UNIQUE

Syntax Description

SHARED

specifies that all operations that access DBMS tables in a single libref share a single connection.

Use this option with caution. When a single SHARED connection is used for multiple table opens, a commit or rollback that is performed on one table being updated also applies to all other tables that are opened for update. Even if a table is opened for a Read operation, its Read cursor might be resynchronized as a result of this commit or rollback. If the cursor is resynchronized, there is no guarantee that the new table will match the original table that was being read.
Use SHARED to eliminate the deadlock that can occur when you create and load a DBMS table from an existing table that resides in the same database or tablespace. This happens only in certain output processing situations and is the only recommended use for CONNECTION=SHARED.

SHAREDREAD

specifies that all Read operations that access DBMS tables in a single libref share a single connection. A separate connection is established for every table that is opened for update or output operations.

Where available, this is the default because it offers the best performance and it guarantees data integrity.

UNIQUE

specifies that a separate connection is established every time a DBMS table is accessed by your SAS application.

Use UNIQUE if you want each use of a table to have its own connection.

Details

Typically, each DBMS connection has one transaction, or work unit, that is active in the connection. This transaction is affected by commits or rollbacks that are performed within the connection while executing the SAS application. The CONNECTION= option enables you to control the number of connections, and therefore transactions, that are executed and supported for each LIBNAME statement.
For ODBC databases, if the data source supports only one active open cursor per connection, the default value is CONNECTION=UNIQUE. Otherwise, the default value is CONNECTION=SHAREDREAD.