Chapter Contents |
Previous |
Next |
LIBNAME Statement: Informix Specifics |
Valid: | Anywhere |
Syntax | |
Arguments | |
Details | |
SAS/ACCESS Engine Connection Options | |
SAS/ACCESS LIBNAME Options | |
Example: Specifying a LIBNAME Statement to Access Informix Data |
Syntax |
LIBNAME libref
SAS/ACCESS-engine-name
<SAS/ACCESS-engine-connection-options> <SAS/ACCESS-LIBNAME-options>; |
informix
. SAS/ACCESS engines
are implemented differently in different operating environments. The engine
name is required.Some SAS/ACCESS LIBNAME options have the same names as SAS/ACCESS data set options. When you specify an option in the LIBNAME statement, it applies to objects and data that are referenced 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 LIBNAME statement and after a data set name (which references a DBMS table or view), the SAS System uses the value that is specified later, on the data set name. See Data Set Options: Informix Specifics for more information.
Details |
The LIBNAME statement associates a libref with a SAS/ACCESS engine to access tables or views in a database management system (DBMS). The SAS/ACCESS engine enables you to connect to a particular DBMS and to specify a DBMS table or view name in a two-level SAS name. For example, in MYDBLIB.EMPLOYEES_Q2, MYDBLIB is a SAS libref that points to a particular group of DBMS objects, and EMPLOYEES_Q2 is a DBMS table name. When you specify MYDBLIB.EMPLOYEES_Q2 in a DATA step or procedure, you dynamically access the DBMS table. In Version 7 and higher, SAS software supports reading, updating, creating, and deleting DBMS tables.
To disassociate or clear a libref from a DBMS, use a LIBNAME statement, specifying the libref (for example, MYDBLIB) and the CLEAR option as follows:
libname mydblib CLEAR;The database engine will disconnect from the database and close any threads or resources that are associated with that connection.
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 for Informix are as follows:
USER= | |
USING= | |
DATABASE= | |
SERVER= |
USING can also be specified with the PASSWORD= and PWD=aliases.
DATABASE= can also be specified with the DB= alias.
You can use the DBDATABASE environment variable to specify database/server combinations, such as database@server or //server/database.
SAS/ACCESS LIBNAME Options |
The SAS/ACCESS interface to Informix supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Options, except for DBMAX_TEXT=. In addition to the supported options, the following LIBNAME options are used only in the interface to Informix or have Informix-specific aspects to them:
LOCKTABLE= | |
LOCKTIME= | |
LOCKWAIT= | |
PRESERVE_COL_NAMES= | |
PRESERVE_TAB_NAMES= | |
READ_ISOLATION_LEVEL= | |
SCHEMA= | |
SPOOL= |
If you omit LOCKTABLE=, no locking occurs. If you specify LOCKTABLE=EXCLUSIVE, other users are prevented from accessing each table that you open in the libref.
If you specify LOCKTABLE=SHARE, other users or processes can read data from the tables, but they cannot update the data.
LOCKTABLE= can also be specified with the TABLELOCK= alias.
You must specify LOCKWAIT=YES for LOCKTIME= to have an effect. If you omit the LOCKTIME= option and use LOCKWAIT=YES, SAS suspends your process indefinitely until a lock can be obtained.
See also: LOCKWAIT=.
By default, the SAS/ACCESS Interface to Informix returns an error if another user holds a lock on the rows that you want to lock. If you specify LOCKWAIT=YES, SAS waits until rows are available for locking, or until the number of seconds specified by using the LOCKTIME= option has passed. In the latter case, an error is returned.
If you specify LOCKWAIT=NO or omit this option, SAS does not wait and returns an error to indicate that the lock is not available.
Note: If you specify LOCKWAIT= and do not limit the wait time
by using the LOCKTIME= option, your process might suspend indefinitely if
the lock cannot be obtained.
See also: LOCKTIME=.
If you omit PRESERVE_COL_NAMES=, the default value is PRESERVE_COL_NAMES=NO, which means that column names are created and referenced in lowercase. If you want to preserve the case or allow characters that are not supported in SAS names, such as '$', in your column names, set PRESERVE_COL_NAMES=YES.
For a full description of this option, refer to SAS/ACCESS LIBNAME Statement.
If you omit PRESERVE_TAB_NAMES=, the default value is PRESERVE_TAB_NAMES=NO, which means that table names are created and referenced in lowercase. If you want to preserve case or allow characters that are not supported in SAS names, such as '$', in your object names, including table names, schema names, and link names, set PRESERVE_TAB_NAMES=YES.
For a full description of this option, refer to SAS/ACCESS LIBNAME Statement.
If you omit the READ_ISOLATION_LEVEL= option, the default value is READ_ISOLATION_LEVEL= COMMITTED_READ, which retrieves only committed rows. No locks are acquired, and rows can be locked exclusively for update by other users or processes.
If you specify READ_ISOLATION_LEVEL=REPEATABLE_READ, you acquire a shared lock on every row that is selected during the transaction. Other users or processes can also acquire a shared lock, but no other process can modify any row that is selected by your transaction. If you repeat the query during the transaction, you reread the same information. The shared locks are released only when the transaction commits or rolls back. Another process cannot update or delete a row that is accessed by using a repeatable read.
If you specify READ_ISOLATION_LEVEL=DIRTY_READ, you retrieve committed and uncommitted rows that might include phantom rows, which are rows that are created or modified by another user or process that might subsequently be rolled back. This type of read is most appropriate for tables that are not frequently updated.
If you specify READ_ISOLATION_LEVEL=CURSOR_STABILITY, you acquire a shared lock on the selected row. Another user or process can acquire a shared lock on the same row, but no process can acquire an exclusive lock to modify data in the row. When you retrieve another row or close the cursor, the shared lock is released.
Note: For current Informix releases, READ_ISOLATION_LEVEL= is only valid when transaction logging is enabled. If transaction logging is not enabled, an error is generated when you use this option. Also, locks placed when READ_ISOLATION_LEVEL=REPEATABLE READ or CURSOR_STABILITY are not freed until the libref is cleared.
In most situations, spooling, which is on by default, provides the data consistency you need. However, if you want to use READ_ISOLATION_LEVEL=REPEATABLE_READ or CURSOR_STABILITY, it is recommended that you assign a separate libref with this option, and that you clear the libref when you have finished working with the tables. This technique minimizes the negative performance impact on other users that occurs when you lock the tables. To clear the libref, include the following code:
LIBNAME libref CLEAR;
If you omit the SCHEMA= option, you can view only your own tables and views.
SCHEMA= can also be specified with the OWNER= alias.
If you omit SPOOL=, the default value is SPOOL=YES, which means that SAS performs spooling. If you specify SPOOL=NO, SAS does not perform spooling.
For a full description of this option, refer to SAS/ACCESS LIBNAME Statement.
Example: Specifying a LIBNAME Statement to Access Informix Data |
libname mydblib informix user=testuser using=testpass database=testdb server=testserver; proc print data=mydblib.customers; where gender='M'; run;
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.