DBMSTEMP= LIBNAME Option

Specifies whether SAS creates temporary or permanent tables.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	NO
Requirement:	To specify this option, you must first specify CONNECTION=GLOBAL—except for Microsoft SQL Server, which defaults to UNIQUE.
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase IQ, Teradata
See:	CONNECTION= LIBNAME option, Temporary Table Support for SAS/ACCESS

Syntax

Details

Example

Syntax

DBMSTEMP=YES | NO

Syntax Description

YES: specifies that SAS creates one or more temporary tables.

NO: specifies that SAS creates permanent tables.

Details

To significantly improve performance, you must also set DBCOMMIT=0. The value for SCHEMA= is ignored. You can then access and use the DBMS temporary tables using SAS/ACCESS engine librefs that share the global connection that SAS used to create those tables.

To join a temporary and a permanent table, you need a libref for each table and these librefs must successfully share a global connection.

DB2 under z/OS, Oracle, and Teradata: Set INSERTBUFF=1000 or higher to significantly improve performance.

ODBC: This engine supports DB2, MS SQL Server, or Oracle if you are connected to them.

Example: Create and Join a Permanent and a Temporary Table

This example shows how to use this option to create a permanent and temporary table and then join them in a query. The temporary table might not exist beyond a single PROC step. However, this might not be true for all DBMSs.

options sastrace=(,,d,d) nostsuffix sastraceloc=saslog;
LIBNAME permdata DB2 DB=MA40 SCHEMA=SASTDATA connection=global 
   dbcommit=0 USER=sasuser PASSWORD=xxx;
LIBNAME tempdata DB2 DB=MA40 SCHEMA=SASTDATA connection=global 
   dbcommit=0 dbmstemp=yes  USER=sasuser  PASSWORD=xxx;
proc sql;
create table tempdata.ptyacc as
     (
      select pty.pty_id
      from permdata.pty_rb pty,
           permdata.PTY_ARNG_PROD_RB acc
      where acc.ACC_PD_CTGY_CD = 'LOC'
        and acc.pty_id = pty.pty_id
      group by pty.pty_id having count(*) > 5
     );
create table tempdata.ptyacloc as
     (
      select ptyacc.pty_id,
             acc.ACC_APPSYS_ID,
             acc.ACC_CO_NO,
             acc.ACCNO,
             acc.ACC_SUB_NO,
             acc.ACC_PD_CTGY_CD
      from tempdata.ptyacc ptyacc,
          perm data.PTY_ARNG_PROD_RB acc
      where ptyacc.pty_id = acc.pty_id
        and acc.ACC_PD_CTGY_CD = 'LOC'
     );
create table tempdata.righttab as
     (
      select ptyacloc.pty_id
      from permdata.loc_acc loc,
           tempdata.ptyacloc ptyacloc
      where
          ptyacloc.ACC_APPSYS_ID  = loc.ACC_APPSYS_ID
      and ptyacloc.ACC_CO_NO      = loc.ACC_CO_NO
      and ptyacloc.ACCNO          = loc.ACCNO
      and ptyacloc.ACC_SUB_NO     = loc.ACC_SUB_NO
      and ptyacloc.ACC_PD_CTGY_CD = loc.ACC_PD_CTGY_CD
      and loc.ACC_CURR_LINE_AM - loc.ACC_LDGR_BL > 20000
     );
select * from tempdata.ptyacc
except
select * from tempdata.righttab;
drop table tempdata.ptyacc;
drop table tempdata.ptyacloc;
drop table tempdata.righttab;
quit;