Previous Page | Next Page

Data Set Options for Relational Databases

DBKEY= Data Set Option

Specifies a key column to optimize DBMS retrieval.
Default value: none
Valid in: DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support: Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax Description
See Also


DBKEY=(<'>column-1<'><... <'>column-n<'>>)

Syntax Description


SAS uses this to build an internal WHERE clause to search for matches in the DBMS table based on the key column. For example:

select * from sas.a, dbms.b(dbkey=x) where a.x=b.x;

In this example, DBKEY= specifies column x , which matches the key column that the WHERE clause designates. However, if the DBKEY= column does NOT match the key column in the WHERE clause, DBKEY= is not used.


You can use this option to potentially improve performance when you are processing a join that involves a large DBMS table and a small SAS data set or DBMS table.

When you specify DBKEY=, it is strongly recommended that an index exists for the key column in the underlying DBMS table. Performance can be severely degraded without an index.


Improper use of this option can decrease performance. For detailed information about using this option, see the Using the DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= Options.  [cautionend]


This example uses DBKEY= with the MODIFY statement in a DATA step:

libname invty db2;
data invty.stock;
   set addinv;
   modify invty.stock(dbkey=partno) key=dbkey;
   if _iorc_=0 then replace;

To use more than one value for DBKEY=, you must include the second value as a join on the WHERE clause. In the next example PROC SQL brings the entire DBMS table into SAS and then proceeds with processing:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

proc sql;
create table work.barbkey as
select keyvalues.empid, employees.hiredate, employees.jobcode 
     from mydblib.employees(dbkey=(empid jobcode)) 
     inner join work.keyvalues on employees.empid = keyvalues.empid;

See Also

DBINDEX= Data Set Option

Previous Page | Next Page | Top of Page