The LIBNAME Statement for Relational Databases

CONNECTION_GROUP= LIBNAME Option

Causes operations on multiple librefs and on multiple SQL pass-through facility CONNECT statements to share a connection to the DBMS.

Default value:	none
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
Example
See Also

Syntax

CONNECTION_GROUP=connection-group-name

Syntax Description

connection-group-name: is the name of a connection group.

Details

This option causes a DBMS connection to be shared by all READ operations on multiple librefs if the following is true:

All participating librefs that LIBNAME statements create specify the same value for CONNECTION_GROUP=.
All participating librefs that LIBNAME statements create specify identical DBMS connection options.

To share a connection for all operations against multiple librefs, specify CONNECTION=GLOBAL on all participating LIBNAME statements. Not all SAS/ACCESS interfaces support CONNECTION=GLOBAL.

If you specify CONNECTION=GLOBAL or CONNECTION=GLOBALREAD, operations on multiple librefs can share a connection even if you omit CONNECTION_GROUP=.

Informix: The CONNECTION_GROUP option enables multiple librefs or multiple SQL pass-through facility CONNECT statements to share a connection to the DBMS. This overcomes the Release 8.2 limitation where users were unable to access scratch tables across step boundaries as a result of new connections being established with every procedure.

Example

In the following example, the MYDBLIB libref shares a connection with MYDBLIB2 by specifying CONNECTION_GROUP=MYGROUP and by specifying identical connection options. The libref MYDBLIB3 makes a second connection to another connection group called ABC. The first connection is used to print the data from MYDBLIB.TAB, and is also used for updating MYDBLIB.TAB. The third connection is closed at the end of the step. Note that the first connection is closed by the final LIBNAME statement for that connection. Similarly, the second connection is closed by the final LIBNAME statement for that connection.

libname mydblib oracle user=testuser    /* connection 1 */
        pw=testpass  
        connection_group=mygroup;

libname mydblib2 oracle user=testuser 
        pw=testpass
        connection_group=mygroup;

libname mydblib3 oracle user=testuser   /* connection 2 */ 
        pw=testpass
        connection_group=abc;

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

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