SAS/ACCESS Software for Relational Databases: Reference

Overview of Informix Servers

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 Informix-SE server 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 online server.

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 tab1 and tab2 reside in different databases, mydb1 and mydb2, respectively.

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.

Chapter Contents
Previous
Next
Top of Page