Chapter Contents

Previous

Next

LIBNAME Statement: Informix Specifics

Valid:

Anywhere

Arguments

libref

is any SAS name that serves as an alias to associate the SAS System with a database, schema, server, or group of tables and views.

SAS/ACCESS-engine-name

is a SAS/ACCESS engine name for your DBMS, in this case, informix. SAS/ACCESS engines are implemented differently in different operating environments. The engine name is required.

SAS/ACCESS-engine-connection-options

are options that you specify to connect to a particular database; these options are different for each database. If the connection options contain characters that are not allowed in SAS names, enclose the values of the options in quotation marks. If you specify the appropriate system options or environment variables for your database, you can often omit the connection options. See your Informix documentation for details.

SAS/ACCESS-LIBNAME-options

are options that apply to the processing of objects and data in a DBMS, such as its tables or indexes. For example, the LOCKTABLE= option enables you to lock or unlock tables in a libref. Support for many of these options is DBMS specific.

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;

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=

USER=<'>Informix-user-name<'>

specifies the Informix user name that you use to connect to the database that contains the tables and views that you want to access. If you omit the USER= option, your operating system account name is used, if applicable to your operating environment.

USING=<'>Informix-password<'>

specifies the password that is associated with the Informix user. If you omit the password, Informix uses the password in the /etc/password file.

USING can also be specified with the PASSWORD= and PWD=aliases.

DATABASE=<'>database-name<'>

specifies the name of the Informix database that contains the tables and views that you want to access. If you omit the DATABASE= option, the value of the SAS environment variable DBDATABASE is used as the database name. An error occurs if neither the DATABASE= option nor the DBDATABASE environment variable is set. See Using the DBDATABASE Environment Variable for more information.

DATABASE= can also be specified with the DB= alias.

SERVER=<'>server-name<'>

specifies the server with which to connect. This server accesses the database that contains the tables and views that you want to access. If you omit the SERVER= option, the value of the environment variable INFORMIXSERVER is used as the server name. An error occurs if neither the SERVER= option nor the INFORMIXSERVER environment variable is set.

You can use the DBDATABASE environment variable to specify database/server combinations, such as database@server or //server/database.

SAS/ACCESS LIBNAME Options

When you specify any of the following options in the LIBNAME statement, the option is applied to all objects (such as tables, views, and indexes) in the database that the libref represents.

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=

LOCKTABLE=EXCLUSIVE | SHARE

places exclusive or shared locks on tables. You may lock tables only if you are the owner or have been granted the necessary privilege.

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.

LOCKTIME=integer

specifies the number of seconds to wait until rows are available for locking.

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=.

LOCKWAIT=YES | NO

specifies whether to wait until rows are available for locking.

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=.

PRESERVE_COL_NAMES=YES | NO

preserves spaces, special characters, and mixed case in column names.

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.

PRESERVE_TAB_NAMES=YES | NO

preserves spaces, special characters, and mixed case in table names.

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.

READ_ISOLATION_LEVEL=COMMITTED_READ | REPEATABLE_READ | DIRTY_READ | CURSOR_STABILITY

specifies the method of read locking for Informix to use when it reads tables and views.

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;

  

SCHEMA=username

allows you to view another user's database tables and views.

If you omit the SCHEMA= option, you can view only your own tables and views.

SCHEMA= can also be specified with the OWNER= alias.

SPOOL=YES | NO

specifies whether SAS creates a utility spool file during read operations that are performed with the specified libref.

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

In this example, the libref MYDBLIB uses the Informix engine to connect to an Informix database. The SAS/ACCESS engine connection options are USER=, PASSWORD=, DATABASE=, and SERVER=.

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.