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
Syntax Description
Details
Examples
See Also

Syntax

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

Syntax Description

column

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.

Details

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.

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

Examples

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;
   INSTOCK=instock+nwstock;
   RECDATE=today();
   if _iorc_=0 then replace;
run;

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;
	 quit;