SAS/ACCESS Interface for Informix

Temporary Table Support for Informix

For general information about this feature, see Temporary Table Support for SAS/ACCESS.

To establish the DBMS connection to support the creation and use of temporary tables, issue a LIBNAME statement with the connection options CONNECTION_GROUP=connection-group and CONNECTION=GLOBAL. This LIBNAME statement is required even if you connect to the database using the Pass-Through Facility CONNECT statement, because it establishes a connection group.

For every new PROC SQL step or LIBNAME statement, you must reissue a CONNECT statement with the CONNECTION_GROUP= option set to the same value so that the connection can be reused.

Terminating a Temporary Table

To terminate a temporary table, disassociate the libref by issuing this statement:

libname libref clear;

Example

In this Pass-Through example, joins are pushed to Informix:

libname x informix user=tester using=xxxxx server=dsn_name
                   connection=global connection_group=mygroup;
 
proc sql;
   connect to informix (user=tester using=xxxxx server=dsn_name
                        connection=global connection_group=mygroup);
   execute (select * from t1 where (id >100)
            into scratch scr1 ) by informix;
   create table count2 as select * from connection to informix
          (select count(*) as totrec from scr1);
quit;
 
proc print data=count2;
run;
 
proc sql;
   connect to informix (user=tester using=xxxxx server=dsn_name
                        connection=global connection_group=mygroup);
   execute(select t2.fname, t2.lname, scr1.dept from t2, scr1 where
          (t2.id = scr1.id) into scratch t3 ) by informix;
quit;

libname x clear;  /* connection closed, temp table closed */

Top of Page