Overview: Informix Servers

There are two types of Informix database servers, the Informix OnLline and Informix SE servers. 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 individuals use privately or that a small number of users share.

The SQL pass-through facility supports the DBDATASRC environment variable, which is an extension to the Informix environment variable. If you set DBDATASRC, you can omit the CONNECT statement. The value of DBDATASRC is used instead of the SERVER= argument in the CONNECT statement. The syntax for setting DBDATASRC is like the syntax of the SERVER= argument:

Bourne shell:

 export DBDATABASE='testdsn'

C shell:

setenv DBDATASRC testdsn

If you set DBDATASRC, 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 an SQL view without an explicit CONNECT statement, the view can dynamically connect to different databases, depending on the value of the DBDATASRC environment variable.

Informix supports a connection to only one database. If you have data that spans multiple databases, you must use fully qualified table names to work within the Informix single-connection constraints.

In this example, the tables Tab1 and Tab2 reside in different databases, MyDB1 and MyDB2, respectively.

proc sql;
   connect to informix
   (server=testdsn);

   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.