|SAS/ACCESS Software for Relational Databases: Reference|
There are two Informix database servers, the Informix-Online and Informix-SE server. Informix-Online database servers can support many users and provide tools that ensure high availability, high reliability, and that support critical applications. Informix-SE database servers are designed to manage relatively small databases that are used privately by individuals or shared among a small number of users.
|Specifying Databases and Servers|
To connect to an Informix database, the Pass-Through Facility executes an Informix SQL DATABASE statement. The value that you specify in the DATABASE= argument of the CONNECT statement is passed as a parameter to the Informix SQL DATABASE statement. Your Informix environment must be properly configured in order for this DATABASE= statement to execute correctly. The environment variables that are required for local or remote processing must be set correctly. For a full explanation of database name and path specifications, see your Informix documentation.
To connect to an Informix-SE database, you must either
be in the directory that contains the database or you must specify the full
pathname of the database in the DATABASE= argument. The following example
connects to the database
mydb in the current directory.
proc sql; connect to informix(db=mydb);
The following example connects to the database
groupdb in the directory
/usr/projects on the remote
rmtse. Notice that the DB= argument
is quoted because the SAS System does not accept slashes (/) in names.
proc sql; connect to informix (db='//rmtse/usr/projects/groupdb');
For Informix-Online, only the database server name and
the database name are required. This example connects to the database
corpdb that resides on the
proc sql; connect to informix (db='//online/corpdb');
|Using the DBDATABASE Environment Variable|
The Pass-Through Facility supports the environment variable DBDATABASE, which is an extension to the Informix environment variable. If you set DBDATABASE, you can omit the CONNECT statement. The value of DBDATABASE is used instead of the DATABASE= argument in the CONNECT statement. The syntax for setting DBDATABASE is like the syntax of the DATABASE= argument.
Bourne shell: DBDATABASE='//online/corpdb' export DBDATABASE C shell: setenv DBDATABASE //online/corpdb
If you set DBDATABASE, you can issue a PROC SQL SELECT or EXECUTE statement without first connecting to Informix with the CONNECT statement. If you omit the CONNECT statement, an implicit connection is performed when the SELECT or EXECUTE statement is passed to Informix. If you create a PROC SQL Pass-Through view without an explicit CONNECT statement, the view can dynamically connect to different databases, depending on the value of the DBDATABASE environment variable.
If you specify both the DBDATABASE environment variable and the DATABASE= argument in the CONNECT statement, the DATABASE= argument takes precedence.
You can also use the DBDATABASE environment variable to specify database/server combinations, such as database@server instead of //server/database.
|Using Fully Qualified Table Names|
Informix supports a connection to only one database. If you have data that span multiple databases, you must use fully qualified table names to work within the Informix single-connection constraints.
In the following example, the tables
tab2 reside in different databases,
proc sql; connect to informix (database=corpdb server=online); create view tab1v as select * from connection to informix (select * from mydb1.tab1); create view tab2v as select * from connection to informix (select * from mydb2.tab2); quit; data getboth; merge tab1v tab2v; by common; run;
Because the tables reside in separate databases, you cannot connect to each database with a PROC SQL CONNECT statement and then retrieve the data in a single step. Using the fully qualified table name (that is, database.table) enables you to use any Informix database in the CONNECT statement and access Informix tables in the same or different databases in a single SAS procedure or DATA step.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.