DBKEY= Data Set Option

Specifies a key column to optimize DBMS retrieval. Can improve performance when you are processing a join that involves a large DBMS table and a small SAS data set or DBMS table.

Valid in:	DATA and PROC steps
Supports:	All

Syntax

Details

Examples

Example 1: Using DBKEY= with MODIFY=

Example 2: Using More than One DBKEY= Value

Syntax

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

Syntax Description

column

used by SAS 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 designated in the WHERE clause. However, if the DBKEY= column does not match the key column in the WHERE clause, then DBKEY= is not used.

Details

When processing a join that involves a large DBMS table and a relatively small SAS data set, you might be able to use DBKEY= to improve performance.

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.

Examples

Example 1: Using DBKEY= with MODIFY=

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

libname invty fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=db2dsn dsnuser=db2user dsnpwd=db2pwd;
data invty.stock;
   set addinv;
   modify invty.stock(dbkey=partno) key=dbkey;
   INSTOCK=instock+nwstock;
   RECDATE=today();
   if _iorc_=0 then replace;
run;

Example 2: Using More than One DBKEY= Value

To use more than one value for DBKEY=, you must include the second value as a join in the WHERE clause. In the following example, the 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;