SAS/ACCESS Interface to DB2 Under UNIX and PC Hosts

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

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 (db=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.

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

In the following examples, it is assumed that there is a DeptInfo table on the DBMS that has all of your department information. It is also assumed 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 'SAS-Data-Library';
libname dept db2 db=sample user=myuser pwd=mypwd connection=global;
libname temp db2 db=sample user=myuser pwd=mypwd connection=global
        schema=SESSION; 
/* Note that the temporary table has a schema of SESSION */

proc sql; 
   connect to db2 (db=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;

The following 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 (db=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 the following 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 (db=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;

Top of Page