LIBNAME Statement Specifics for DB2 under UNIX and PC Hosts

Overview

This section describes the LIBNAME statement that SAS/ACCESS Interface to DB2 under UNIX and PC Hosts supports and includes an example. For details about this feature, see the LIBNAME statement.

Here is the LIBNAME statement syntax for accessing DB2 under UNIX and PC Hosts.

LIBNAME libref db2 <connection-options> <LIBNAME-options>;

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

db2

specifies the SAS/ACCESS engine name for the DB2 under UNIX and PC Hosts interface.

connection-options

provides connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. When you use the LIBNAME statement, you can connect to DB2 several ways. Specify only one of these methods for each connection because they are mutually exclusive.

USER=, PASSWORD=, DATASRC=
COMPLETE=
NOPROMPT=
PROMPT=
REQUIRED=

Here is how these options are defined.

USER=<'>user-name<'>

lets you connect to a DB2 database with a user ID that is different from the default ID. USER= is optional. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID for your operating environment is used.

PASSWORD=<'>password<'>

specifies the DB2 password that is associated with your DB2 user ID. PASSWORD= is optional. If it contains spaces or nonalphanumeric characters, you must enclose it in quotation marks. If you specify USER=, you must specify PASSWORD=.

DATASRC=<'>data-source-name<'>

specifies the DB2 data source or database to which you want to connect. DATASRC= is optional. If you omit it, you connect by using a default environment variable.

Alias: DATABASE=, DB=, DSN=

COMPLETE=<'>CLI-connection-string<'>

specifies connection information for your data source or database for PCs only. Separate multiple options with a semicolon. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, you are prompted with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the data source. This option is not available on UNIX platforms. See your DB2 documentation for more details.

NOPROMPT=<'>CLI-connection-string<'>

specifies connection information for your data source or database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned (no dialog box is displayed).

PROMPT=<'> CLI-connection-string<'>

specifies connection information for your data source or database for PCs only. Separate multiple options with a semicolon. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. PROMPT= does not immediately attempt to connect to the DBMS. Instead, it displays a dialog box that contains the values that you entered in the PROMPT= connection string. You can edit values or enter additional values in any field before you connect to the data source.

This option is not available on UNIX platforms.

REQUIRED=<'>CLI-connection-string<'>

specifies connection information for your data source or database for PCs only. Separate the multiple options with semicolons. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, a dialog box prompts you for the connection options. REQUIRED= lets you modify only required fields in the dialog box.

This option is not available on UNIX platforms.

LIBNAME-options

defines how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to DB2 under UNIX and PC Hosts, with the applicable default values. For more detail about these options, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for DB2 under UNIX and PC Hosts

Option	Default Value
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	varies with transaction type
CONNECTION=	SHAREDREAD
CONNECTION_GROUP=	none
CURSOR_TYPE=	operation-specific
DBCOMMIT=	1000 when inserting rows; 0 when updating rows; 10,000 when bulk-loading rows
DBCONINIT=	none
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBINDEX=	YES
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBPROMPT=	NO
DBSLICEPARM=	THREADED_APPS,2 or 3
DEFER=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
FETCH-IDENTITY=	NO
IGNORE_READ_ONLY_COLUMNS=	NO
IN=	none
INSERTBUFF=	automatically calculated based on row length
MULTI_DATASRC_OPT=	NONE
PRESERVE_COL_NAMES=	NO (see Naming Conventions for DB2 under UNIX and PC Hosts)
PRESERVE_TAB_NAMES=	NO (see Naming Conventions for DB2 under UNIX and PC Hosts)
QUERY_TIMEOUT=	0
READBUFF=	automatically calculated based on row length
READ_ISOLATION_LEVEL=	set by the user in the DB2Cli.ini file (see Locking in the DB2 under UNIX and PC Hosts Interface)
READ_LOCK_TYPE=	ROW
REREAD_EXPOSURE=	NO
SCHEMA=	your user ID
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SQLGENERATION=	DBMS
STRINGDATES=	NO
UPDATE_ISOLATION_LEVEL=	CS (see Locking in the DB2 under UNIX and PC Hosts Interface)
UPDATE_LOCK_TYPE=	ROW
UTILCONN_TRANSIENT=	YES

DB2 under UNIX and PC Hosts LIBNAME Statement Example

In this example, the libref MyDBLib uses the DB2 engine and the NOPROMPT= option to connect to a DB2 database. PROC PRINT is used to display the contents of the DB2 table Customers.

libname mydblib db2
   noprompt="dsn=userdsn;uid=testuser;pwd=testpass;";

proc print data=mydblib.customers;
   where state='CA';
run;