CONNECTION= LIBNAME Statement Option

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=myusr1 pw=mypwd1 
   path='mysrv1' connection=sharedread;
/* connection 2 */
libname mydblib2 oracle user=myusr1
   pw=mypwd1 path='mysrv1' 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=mysrv1 pw=mypwd1 
   path='myorapath' connection=globalread;
libname mydblib2 oracle user=mysrv1 pw=mypwd1 
   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 establishes a connection to obtain database information. Another 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 then closes the connection that was made during the MYDBLIB libref assignment.

libname mydblib oracle user=myusr1 pw=mypwd1 
   path='mysrv1' 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, 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, 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.