DBKEY= Data Set Option

Specifies a key column to optimize DBMS retrieval.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	none
Data source:	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
See:	DBINDEX= data set option

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

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 DBINDEX= LIBNAME option.

Examples

Example 1: Using DBKEY= with MODIFY=

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;

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