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 UNIX/PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Details

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 performed on one table being updated also applies to all other tables opened for update. Even if a table is opened for read, 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 only happens 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.