SAS/ACCESS Interface to DB2 Under z/OS

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 that 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.

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 singe connection.

Examples

These examples assume there is a DeptInfo table on the DBMS that has all of your department information. They also assume that you have a SAS data set with join criteria that you want to use to get certain rows out of the DeptInfo table, and another SAS data set with updates to the DeptInfo table.

These librefs and temporary tables 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;

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 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 next 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.

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;

Top of Page