Data Set Options for Relational Databases |
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
|
DBKEY=(<'>column-1<'><...
<'>column-n<'>>)
|
-
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.
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. ![[cautionend]](../../../../common/63294/HTML/default/images/cautend.gif)
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;
DBINDEX= Data Set Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.