Chapter Contents |
Previous |
Next |
LIBNAME Statement: ODBC Specifics |
Valid: | in a DATA or PROC step |
Syntax |
LIBNAME libref
SAS/ACCESS-engine-name
SAS/ACCESS-engine-connection-options
< SAS/ACCESS-LIBNAME-options>; |
Some SAS/ACCESS LIBNAME options have the same names as SAS/ACCESS engine data set options. When you specify an option in the LIBNAME statement, it applies to objects in the particular database (which is accessed by the libref). A SAS/ACCESS data set option applies only to the data set on which it is specified. If a like named option is specified in both the SAS/ACCESS engine LIBNAME statement and after a data set name (which represents a DBMS table or view), the SAS System uses the value that is specified after the data set name. For more information, see SAS/ACCESS LIBNAME Statement .
Details |
The LIBNAME statement associates a libref with a SAS/ACCESS engine in order to access tables or views in a DBMS. The SAS/ACCESS engine enables you to connect to a particular DBMS and, therefore, to specify a DBMS table or view name in a two-level SAS name. For example, in MYLIB.EMPLOYEES_Q2, MYLIB is a SAS libref that points to a particular DBMS, and EMPLOYEES_Q2 is a DBMS table name. When you specify MYLIB.EMPLOYEES_Q2 in a DATA step or procedure, you dynamically access the DBMS table. Beginning in Version 7, SAS software supports reading, updating, creating, and deleting DBMS tables.
See SAS/ACCESS LIBNAME Statement for more information on arguments that you can use in the LIBNAME statement.
SAS/ACCESS-Engine Connection Options |
The SAS/ACCESS engine connection options are as follows:
USER= | |
PASSWORD= | |
DATASRC= | |
AUTOCOMMIT= | |
COMPLETE= | |
NOPROMPT = | |
PROMPT = | |
REQUIRED= |
Note: Not all
of these engine connection
options are supported by all ODBC drivers. Refer to your vendor-supplied documentation
for more information.
There are multiple ways that you can connect to the DBMS when using the LIBNAME statement. Use only one of the following methods for each connection since they are mutually exclusive:
The USER= and PASSWORD= connections are optional in ODBC. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID is used.
Note: If you do
not specify the data source name, but you do specify USER= and PASSWORD=,
the default data source, default USER, and default PASSWORD are used. The
USER and PASSWORD options specified in the connection will not be used.
USER= can also be specified with the UID= alias.
The USER= and PASSWORD= connection options are optional in ODBC because users have default user IDs. If you specify USER=, you must specify PASSWORD=.
PASSWORD= can also be specified with the PWD=, PW=, USING=, and PASS= aliases.
DATASRC= is an optional connection option. If you omit it, you connect by using a default environment variable.
DATASRC= can also be specified with the DSN=, DS=, and DATABASE= aliases.
If AUTOCOMMIT=YES, no rollback is possible.
If AUTOCOMMIT=NO, the SAS/ACCESS engine automatically does the commit when it reaches the end of the file.
The default value for AUTOCOMMIT= under ODBC is NO if the ODBC driver supports transactions and the connection is used for updating. Otherwise, the default value is YES. The default value is always YES when the PROC SQL Pass-Through facility is used.
COMPLETE= is similar to the PROMPT= option. However, if COMPLETE= attempts to connect and fails, then a dialog box is displayed and you can edit values or enter additional values.
COMPLETE= is optional.
See your driver documentation for more details.
A dialog box is displayed, using the values from the PROMPT= connection string. You can edit any field before you connect to the data source. When a successful connection is made, the complete connect string is returned in the SYSDBMSG macro variable.
PROMPT= is similar to the COMPLETE= option. However, unlike COMPLETE=, PROMPT= does not attempt to connect to the DBMS first. It displays the dialog box where you can edit or enter additional values.
If you specify enough correct connection options, such as user ID, password, and data source name, the SAS/ACCESS engine connects to the data source or database. Otherwise, a dialog box is displayed to prompt you for the connection options. Options in the dialog box that are not related to the connection are disabled. REQUIRED= only allows you to modify required fields in the dialog box. When a successful connection is made, the complete connect string is returned in the SYSDBMSG macro variable.
REQUIRED= is similar to COMPLETE= because it attempts to connect to the DBMS first. However, if REQUIRED= attempts to connect and fails, then a dialog box is displayed and you can only edit values that are in the required fields.
REQUIRED= is optional.
SAS/ACCESS LIBNAME Options |
The SAS/ACCESS interface to ODBC supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Options. In addition to the supported options, the following LIBNAME options are used only in the interface to ODBC or have ODBC-specific aspects to them:
Default value: NO.
BCP is Microsoft's bulk copy facility, a high performance method of inserting data into a DBMS table. As SAS sends each row of data to BCP, the data is buffered. After all insertions, the data is committed to the table. If errors occur, they are written to the file that you specify with the BCP_ERRORFILE= option. A generic error is printed in the SAS log.
Note that to use BCP, your installation of Microsoft SQL Server must include the ODBCBCP.DLL, which is currently only supported by Microsoft SQL Server 7.0. Alternatively, you can set the DBCOMMIT= option to commit rows after a specific number of insertions.
Default value: No error file is specified.
If BCP_ERRORFILE= is not specified, errors are not recorded during BCP processing.
Default value: 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.
You may change the value of this option, which is fully described in the LIBNAME option, CONNECTION= .
By default, CURSOR_TYPE=DYNAMIC, but the driver is allowed to modify the default without an error.
If CURSOR_TYPE=DYNAMIC, then the cursor reflects all of the changes that are made to the rows in a result set as you scroll around the cursor. The data values and the membership of rows in the cursor can change dynamically on each fetch.
If CURSOR_TYPE=FORWARD_ONLY, then the cursor behaves like a DYNAMIC cursor except that it only supports fetching the rows sequentially.
If CURSOR_TYPE=KEYSET_DRIVEN, then the cursor determines which rows belong to the result set when the cursor is opened. However, changes that are made to these rows will be reflected as you scroll around the cursor.
If CURSOR_TYPE=STATIC, then the cursor builds the complete result set when the cursor is opened. No changes that are made to the rows in the result set after the cursor is opened will be reflected in the cursor. Static cursors are read-only.
CURSOR_TYPE= can also be specified with the CURSOR= alias.
See also: KEYSET_SIZE=.
Default value: YES
For a full description of this option, refer to the LIBNAME option, DBINDEX= .
Default value: NO
If DEFER=YES, the connection to the DBMS occurs when a table in the DBMS is opened. If DEFER=NO, the connection to the DBMS occurs when the libref is assigned by a LIBNAME statement. The DEFER= option is ignored when CONNECTION=UNIQUE because a connection is performed for every open.
When setting DEFER=YES in the SAS/ACCESS Interface to ODBC, you must also set the PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= options to their desired values. Normally, SAS queries the data source to default these values correctly during LIBNAME assignment, but setting DEFER=YES postpones the connection. Because these values must be set at the time of LIBNAME assignment, you must assign them explicitly when you set DEFER=YES.
Default value: NO.
Default value: 0
Alias: KEYSET=
This option is valid only when CURSOR_TYPE=KEYSET_DRIVEN. See CURSOR_TYPE= for more information on KEYSET_DRIVEN cursors.
Valid values for KEYSET_SIZE= are 0 through the number of rows in the cursor. If KEYSET_SIZE=0, then the entire cursor is keyset driven. If a value greater than 0 is specified for KEYSET_SIZE=, then the value chosen indicates the number of rows within the cursor that will behave as a keyset driven cursor. When you scroll beyond the bounds that are specified by KEYSET_SIZE=, then the cursor becomes dynamic and new rows may be included in the cursor. This becomes the new keyset and the cursor behaves as a keyset driven cursor again. Whenever the value that is specified is between 1 and the number of rows in the cursor, the cursor is considered to be a mixed cursor since part of the cursor behaves as a keyset driven cursor and part of the cursor behaves as a dynamic cursor.
Default value: YES for Microsoft Access, Microsoft Excel, and Microsoft SQL Server; NO for all others
For a full description of this option, refer to the LIBNAME option, PRESERVE_COL_NAMES= .
Default value: YES for Microsoft Access, Microsoft Excel, and Microsoft SQL Server; NO for all others
For a full description of this option, refer to the LIBNAME option, PRESERVE_TAB_NAMES= .
QUALIFIER= is optional. If it is omitted, you use the
default DBMS qualifier name, if any. QUALIFIER= can be used for any DBMS that
allows three part identifier names such as qualifier.schema.object.
In the following example libname statement, the QUALIFIER= option causes
any reference in SAS to
mydblib.employee
to be interpreted by ODBC as
mydept.scott.employee
.
libname mydblib odbc schema=scott qualifier=mydept;
Default value: 0
The default value of 0 indicates that there is no time limit for a query. This option is useful when you are testing a query, you suspect that a query might contain an endless loop, or you access a table that may be locked by other users.
QUERY_TIMEOUT= can also be specified with the TIMEOUT= alias.
Default value: none
QUOTE_CHAR= overrides the ODBC default since some drivers return a blank for the identifier delimiter even though the DBMS uses a quote (for example, Intersolv to Sybase).
S = Serializable | |
RR = Repeatable Read | |
RC = Read Committed | |
RU = Read Uncommitted | |
V = Versioning |
Default value: RC
The degree of isolation identifies
READ_ISOLATION_LEVEL= is ignored if READ_LOCK_TYPE= is not set to ROW.
The ODBC driver manager supports five isolation levels. The isolation levels are defined in terms of several possible occurrences:
For example, suppose that transaction T1 performs an update on a row, transaction T2 then retrieves that row, and transaction T1 then terminates with rollback. Transaction T2 has then seen a row that no longer exists.
For example, suppose that transaction T1 retrieves a row, transaction T2 then updates that row, and transaction T1 then retrieves the same row again. Transaction T1 has now retrieved the same row twice but has seen two different values for it.
For example, suppose that transaction T1 retrieves the set of all rows that satisfy some condition. Suppose that transaction T2 then inserts a new row that satisfies that same condition. If transaction T1 now repeats its retrieval request, it will see a row that did not previously exist, a phantom.
The isolation levels for READ_ISOLATION_LEVEL= include the following:
These transactions are serializable, but higher concurrency is possible than with the Serializable isolation level. Typically a nonlocking protocol is used.
READ_ISOLATION_LEVEL= can also be specified with the following alias: RIL=.
See Also: UPDATE_ISOLATION_LEVEL=.
Default value: ROW
For a full description of this option, refer to the LIBNAME option, READ_LOCK_TYPE= .
See also: UPDATE_LOCK_TYPE=.
Default value: 0
When ROWSET_SIZE=0, no internal SAS buffering is performed. Setting ROWSET_SIZE=0 causes the SQLFetch API call to be used.
When ROWSET_SIZE=1, only one row is retrieved at a time. The higher the value for ROWSET_SIZE=, the more rows the DB2 engine retrieves in one fetch operation. This option reduces the amount of I/O that is used and can help improve performance. However, because SAS software stores the rows in memory, higher values for ROWSET_SIZE= use more memory. In addition, if too many rows are selected at once, then the rows that are returned to the SAS application might be out of date. For example, if someone else modified the rows, you would not see the changes. Setting ROWSET_SIZE=1 or greater causes the SQLExtendedFetch API call to be used.
ROWSET_SIZE= can also be specified with the ROWSET= alias.
SCHEMA= is optional. If it is omitted, you connect
to the default schema. In the following example LIBNAME statement, the SCHEMA=
option causes any reference in SAS to
mydblib.employee
to be interpreted by ODBC as
scott.employee
.
libname mydblib odbc schema=scott;
SCHEMA= can also be specified with the OWNER= alias.
Default value: YES
For a full description of this option, refer to the LIBNAME option, SPOOL= .
Default value: NO
If STRINGDATES=YES, then the SAS application reads date-time values as character strings.
If STRINGDATES=NO, then the SAS application reads date-time values as numeric date values.
STRINGDATES= can also be specified with the STRDATES= alias.
STRINGDATES= is used for Version 6 compatibility.
Default value: NO
If TRACE=YES, tracing is turned on, and the ODBC driver manager writes each function call to the trace file that is specified by TRACEFILE=.
If TRACE=NO, tracing is not turned on.
See also: TRACEFILE=.
Default value: none
TRACEFILE= is used only when TRACE=YES.
See also: TRACE=.
RC = Read Committed | |
S = Serializable | |
RR = Repeatable Read | |
V = Versioning |
Default value: RC
The degree of isolation identifies
UPDATE_ISOLATION_LEVEL= is ignored if UPDATE_LOCK_TYPE= is not set to ROW.
The ODBC driver manager supports four isolation levels. The isolation levels are defined in terms of several possible occurrences:
For example, suppose that transaction T1 performs an update on a row, transaction T2 then retrieves that row, and transaction T1 then terminates with rollback. Transaction T2 has then seen a row that no longer exists.
For example, suppose that transaction T1 retrieves a row, transaction T2 then updates that row, and transaction T1 then retrieves the same row again. Transaction T1 has now retrieved the same row twice but has seen two different values for it.
For example, suppose that transaction T1 retrieves the set of all rows that satisfy some condition. Suppose that transaction T2 then inserts a new row that satisfies that same condition. If transaction T1 now repeats its retrieval request, it will see a row that did not previously exist, a phantom.
The isolation levels for UPDATE_ISOLATION_LEVEL= include the following:
These transactions are serializable but higher concurrency is possible than with the Serializable isolation level. Typically a nonlocking protocol is used.
UPDATE_ISOLATION_LEVEL= can also be specified with the UIL= alias.
See Also: READ_ISOLATION_LEVEL=.
Default value: ROW
The value NOLOCK specifies that there is no locking on the table when it is read for update. Although NOLOCK is allowed, not all ODBC drivers support this option. In this case, an error will be printed.
For a full description of this option, refer to the LIBNAME option, UPDATE_LOCK_TYPE= .
See also: READ_LOCK_TYPE=.
Default value: NO
This option allows SAS to continue if multiple rows were updated.
Default value: NO
If USE_ODBC_CL=YES, the Driver Manager uses the ODBC Cursor Library. The ODBC Cursor Library supports block scrollable cursors and positioned update and delete statements. For more information on the ODBC Cursor Library, see your vendor-specific documentation.
If USE_ODBC_CL=NO, the Driver Manager uses the scrolling capabilities of the driver.
Examples |
libname mydblib odbc dsn=as400 uid=testuser pwd=testpass; proc print data=mydblib.customers; where state='CA'; run;
libname mydblib odbc noprompt="uid=testuser;pwd=testpass;dsn=sqlservr;" stringdates=yes; proc print data=mydblib.customers; where state='CA'; run;
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.