The LIBNAME Statement for Relational Databases

CONNECTION= LIBNAME Option

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

Default value:	DBMS-specific
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	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

Syntax
Syntax Description
Details
Examples
See Also

Syntax

CONNECTION=SHAREDREAD | UNIQUE | SHARED | GLOBALREAD | GLOBAL

Syntax Description

Not all values are valid for all SAS/ACCESS interfaces. See "Details."

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, a commit or rollback that is performed on one table that is being updated also applies to all other tables that are opened for update. Even if you open a table only 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 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 tablespace. 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. [cautionend]

GLOBALREAD

specifies that all READ operations that access DBMS tables in multiple librefs share a single connection if the following is true:

the participating librefs are created by LIBNAME statements that specify identical values for the CONNECTION=, CONNECTION_GROUP=, DBCONINIT=, DBCONTERM=, DBLIBINIT=, and DBLIBTERM= options
the participating librefs are created by LIBNAME statements that specify identical values for 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 the following is true:

All participating librefs that LIBNAME statements create specify identical values for the CONNECTION=, CONNECTION_GROUP=, DBCONINIT=, DBCONTERM=, DBLIBINIT=, and DBLIBTERM= options.
All participating librefs that LIBNAME statements create specify identical values for 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, a commit/rollback that is performed on one table that is being updated also applies to all other tables that are opened 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

For most SAS/ACCESS interfaces, there must be a connection, also known as an attach, to the DBMS server before any data can be accessed. 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=.

This option is supported by the SAS/ACCESS interfaces that support single connections or multiple, simultaneous connections to the DBMS.

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

In the following SHAREDREAD 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.

libname mydblib oracle user=testuser  /* connection 1 */
        pw=testpass path='myorapath' 
        connection=sharedread;

libname mydblib2 oracle user=testuser /* connection 2 */ 
        pw=testpass path='myorapath' 
        connection=sharedread;

proc print data=mydblib.tab ...       
proc sql;                           /* connection 3 */  
   update mydblib.tab ...

libname mydblib clear;
libname mydblib2 clear;

In the following GLOBALREAD 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 is used to print the data from MYDBLIB.TAB while a second connection is made for updating MYDBLIB.TAB. The second connection is closed at the end of the step. Note that the first connection is closed with the final LIBNAME statement.

libname mydblib oracle user=testuser /* connection 1 */ 
        pw=testpass path='myorapath' 
        connection=globalread;

libname mydblib2 oracle user=testuser 
        pw=testpass path='myorapath' 
        connection=globalread;

proc print data=mydblib.tab ...        
proc sql;                         /* connection 2 */
   update mydblib.tab ...

libname mydblib clear;         /* does not close connection 1 */
libname mydblib2 clear;        /* closes connection 1 */

In the following UNIQUE example, the libref, MYDBLIB, 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;

In the following GLOBAL example for DB2 under z/OS, both PROC DATASETS invocations appropriately report "no members in directory" because SESSION.B, as a temporary table, has no entry in the system catalog SYSIBM.SYSTABLES. 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 then 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;

/*  Get at the temp table through the global libref. */
data _null_; 
set x.b; 
put _all_; 
run;

/*  Get at 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 the following SHARED example, DB2DATA.NEW is created in the database TEST. Because the table DB2DATA.OLD exists in the same database, the option CONNECTION=SHARED enables the DB2 engine to share the connection both for reading the old table and for creating and loading the new table.

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

In the following GLOBAL 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 the above two examples, you would deadlock in DB2 and get the following 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.