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. In the CONNECT statement, set the CONNECTION_GROUP= option to the same value so that the connection can be reused.

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

libname libref clear;

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 */