The LIBNAME Statement for Relational Databases

UTILCONN_TRANSIENT= LIBNAME Option

Enables utility connections to maintain or drop, as needed.

Default value:	YES (DB2 under z/OS), NO (Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLEDB, Oracle, Sybase, Sybase IQ, Teradata)
Valid in:	SAS/ACCESS LIBNAME statement and some DBMS-specific connection options. See the DBMS-specific reference section for details.
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
See Also

Syntax

UTILCONN_TRANSIENT=YES | NO

Syntax Description

NO: specifies that a utility connection is maintained for the lifetime of the libref.
YES: specifies that a utility connection is automatically dropped as soon as it is no longer in use.

Details

For engines that can lock system resources as a result of operations such DELETE or RENAME, or as a result of queries on system tables or table indexes, a utility connection is used. The utility connection prevents the COMMIT statements that are issued to unlock system resources from being submitted on the same connection that is being used for table processing. Keeping the COMMIT statements off of the table processing connection alleviates the problems they can cause such as invalidating cursors and committing pending updates on the tables being processed.

Because a utility connection exists for each LIBNAME statement, the number of connection to a DBMS can get large as multiple librefs are assigned across multiple SAS sessions. Setting UTILCONN_TRANSIENT=YES keeps these connections from existing when they are not being used. This setting reduces the number of current connections to the DBMS at any given point in time.

UTILCONN_TRANSIENT= has no effect on engines that do not support utility connections.