DBKEY=

DBKEY=

Specifies the column to use as an index.

Default value: none

See Also: DBINDEX=

Syntax
Details

Syntax

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

column: is the name of the column that forms the index on the DBMS table.

Details

When DBKEY= is specified, you provide the names of the columns that form the index. The index may or may not actually exist on the DBMS table. The DBKEY= option is similar to the DBINDEX=index-name option except that instead of providing the index name, you provide the column name or names that form the index. SAS uses the specified column name or names as an index by constructing and passing a WHERE clause to the DBMS. DBINDEX= and DBKEY= are mutually exclusive: if you specify them together, SAS overrides the DBINDEX= data set option and the DBINDEX= LIBNAME option.

You can use the DBKEY= option if the index that you need does not exist in the DBMS or if the engine cannot retrieve index information from the DBMS due to insufficient privileges.

The DBKEY= option can be used to improve performance just as indexing can improve performance. With the DBKEY= option, it isn't necessary to have an existing index on the DBMS table.

Note: When you use DBKEY= with the DATA step KEY= option, you must specify DBKEY as the value of the KEY= option, as shown in the following examples. [cautionend]

libname mydblib oracle user=karin pw=haggis schema=hrdept;
data keyvalues;
   input empid;
   datalines;
1247
4444
;

data mydata;
   set keyvalues;
   set mydblib.employees(dbkey=empid) key=dbkey;
run;

The next example shows two columns specified as keys. These columns are used to simulate a composite index.

libname mydblib oracle user=karin pw=haggis schema=hrdept
        pw=testpass;
data keyvalues;
     input empid jobcode;
     datalines;
1247 10
1266 20
;

data mydata; 
   set keyvalues;
   set mydblib.employees(dbkey=(empid jobcode)) 
      key=dbkey;
run;

For more information about using keys, indexes, and WHERE clauses, see Using SAS/ACCESS LIBNAME and Data Set Options to Improve Performance .

Chapter Contents
Previous
Next
Top of Page