Temporary Table Support for Netezza

To make full use of temporary tables, the CONNECTION=GLOBAL connection option is necessary. This option lets you use a single connection across SAS DATA steps and SAS procedure boundaries. This connection can also be shared between LIBNAME statements and the SQL pass-through facility. Because a temporary table exists only within a single connection, you need to be able to share this single connection among all steps that reference the temporary table. The temporary table cannot be referenced from any other connection.

You can currently use only a PROC SQL statement to create a temporary table. To use both the SQL pass-through facility and librefs to reference a temporary table, you must specify a LIBNAME statement before the PROC SQL step so that global connection persists across SAS steps and even across multiple PROC SQL steps. Here is an example.

proc sql;
   connect to netezza (server=nps1 database=test
       user=myuser password=mypwd connection=global);
   execute (create temporary table temptab1 
       as select * from permtable ) by netezza;
quit;

At this point, you can refer to the temporary table by using either the Temp libref or the CONNECTION=GLOBAL option with a PROC SQL step.

You can drop a temporary table at any time or allow it to be implicitly dropped when the connection is terminated. Temporary tables do not persist beyond the scope of a single connection.

These assumptions apply to the examples in this section.

Examples use these librefs and temporary tables.

libname saslib base 'SAS-library';
libname dept netezza server=nps1 database=test
    user=myuser pwd=mypwd connection=global;
libname temp netezza server=nps1 database=test
    user=myuser pwd=mypwd connection=global;

proc sql;
   connect to netezza (server=nps1 database=test
        user=myuser pwd=mypwd connection=global);
   execute (create temporary table temptab1 (dname char(20),
        deptno int)) by netezza;
quit;

This example shows how to use a heterogeneous join with a temporary table to perform a homogeneous join on the DBMS, instead of reading the DBMS table into SAS to perform the join. By using the table that was created previously, you can copy SAS data into the temporary table to perform the join.

proc sql;
   connect to netezza (server=nps1 database=test
        user=myuser pwd=mypwd connection=global);
   insert into temp.temptab1 select * from saslib.joindata;
   select * from dept.deptinfo info, temp.temptab1 tab
        where info.deptno = tab.deptno;
   /* remove the rows for the next example */
   execute (delete from temptab1) by netezza;
   quit;

In this example, transaction processing on the DBMS occurs by using a temporary table instead of using either DBKEY= or MULTI_DATASRC_OPT=IN_CLAUSE with a SAS data set as the transaction table.

proc sql;
connect to netezza (server=nps1 database=test 
   user=myuser pwd=mypwd connection=global);
   insert into temp.temptab1 select * from saslib.transdat;
   execute (update deptinfo d set dname = (select dname from temptab1)
            where d.deptno = (select deptno from temptab1)) by netezza;
quit;