Chapter Contents

Previous

Next

SAS/ACCESS LIBNAME Statement: CA-OpenIngres 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. The value in this argument is INGRES. 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 in order to connect to a particular database; these options are different for each database. If the SAS/ACCESS engine connection options contain characters that are not allowed in SAS names, enclose the values of the options in quotation marks. On some DBMSs, if you specify the appropriate system options or environment variables for your database, you can often omit the SAS/ACCESS engine connection options.

SAS/ACCESS-LIBNAME-options

are options that apply to the objects in a DBMS, such as its tables or indexes. For example, the SPOOL= option enables you to specify whether or not SAS creates a utility spool file during read operations. Support for many of these options is DBMS specific.

Some SAS/ACCESS LIBNAME options can also be specified as SAS/ACCESS engine 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 engine LIBNAME statement and after a data set name (which references a DBMS table or view), the SAS System uses the value that is specified after the data set name. Refer to SAS/ACCESS Data Set Options for more information.

Details

The LIBNAME statement associates a libref with a SAS/ACCESS engine in order to access tables or views in a database management system (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 group of DBMS objects, 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.

To disassociate or clear a libref from a DBMS, use a LIBNAME statement that specifies the libref (for example, MYDBLIB) and the CLEAR option as follows:

libname mydblib CLEAR;

The SAS/ACCESS engine disconnects from the database and closes any free threads or resources that are associated with that connection.

See SAS/ACCESS LIBNAME Statement for more information on options that you can use in the LIBNAME statement.

SAS/ACCESS Engine Connection Options

The SAS/ACCESS engine connection options for CA-OpenIngres are as follows:

	CONN_OPTIONS=
	DATABASE=
	PASSWORD=
	SCHEMA=
	USER=

CONN_OPTIONS="connection-options"

specifies optional connection flags to the CA-OpenIngres DBMS. You can use any flag that is valid in CA-OpenIngres SQL. Connection options must be specified in quotation marks and separated by commas.

DATABASE= 'database-name'

specifies the name of the CA-OpenIngres database that contains the tables and views that you want to access.

The DATABASE= option is required in order to connect to CA-OpenIngres. If you use a remote database, enter its remote virtual node name. If you use a distributed database, enter the database server type. If you use a remote distributed database, enter the database's virtual node name and server type. Quotation marks are required around the entire database string. The syntax for the database field follows:

<'v-node::>database</server--type'>

where

v-node is the name of the remote virtual node where the database is located. It implies that the network node address and protocol are defined by the NETU utility provided by CA-OpenIngres. Refer to your CA-OpenIngres administrator's guide for more information on the NETU utility.

database is the name of the database.

server-type is the type of server that is being accessed at the local or remote site. Valid server types are INGRES for a CA-OpenIngres DBMS server or STAR for a CA-OpenIngres/STAR server. (You can replace /STAR with /D.) If you omit a server type, the default server type, INGRES, for the CA-OpenIngres DBMS, is used.

For example, to access the distributed database called TEXTILE on a remote virtual node named ATLANTA, you would enter 'ATLANTA::TEXTILE/STAR'.

PASSWORD=password

specifies the password that is associated with a username.

The USER= and PASSWORD= connection options are optional in CA-OpenIngres because users have pre-configured userids on their respective nodes.

SCHEMA=schema-name

enables you to specify which schema a libref references when you connect to CA-OpenIngres. When SCHEMA= refers to a userid that is different from your default userid, you must have the appropriate GRANT privileges to be able to access tables in that schema.

By default, when you connect to CA-OpenIngres, all tables are referenced by using your default schema.

USER=username

enables you to connect to a CA-OpenIngres database with a user ID that is different from the default ID.

The USER= and PASSWORD= connections are optional in CA-OpenIngres because users have pre-configured userids on their respective nodes. If USER= is omitted, your default userid is used.

SAS/ACCESS LIBNAME Options

This section describes the LIBNAME statement and its options that are specific to CA-OpenIngres. The SAS/ACCESS interface to CA-OpenIngres supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Statement , except for READ_LOCK_TYPE= and UPDATE_LOCK_TYPE=. In addition to the supported options, the following LIBNAME options are used only in the interface to CA-OpenIngres or have CA-OpenIngres-specific aspects to them:

	DBINDEX=
	PRESERVE_COL_NAMES=
	PRESERVE_TAB_NAMES =
	SPOOL=

DBINDEX=YES|NO

indicates whether or not SAS calls CA-OpenIngres to find all indexes on the specified table.

The default value for CA-OpenIngres is NO. For a full description of this option, refer to SAS/ACCESS LIBNAME Statement .

PRESERVE_COL_NAMES=YES|NO

preserves spaces, special characters, and mixed case in CA-OpenIngres column names if the database was created with the ANSI/ISO Entry SQL-92 setting. Otherwise, PRESERVE_COL_NAMES= only preserves special characters and does not preserve case.

The default value for PRESERVE_COL_NAMES= for CA-OpenIngres is NO because CA-OpenIngres is a case-insensitive DBMS. 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 CA-OpenIngres table names if the database was created with the ANSI/ISO Entry SQL-92 setting. Otherwise, PRESERVE_TAB_NAMES= only preserves special characters and does not preserve case.

The default value for PRESERVE_TAB_NAMES= for CA-OpenIngres is NO because CA-OpenIngres is a case-insensitive DBMS. For a full description of this option, refer to SAS/ACCESS LIBNAME Statement .

SPOOL=YES|NO

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

If you omit SPOOL=, the default value is SPOOL=YES. For a full description of this option, refer to SAS/ACCESS LIBNAME Statement .

Example: Specifying a LIBNAME Statement to Access CA-OpenIngres Data

In this example, the libref MYLIB uses the CA-OpenIngres engine to connect to a CA-OpenIngres database. The SAS/ACCESS engine connection options are USER=, PASSWORD=, DATABASE=, CONN_OPTIONS=, and SCHEMA=.

libname mylib ingres 
   user=tester1 password=myingpass
   database=hrdept
   conn_options="-xw"
   schema=levine;
proc print data=mylib.staff; 
   where state='CA';   
run;

The SQL Chapter in SAS Procedures Guide.

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.