The LIBNAME Statement for Relational Databases DBPROMPT= LIBNAME Option 93
DBNULLKEYS= LIBNAME Option
Controls the format of the WHERE clause when you use the DBKEY= data set option
Valid in: the SAS/ACCESS LIBNAME statement
DBMS support: DB2 OS/390, DB2 UNIX/PC, Informix, ODBC, OLE DB, Oracle,
Microsoft SQL Server
Default value:
DBMS-specific
Syntax
DBNULLKEYS= YES | NO
Details
If there might be NULL values in the transaction table or the master table for the
columns that you specify in the DBKEY= option, use DBNULLKEYS=YES. This is the
default for most interfaces. When you specify DBNULLKEYS=YES and specify a
column that is not defined as NOT NULL in the DBKEY= data set option, SAS
generates a WHERE clause that can find NULL values. For example, if you specify
DBKEY=COLUMN and COLUMN is not defined as NOT NULL, SAS generates a
WHERE clause with the following syntax:
WHERE ((COLUMN = ?) OR ((COLUMN IS NULL) AND (? IS NULL)))
This syntax enables SAS to prepare the statement once and use it for any value (NULL
or NOT NULL) in the column. Note that this syntax has the potential to be much less
efficient than the shorter form of the WHERE clause (presented below). When you
specify DBNULLKEYS=NO or specify a column that is defined as NOT NULL in the
DBKEY= option, SAS generates a simple WHERE clause.
If you know that there are no NULL values in the transaction table or the master
table for the columns that you specify in the DBKEY= option, then you can use
DBNULLKEYS=NO. This is the default for the interface to Informix. If you specify
DBNULLKEYS=NO and specify DBKEY=COLUMN, SAS generates a shorter form of
the WHERE clause (regardless of whether or not the column specified in DBKEY= is
defined as NOT NULL):
WHERE (COLUMN = ?)
See Also
To apply this option to an individual data set, see the data set option
“DBNULLKEYS= Data Set Option” on page 183.
DBPROMPT= LIBNAME Option
Specifies whether SAS displays a window that prompts the user to enter DBMS connection
information prior to connecting to the DBMS in interactive mode
