CONNECTION= LIBNAME Option

Specifies whether operations on a single or multiple librefs share a connection to the DBMS.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	DBMS-specific
Interaction:	For DBMSs that default to CONNECTION=UNIQUE, the LIBNAME connection can fail when you use SQL_FUNCTIONS= for that same DBMS to store the external SQL dictionary.
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	ACCESS= LIBNAME option, CONNECTION_GROUP= LIBNAME option, DEFER= LIBNAME option

Syntax

Details

Examples

Example 1: Use SHAREDREAD

Example 2: Use GLOBALREAD

Example 3: Use UNIQUE

Example 4: Use SHARED

Example 5: Use GLOBAL

Syntax

CONNECTION=SHAREDREAD | UNIQUE | SHARED | GLOBALREAD | GLOBAL

Syntax Description

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 usually the default value 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.

SHARED [not valid for MySQL]: specifies that all operations that access DBMS tables in a single libref share a single connection.

Use this option with caution. When you use a single SHARED connection for multiple table opens, performing a commit or rollback on one table that is being updated also applies to all other tables that are open for update. Even if you open a table only for READ, its READ cursor might be resynchronized due to this commit or rollback. If the cursor is resynchronized, there is no guarantee that the new solution table will match the original solution 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 exists in the same database or table space. This happens only in certain output processing situations and is the only recommended for use with CONNECTION=SHARED.

Note:The CONNECTION= option influences only connections that you use to open tables with a libref. When you set CONNECTION=SHARED, it has no influence on utility connections or explicit pass-through connections.

GLOBALREAD

specifies that all READ operations that access DBMS tables in multiple librefs share a single connection if the participating librefs that LIBNAME statements create specify identical values for these options:

CONNECTION=, CONNECTION_GROUP=, DBCONINIT=, DBCONTERM=, DBLIBINIT=, and DBLIBTERM= options
any DBMS connection options

A separate connection is established for each table that is opened for update or output operations.

GLOBAL [not valid for MySQL]

specifies that all operations that access DBMS tables in multiple librefs share a single connection if all participating librefs that LIBNAME statements create specify identical values for these options:

CONNECTION=, CONNECTION_GROUP=, DBCONINIT=, DBCONTERM=, DBLIBINIT=, and DBLIBTERM= options
any DBMS connection options

One connection is shared for all tables that any libref references for which you specify CONNECTION=GLOBAL.

Use this option with caution. When you use a GLOBAL connection for multiple table opens, performing a commit/rollback on one table that is being updated also applies to all other tables that are open for update. Even if you open a table only for READ, its READ cursor might be resynchronized as a result of this commit/rollback. If the cursor is resynchronized, there is no guarantee that the new solution table will match the original solution table that was being read.

When you set CONNECTION=GLOBAL, any pass-through code that you include after the LIBNAME statement can share the connection. For details, see the CONNECT statement example for the pass-through facility.

Details

SAS/ACCESS interfaces that support single or multiple, simultaneous connections to the DBMS support this option. Not all values are valid for all SAS/ACCESS interfaces.

For most SAS/ACCESS interfaces, there must be a connection, also known as an attach, to the DBMS server before they can access any data. Typically, each DBMS connection has one transaction, or work unit, that is active in the connection. This transaction is affected by any SQL commits or rollbacks that the engine performs within the connection while executing the SAS application.

The CONNECTION= option lets you control the number of connections, and therefore transactions, that your SAS/ACCESS interface executes and supports for each LIBNAME statement.

GLOBALREAD is the default value for CONNECTION= when you specify CONNECTION_GROUP=.

Aster nCluster, MySQL: The default value is UNIQUE.

Greenplum, HP Neoview, Microsoft SQL Server, Netezza, ODBC, Sybase IQ: 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.

Teradata: For channel-attached systems (z/OS), the default is SHAREDREAD; for network attached systems (UNIX and PC platforms), the default is UNIQUE.

Examples

Example 1: Use SHAREDREAD

In this example, MYDBLIB makes the first connection to the DBMS. This connection is used to print the data from MYDBLIB.TAB. MYDBLIB2 makes the second connection to the DBMS. A third connection is used to update MYDBLIB.TAB. The third connection is closed at the end of the PROC SQL UPDATE statement. The first and second connections are closed with the CLEAR option.

/* connection 1 */
libname mydblib oracle user=testuser
        pw=testpass path='myorapath'
        connection=sharedread;
/* connection 2 */
libname mydblib2 oracle user=testuser
        pw=testpass path='myorapath'
        connection=sharedread;
proc print data=mydblib.tab…
/* connection 3 */
proc sql;
   update mydblib.tab…
libname mydblib clear;
libname mydblib2 clear;

Example 2: Use GLOBALREAD

In this example, the two librefs, MYDBLIB and MYDBLIB2, share the same connection for Read access because CONNECTION=GLOBALREAD and the connection options are identical. The first connection prints the data from MYDBLIB.TAB while a second connection updates MYDBLIB.TAB. The second connection is closed at the end of the step. The first connection is closed with the final LIBNAME statement.

/* connection 1 */
libname mydblib oracle user=testuser
        pw=testpass path='myorapath'
        connection=globalread;
libname mydblib2 oracle user=testuser
        pw=testpass path='myorapath'
        connection=globalread;
proc print data=mydblib.tab…
/* connection 2 */
proc sql;
   update mydblib.tab…
/* does not close connection 1 */
libname mydblib clear;
/* closes connection 1 */
libname mydblib2 clear;

Example 3: Use UNIQUE

In this example, the MYDBLIB libref does not establish a connection. A connection is established in order to print the data from MYDBLIB.TAB. That connection is closed at the end of the print procedure. Another connection is established to update MYDBLIB.TAB. That connection is closed at the end of the PROC SQL. The CLEAR option in the LIBNAME statement at the end of this example does not close any connections.

libname mydblib oracle user=testuser
        pw=testpass path='myorapath'
        connection=unique;
proc print data=mydblib.tab…
proc sql;
   update mydblib.tab…
libname mydblib clear;

Example 4: Use SHARED

In this SHARED example, DB2DATA.NEW is created in the database TEST. The DB2DATA.OLD table exists in the same database. So the CONNECTION=SHARED option lets the DB2 engine share the connection for reading the old table and also creating and loading the new table.

libname db2data db2 connection=shared;
data db2data.new (in = 'database test');
   set db2data.old;
run;

If you did not use the CONNECTION= option in this case, you would deadlock in DB2 and receive this error.

ERROR:  Error attempting to CREATE a DBMS table.
ERROR:  DB2 execute error DSNT408I SQLCODE = –911,
ERROR:  THE CURRENT UNIT OF WORK HAS BEEN ROLLED
        BACK DUE TO DEADLOCK.

Example 5: Use GLOBAL

In this example for DB2 under z/OS, both PROC DATASETS invocations appropriately report “no members in directory.” This happens because SESSION.B, as a temporary table, has no entry in the SYSIBM.SYSTABLES system catalog. However, the DATA _NULL_ step and SELECT * from PROC SQL step both return the expected rows. For DB2 under z/OS, when SCHEMA=SESSION the database first looks for a temporary table before attempting to access any physical schema named SESSION.

libname x db2 connection=global schema=SESSION;
proc datasets lib=x;
quit;
/*
 *  DBMS-specific code to create a temporary table impervious
 *  to commits and populate the table directly in the
 *  DBMS from another table.
 */
proc sql;
connect to db2(connection=global schema=SESSION);
execute ( DECLARE GLOBAL TEMPORARY TABLE SESSION.B LIKE SASDXS.A
          ON COMMIT PRESERVE ROWS
         ) by db2;
execute ( insert into SESSION.B select * from SASDXS.A
         ) by db2;
quit;
/*  Access the temp table through the global libref. */
data _null_;
set x.b;
put _all_;
run;
/*  Access the temp table through the global connection. */
proc sql;
connect to db2 (connection=global schema=SESSION);
select * from connection to db2
( select * from SESSION.B  );
quit;
proc datasets lib=x;
quit;

In this example, two different librefs share one connection.

libname db2lib db2 connection=global;
libname db2data db2 connection=global;
data db2lib.new(in='database test');
   set db2data.old;
run;

If you did not use the CONNECTION= option in this last example, you would deadlock in DB2 and receive this error.

ERROR:  Error attempting to CREATE a DBMS table.
ERROR:  DB2 execute error DSNT408I SQLCODE = –911,
ERROR:  THE CURRENT UNIT OF WORK HAS BEEN ROLLED
        BACK DUE TO DEADLOCK.