Temporary Table Support for DB2 under z/OS

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 connection can also be shared between the LIBNAME statement and the SQL pass-through facility. 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.

To create a temporary table, 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 DBMSTEMP=YES in a LIBNAME statement that persists beyond the PROC SQL step. The global connection then persists across SAS DATA steps and even multiple PROC SQL steps, as shown in this example.

libname temp db2 connection=global;

proc sql;
   connect to db2 (connection=global);
   exec (declare global temporary table temptab1
         like other.table on commit PRESERVE rows) by db2;
quit;

At this point, you can refer to the temporary table by using 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 singe connection.

These examples are based on these assumptions:

Here are the librefs and temporary tables that are used.

libname saslib base 'my.sas.library';
libname dept db2 connection=global schema=dschema;
libname temp db2 connection=global schema=SESSION;
/* Note that temporary table has a schema of SESSION */

proc sql;
   connect to db2 (connection=global);
   exec (declare global temporary table temptab1
        (dname char(20), deptno int)
         on commit PRESERVE rows) by db2;
quit;

To perform a homogeneous join on the DBMS, the next example shows how to use a heterogeneous join with a temporary table. It does this instead of reading the DBMS table into SAS to perform the join. Using the previously created table, SAS data is copied into the temporary table to perform the join.

proc append base=temp.temptab1 data=saslib.joindata;
run;
proc sql;
   connect to db2 (connection=global);
   select * from dept.deptinfo info, temp.temptab1 tab
        where info.deptno = tab.deptno;
   /* remove the rows for the next example */
   exec(delete from session.temptab1) by db2;
quit;

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

proc append base=temp.temptab1 data=saslib.transdat;
run;

proc sql;
   connect to db2 (connection=global);
   exec(update dschema.deptinfo d set deptno = (select deptn from
        session.temptab1)
        where d.dname = (select dname from session.temptab1)) by db2;
quit;