Specifies a key column to optimize DBMS retrieval.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Set Control |
| Default: | none |
| Data source: | Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HAWQ, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | DBINDEX= data set option, DBNULLKEYS= data set option, DBNULLKEYS= LIBNAME option |
| CAUTION |
Improper use of this option can decrease performance. For detailed information about using this option, see the DBINDEX= LIBNAME option. |
Table of Contents
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.
When DBNULLKEYS= is specified as YES (or is YES by default), but a particular column has been specified as NOT NULL in DB2, any comparison in the WHERE clause must explicitly exclude SAS missing values. Otherwise, rows in a SAS table with missing values could incorrectly match rows with zeros in a DB2 table.
For example, suppose you specify the Age variable in DB2 as NOT NULL in two tables, A and B. Check for missing values of Age when you use it in a WHERE clause.
select * from sas.a, dbms.b(dbkey=(name address age))
where a.name=b.name and
a.address=b.address and
a.age=b.age and a.age is not null;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;