Temporary Table Support for DB2 under UNIX and PC Hosts

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

To make full use of temporary tables, the CONNECTION=GLOBAL connection option is necessary. You can use this option to establish a single connection across SAS DATA step and procedure boundaries. The LIBNAME statement and the SQL pass-through facility can also share this connection. Because a temporary table only exists within a single connection, you must 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.

The type of temporary table that is used for this processing is created using the DECLARE TEMPORARY TABLE statement with the ON COMMIT PRESERVE clause. This type of temporary table lasts for the duration of the connection—unless it is explicitly dropped—and retains its rows of data beyond commit points.

DB2 places all global temporary tables in the SESSION schema. Therefore, to reference these temporary tables within SAS, you must explicitly provide the SESSION schema in pass-through SQL statements or use the SCHEMA= LIBNAME option with a value of SESSION.

Currently, the only supported way to create a temporary table is to use a PROC SQL pass-through statement. To use both the SQL pass-through facility and librefs to reference a temporary table, you need to specify a LIBNAME statement before the PROC SQL step. This enables the global connection to persist across SAS steps, even multiple PROC SQL steps, as shown in this example.

libname temp db2 database=sample user=myuser password=mypwd
        schema=SESSION connection=global;

proc sql;
   connect to db2 (datasrc=sample user=myuser pwd=mypwd connection=global);
   execute (declare global temporary table temptab1 like other.table
            on commit PRESERVE rows not logged) by db2;
quit;

At this point, you can refer to the temporary table by using the libref Temp or by using 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 examples assume that you already have the following data.

Here are the librefs and temporary tables that are used.

libname saslib base 'SAS-library';
libname dept db2 datasrc=sample user=myuser pwd=mypwd connection=global;
libname temp db2 datasrc=sample user=myuser pwd=mypwd connection=global
        schema=SESSION;
/* Note that the temporary table has a schema of SESSION */

proc sql;
   connect to db2 (datasrc=sample user=myuser pwd=mypwd connection=global);
   execute (declare global temporary table
            temptab1 (dname char(20), deptno int)
            on commit PRESERVE rows not logged) by db2;
quit;

This example demonstrates how to take a heterogeneous join and use a temporary table to perform a homogeneous join on the DBMS (as opposed to reading the DBMS table into SAS to perform the join). Using the table created above, the SAS data is copied into the temporary table to perform the join.

proc sql;
   connect to db2 (datasrc=sample 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 session.temptab1) by db2;
   quit;

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

connect to db2 (datasrc=sample user=myuser pwd=mypwd connection=global);
   insert into temp.temptab1 select * from saslib.transdat;
   execute (update deptinfo d set deptno = 
           (select deptno from session.temptab1)
           where d.dname = (select dname from session.temptab1)) by db2;
quit;