SAS/ACCESS Interface to Netezza

Temporary Table Support for Netezza

See Temporary Table Support for SAS/ACCESS for general information about this feature.

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.

Terminating a Temporary Table

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.

Examples

The following assumptions apply to the examples in this section:

The DeptInfo table already exists on the DBMS that contains all of your department information.
One SAS data set contains join criteria that you want to use to extract specific rows from the DeptInfo table.
Another SAS data set contains updates to the DeptInfo table.

These examples use the following librefs and temporary tables.

libname saslib base 'SAS-Data-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 first 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 next 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;

Top of Page