Temporary Table Support for SAS/ACCESS :: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Overview

DBMS temporary table support in SAS consists of the ability to retain DBMS temporary tables from one SAS step to the next. This ability is a result of establishing a SAS connection to the DBMS that persists across multiple SAS procedures and DATA steps.

Temporary table support is available for these DBMSs.

DBMS-Specific Temporary Table Support

DBMS	Temporary Table Support	DBMSTEMP= LIBNAME Support
Aster nCluster	yes	yes
DB2 under UNIX and PC Hosts	yes	yes
DB2 under z/OS	yes	yes
Greenplum	yes	yes
HP Neoview	yes	yes
Informix	yes	no
Microsoft SQL Server	yes	yes
MySQL	no	no
Netezza	yes	yes
ODBC	yes	yes
OLE DB	yes	yes
Oracle	yes	yes
Sybase	yes	no
Sybase IQ	yes	yes
Teradata	yes	yes

The value of DBMS temporary table support in SAS is increased performance potential. By pushing processing to the DBMS in certain situations, you can achieve an overall performance gain. These processes provide a general outline of how to use DBMS temporary tables.

General Temporary Table Use

Follow these steps to use temporary tables on the DBMS.

Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.
Create a DBMS temporary table and load it with data.
Use the DBMS temporary table with SAS.

Closing the global connection causes the DBMS temporary table to close as well.

Pushing Heterogeneous Joins

Follow these steps to push heterogeneous joins to the DBMS.

Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.
Create a DBMS temporary table and load it with data.
Perform a join on the DBMS using the DBMS temporary and DBMS permanent tables.
Process the result of the join with SAS.

Pushing Updates

Follow these steps to push updates (process transactions) to the DBMS.

Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.
Create a DBMS temporary table and load it with data.
Issue SQL that uses values in the temporary table to process against the production table.
Process the updated DBMS tables with SAS.

Although these processing scenarios are purposely generic, they apply to each DBMS that supports temporary tables. For details, see the DBMSTEMP= LIBNAME Option.